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