Microsoft KB Archive/44291

Multiplan: Using an Amortization Formula PSS ID Number: Q44291 Article last modified on 01-10-1991 PSS database name: D_MPlan

1.x 2.x 3.00 3.01 3.02 3.04 4.00 4.01 4.20

MS-DOS

Summary:

The following information is available as an application note from Microsoft Product Support Services by calling (206) 454-2030. The application note is titled “Amortization Formula.”

Example 1
The first example computes the monthly payment and monthly interest rate when the loan term, principal amount, and annual percentage rate (APR) are known. Enter the formulas in column 2 and then type values in column 2 for the loan term, principal amount, and annual percentage rate (rows 2, 3, and 5); the monthly payment and monthly interest rate will be computed.

Column 1                   Column 2 1 “Monthly Payment” R[+2]C/((1-(1+R[+3]C)^-R[+1]C)/R[+3]C) 2 “Loan Term (in months)” 0 3 “Principal Amount of Loan” 0 4 “Monthly Interest Rate” R[+1]C/12 5 “APR” 0 (entered as xx.xx)

Note: The monthly payment will display a “! Div0” error until the three known values are entered.

Example 2
The second example computes the monthly and annual percentage rates when the monthly payment, loan term, and principal amount of loan are known. If you type the values for monthly payment, loan term (in months), and principal amount (rows 1, 2, and 3), the monthly interest and annual percentage rates will be computed.

Column 1 Column 2 ——– ——–

1 “Monthly Payment” 0 2 “Loan Term (in months)” 0 3 “Principal Amount of Loan” 0 4 “Monthly Interest Rate” IF(RC<>0,((1-(1+RC)^(-1R[-2]C))R[- 3]C)/R[-1]C,0.001) 5 “APR” 12*R[-1]C

Note: The monthly interest rate value in column 2 should be one line.

Copyright Microsoft Corporation 1991.