I have searched high and low but I can't seem to find the right calculation to work out exactly what I need.
What I need is an equation. To work out the FV of an inflation indexed savings plan.
The key to my issue is that the savings earn interest which is compounded monthly, where as the inflation that is added to the payments should only be applied annually, yet the person makes payments monthly.
i.e. Someone chooses to deposit $$500 per month into an account that yields 5% interest (compounded monthly) Therefore for the first year every month they would pay $500 and at the end of the year they would have $6165.01
The $500 is then inflated by 2% so in the second year they pay $510 per month and would end up with $12768.73 by the end of year two.
In Excel I am doing this year on year by calculating the following:
firstMonth = $500
intRate = 5%
infRate = 2%
First I calculate the monthly payment value:
pmt = firstMonth * ((1 + infRate) ^ (year -1)
Then I calculate the interest Rate per month
rate = intRate / 12
Then I calculate the Future Value for the first year using:
ABS(FV(rate, nper, pmt, pv, type))
equates to
ABS(FV((0.05/12),12,500,0,1))
This gives me the correct value of $6165.01 = pv
Then for each subsequent year I recalculate pmt ($510) and put it into a new FV calculation passing in the previous years balance (pv)
ABS(FV((0.05/12),12,510,6165.01,1))
This gives me the correct value of $12768.73
I then carry on until I reach the number of years required for the plan.
Is there any way to calculate the Future Value without having to loop through each year passing in the current balance and the new monthly payment value for that year. So that I just have one equation where by I can easily try different scenarios of number of years, first payment etc.
The closest I have found is http://www.calculatorsoup.com/calculators/financial/future-value-calculator.php but that doesn't allow for having the same payment value across the whole year.
Any help would be gratefully appreciated.
Best Answer
Answer:One direct formula may not be possible but I have derived a formula from cashflows and you can see below.
If you need the EXCEL worksheet to culculate, let me know your email address, I shall send it to you.
Thanks Satish