Excel Hint #4: Calculating the future value of a mortgage

A key advantage of choosing a 15-year mortgage over a 30-year mortgage is the more rapid decrease in the mortgage balance and increase in house equity. This post discusses the use of Excel to calculate the future value of different mortgages.

Situation:   A person is considering two options for a $500,000 loan.   The first option is a 30-year term at an interest rate of 3.4%.   The second option is a 15-year term at an interest rate at 2.9%.  

  • How can Excel be used to calculate the mortgage balance at 7 years for the two options?  
  • What are the mortgage balances for the two options after seven years?

The Calculation:  The future value of the mortgages above are calculated in Excel with a two-step procedure. 

Step One

Calculate monthly payment from the PMT function.

The arguments of the PMT function are – the monthly interest rate, the maturity of the loan in months, and the initial loan balance.

  • The 30-year monthly payment is PMT(0.034/12,360,500,000) or $2,217.41.
  • The 15-year monthly payment is PMT(0.029/12,180,500,000) or $3,428.91.

These monthly payment values are input for the second step.

Step Two:  

Calculate the outstanding loan balance from the FV function.

The arguments of the FV function are — the monthly interest rate, the number of months the mortgage is held, the monthly mortgage payment, and the initial value of the mortgage.

  • The outstanding mortgage balance at 7 years for the 30-year loan is FV(0.034/12,84,2217.41,500,000) or $424,180.
  • The outstanding mortgage balance at 7 years for the 15-year mortgage is FV(0.029/12,84,3428.91,500000) or $293,466.

Concluding thoughts:  The more rapid build-up of equity from the use of a 15-year mortgage can allow a person to sell a home and pay off the mortgage even if housing prices fall.  The calculations presented here were used in finance tip #4, a discussion of the advantages and disadvantages of 15-year and 30-year FRM.   

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.