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.
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.
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.