Author Topic: Future Value Function in Excel  (Read 1160 times)

PhillyWill

  • 5 O'Clock Shadow
  • *
  • Posts: 18
  • Location: On the Banks of the Mighty Schuylkill
  • Stubble
Future Value Function in Excel
« on: July 27, 2016, 10:45:47 AM »
Hi Folks,
     I've been using a modiifed version of the FV function to forecast what my long-term growth should look like. I'm strictly a neophyte where excel is concerned, so I'd appreciate a critique! I'm mostly concerned with two points:
1) Am I using the formula correctly?
2) Is my overall mothodology sound, specifically where inflation is concerned?

I'm using the base formula: =FV(ROR,# of time periods, investment per period, Initial investment, Invest at beginning vs end of period)
found here: http://www.excelfunctions.net/Excel-Fv-Function.html

Here is the slightly modified formula that I used for year 2 of my 401(k) growth model: =FV((0.07/12),12,-(M4*1.02),-9500,1)
     For simplicity I'm assuming a monthly contribution at the beginning of the month rather than biweekly as it happens IRL. M4 is my monthly IRA contribution, calculated separately so that I can model the effect of different contribution rates. The only real change I made is to add the "*1.02" term to reflect minimal COL pay raises going forward. For each subsequent year I added an exponent reading "*1.02^2"etc. to reflect raising my contribution by 2% each year.
     Here is how the first few years turned out:     
    Vanguard              401(k)            Total                     I-adj                    Real NW
                  
2016    $29,000            $9500           $38,5000.00      1                   $38,500.00
2017    $31,030.30       $27,116.74    $58,146.74        0.980392157   $57,006.6
2018    $33,202.10       $46,708.66    $79,910.76       0.960784314   $76,777.01
2019    $35,526.25       $68,466.64    $103,992.89     0.941568627   $97,916.44
2020    $38,013.08       $92,599.46    $130,612.54     0.922737255   $120,521.06


Let me know if you have any suggestions! Thanks.

-Will


EDIT: I just realized that the spreadsheet showed up as a downloadable attachment rather than showing in the post. I added the sample values so there's no need to download unless you want to see the whole thing.
« Last Edit: July 27, 2016, 11:49:00 AM by PhillyWill »

jjcamembert

  • Stubble
  • **
  • Posts: 152
Re: Future Value Function in Excel
« Reply #1 on: July 27, 2016, 01:38:53 PM »
In general I think you are using the FV function correctly, but, I think because you are dividing it into monthly and then computing annually you're getting a small error.

For year 6, I rewrote the formula to just be annual:

=FV((0.07),6,-(M3*1.02^6),-D3,1)
this gives a value of $151,990.66, compared to the value you had of $148,924.83.

I believe this is because your PMT parameter is being compounded by 2%. Our answers are about 2% off from one another. I could be wrong.

I have a similar spreadsheet, but instead of FV I just add 3% (preferring to be pessimistic) to each column in the next year. This allows me to update individual rows without affecting the ones before it. For example, if you get a 10% raise you're going to have to make a new spreadsheet. If the IRS doesn't increase the contribution limit one year, you'll need a new spreadsheet. If each row is only dependent on the numbers before it, you can easily write in your new salary for year x, and the calculations will keep up from there.

PhillyWill

  • 5 O'Clock Shadow
  • *
  • Posts: 18
  • Location: On the Banks of the Mighty Schuylkill
  • Stubble
Re: Future Value Function in Excel
« Reply #2 on: July 27, 2016, 03:08:54 PM »
jjcamembert- Thanks for pointing that out. You made me realize that I was assuming an increase in contribution limits much greater than what we've seen historically. I dropped the % growth assumption and added in a flat $390 per year that keeps up the same average rate in max contribution growth since 1994.