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

#### PhillyWill

• 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: 153
##### 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.