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.