formula to adjust for inflation, PV of future retirement $$

[bhpeters]

I'm try to figure out the formula for determining the present day equivalent of a future amount of money (adjusting for inflation). Specificially in determining retirement account growth as the years go on, how much would the value of money in X year (eg: 2037) be in today's dollars? I've already got the spreadsheet determining the value of the account based on contributions and rate of rate... but adjusting for inflation I cannot figure out.

I can't seem to find the formula... but it seems like there's got to be a predone template out there for something like this.


cells value
A1 retirement account value
B1 account value in today's dollars

thanks!

Heather

 

[goldfish]

Depends on the rate of inflation.

If the rate of interest is 8% then 100 dollars 20 years from now is worth

=100/(1+.08)^20

in todays dollars

This is the opposite as figuring out how much 100 dollars today would be worth in 20 years with interest would be

=100*(1+.08)^20

Inflation is the same thing except that causes our money to be would less. So if we have 3% inflation then we have

=100/(1+.08)^20*(1+.03)^20

for our first formula

and

=100*(1+.08)^20/(1+.03)^20

for the second.

These formulas will have much different values at different interest/inflation rates. Keep in mind that if inflation=interest that $100 now is worth $100 in 20 years.

Hope this helps,
~Gold Fish

 

[twarden]

BH - this isnt so much an excel question as a finance question. Anyway - as I remember (I'm studing business administration at University) the formula to convert between nominal and real (inflation adjusted) rates of return is:

Rn = Rr + X, where Rn= nominal rate, Rr= real rate & x= inflation

so if you want the inflation adjusted rate of return, use Rr.... the formula (however you choose to set it up in excel) would be: Rr= X-Rn

hope this helps

 

[bhpeters]

ok... so my spreadsheet is showing me an account balance of $1339264.00 in year 2037... but in today's dollars (2006) it would be worth less than $1339264.00 assuming a constant 3% inflation rate per year (I know, not accurate) and constant 8% rate of return...

so the formula is:

1,339,264/(1+.08)^31*(1+.03)^31 = 411,077.00 right?

seems to be reasonable for what I've seen previously. Thank you!

Heather

 

[twarden]

oops my mistake - i was careless with the formula. The correct formula would be:

Rn - X = Rr


If you are wanting to find out how much the account will be worth by retirement (i'm assuming thats when 2035 is) you have to make sure that you use the real rate through all of your calculations, as it affects not only the simple interest & principle, but also the compounded interest. If you want i've slapped together a simple spreadsheet that I don't mind sending you if you give me your email address

 

[bhpeters]

thanks, I sent my email in a PM. So far my results indicate my several million dollar account will allow me to withdrawl about $1000 a month in today's dollars. eek.

 

[bhpeters]

um, wait a minute. this formula

1,339,264/(1+.08)^31*(1+.03)^31 = 411,077
but if I reduce the inflation rate the value of the account goes DOWN instead of UP. what have I screwed up?

 

[pedro-egoli]

The answer I got for
1,339,264/(1+.08)^31*(1+.03)^31

was 308892

If inflation rate was .02 the figure reduces to
227683

At 1% it reduces to
167760

and at no inflation figure is
123332

This figure is simply the present value if a constant 8% interest rate over 31 years where the Future Value is 1339264.


Pedro

 

[mortgageman]

I believe bhpeter's question is: How come, according to the formula provided, the value of money does not vary inversely with the rate of inflation? In other words, he expects the value of his money to INCREASE as inflation DECREASES

Gene, "The Mortgage Man", Klein

 

[goldfish]

Inflation rate reducing makes it easier to save money. You are effectively earning more interest because your money isn't degenerating at the same time. The easier it is to invest, the less you have to put in now to have 3 million later.

Does this makes sense?

It is just the opposite if you were looking at the future value of present cash. In this case 100 now could earn you 150, 5 years from now, but if there wasn't any inflation that would be a higher number.

Keep in mind when you start "considering" inflation, you start talking about real dollars in each year instead of "nominal dollars". (nominal dollars being a 1 dollar bill, real dollars being the number of hotdogs you can purchase or the quality of the house you can afford with that money).

I would love to answer any and all of the questions you have so please keep shooting them out, or let me know if you want me to clarify anything.

~Gold Fish