1
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

Answer:One direct formula may not be possible but I have derived a formula from cashflows and you can see below.

enter image description here

enter image description here

If you need the EXCEL worksheet to culculate, let me know your email address, I shall send it to you.

Thanks Satish

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.