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