The Money Mustache Community
Learning, Sharing, and Teaching => Ask a Mustachian => Topic started by: PhillyWill 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.
-
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.
-
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.