View Single Post
Old 08.02.2013, 12:27
Momus's Avatar
Momus Momus is offline
Join Date: Sep 2007
Location: Lausanne
Posts: 213
Groaned at 4 Times in 3 Posts
Thanked 102 Times in 42 Posts
Momus is considered knowledgeableMomus is considered knowledgeableMomus is considered knowledgeable
Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

Hi there,

Here's one for you ... I am trying to work out some details of my future finance requirements prior to a meeting with my advisor, and I'm getting stuck on an **&&%%ing excel formula I was hoping whether there are any budding maths people who could help me out.

Basically, I have the following info in a spreadsheet

When I retire, I want this amount per month : $5,000

If I assume a healthy 5% yearly return, therefore, I can easily calculate with a formula from this that, I will need a sum of $1,200,000 to give me this. So far so good.

Enter inflation-related calculation nightmare.

I need a formula to say - assume inflation of 3% per year - how much this 1,200,000 will be in 25 years ( or another number I can enter ) .. I can easily work out 1,200,000 + 3% per year but I think I need to somehow discount backwards instead to get the exact answer. For example,

1,200,000 X 1.03 = 1,236000
1,236,000 X 0.97 =NOT 1,200,000

In other words, what future amount at 3% inflation discounted 25 years into the past, is my figure of 1,200,000 today?

Im thinking to use NPV or IRR ( pref IRR to get the exact rate ) but I'm not so sure how to implement this in practice? I know this is a simple calculation something along the lines of

Future Value = Present Value X ( 1 + inflation rate ) ^ 25 years

but i'm thinking I need to use negative numbers somewhere and I understood that IRR doesn't work so well with those...

Can anyone point me in the direction of a good formula to use?

I will be forever in your debt and will have much less of a headache ...

It's not God I have a problem with, it's the fanclub ...
Reply With Quote