Excel Financial Functions

Question

Time for a new problem. You want to borrow $200,000 at 4% interest compounded monthly. If you can

afford to make monthly payments of only $1200/month, how many months (cell J14 on the sample output)

will it take you to pay off the loan completely? How many years (cell K14 on the sample output)?

Answer 1

Following is how I calculated the answer in Excel.

Loan amount in cell B2: $200,000

Annual interest rate in cell B3: 4%

Monthly interest rate in cell B4: Formula is =B3/12. Answer is .333%

Monthly payment in cell B5: $1,200

Number of payments in cell B6: Formula is -NPER(B4,B5,B2). Answer is 132.77

Number of years in cell B7: Formula is =B6/12. Answer is 11 years (actually 11.06 years rounded to 11 years)