Calculating Interest after Inflation? - 15 Year History

[GJOB]

Hi All,
New to this part of PH so apologies in advance if I've missed the post but I have searched.

I have an endowment maturing shortly which has been running for the last 15 years.

Is there are spreadsheet that I can use to work out my true gain of the period?

I have a list of historic inflation rates but not sure how to apply them.

TIA
S

[walm]

I think you will have to do it manually.

I would first work out the inflation impact on £1.
After 1 year of 1.5% inflation, a zero REAL return would turn that £1 into £1*(1+0.015) = £1.015
After a second year with 2.6% inflation, a zero REAL return would turn that £1.015 into £1.015*(1+0.026) = £1.041
etc....

So you have a figure for the total impact of 15 years. Say 1.5 for example.

If you invested say, £1,000 and it turned into £3,000 then you need to DEFLATE the £3,000 by the 1.5 you calculated above.
So the £3,000 today is actually worth £3,000/1.5 = £2,000 in 15-years-ago money.
So your REAL interest rate turned that £1,000 into £2,000.

What interest rate is that?
Well I always use the RATE function in excel, for which the formula would be =RATE(15,,-1000,2000) = 4.7%

[NoelWatson]

GJOB said:

Hi All,
New to this part of PH so apologies in advance if I've missed the post but I have searched.

I have an endowment maturing shortly which has been running for the last 15 years.

Is there are spreadsheet that I can use to work out my true gain of the period?

I have a list of historic inflation rates but not sure how to apply them.

TIA
S

IIRC RPI number is currently around 240 - I can get the number 15 years ago from BBG. I'm sure there is something similar for interest rates.

[GJOB]

Well I found a table listing the annual inflation rates and by abusing an annuity calculator I have come to the conclusion that inflation over the period was circa 30% and my investment has returned 33% so it's just kept up with inflation.

I think I'm lucky compared with some of the Endowment horror stories I've been reading.

Of course I could be doing it all wrong...

According to the figures the USA has had inflation of 58% of the same period

[NoelWatson]

GJOB said:

Well I found a table listing the annual inflation rates and by abusing an annuity calculator I have come to the conclusion that inflation over the period was circa 30% and my investment has returned 33% so it's just kept up with inflation.

I think I'm lucky compared with some of the Endowment horror stories I've been reading.

Of course I could be doing it all wrong...

According to the figures the USA has had inflation of 58% of the same period

Assume that you are using RPI, I see 12/10 @ 228.4 and 12/95 @150.7, so around 50% inflation - what numbers are you using?