Tag: EXCEL

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.   

Excel Hint 6: The FV function and 401(k) fees

Hint 6: The Excel FV function is used to calculate the future value of a dormant 401(k) or IRA when the only difference between the two accounts is the level of fees.

The situation:    Finance tip #6 considers potential benefits realized by rolling over funds from a high-cost 401(k) to a low-cost IRA.   The retirement account is not accepting new contributions. The person at age 50 will either leave $500,000 in a 401(k) or move $500,000 to a Roth for 15 years.  The pre-fee annual rate of return on both the 401(k) and the IRA is 6.0 percent.  The 401(k) has an annual fee of 1.3 percent.   The IRA has an annual fee of 0.03 percent.

Question on use of Excel:  How does one calculate the value of the retirement account after 15 years?

Analysis:

This calculation can be completed with the FV function.  The FV function has arguments – Rate, Nper, Pmt, Pv, and Type.

  • The Rate is .047 for the 401(k) and 0.057 for the IRA.
  • The Nper or holding period is 15 for both accounts.
  • The PMT is 0 for both accounts.   (The worker is no longer making contributions.)  
  • The PV is the initial account balance, $500,000.
  • The type is 0 because the $500,000 exists in the account at the beginning of the period.

Results

  • FV(0.047,15,0,500,000,1) is $995,796.
  • FV(0.057,15,0,500,000,1) is $1,148,404.

Remember to go to finance tip 6 for a more complete discussion of factors impacting the choice between a high-fee 401(k) and a low-fee IRA.