Go Back   English Forum Switzerland > Help & tips > Finance/banking/taxation  
Reply
 
Thread Tools Display Modes
  #1  
Old 08.02.2013, 11:27
Momus's Avatar
Member
 
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
but
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 ...

Matthew
__________________
It's not God I have a problem with, it's the fanclub ...
Reply With Quote
  #2  
Old 08.02.2013, 11:35
dodgyken's Avatar
Forum Legend
 
Join Date: Apr 2010
Location: Democratic Republic Kenistan
Posts: 10,654
Groaned at 280 Times in 231 Posts
Thanked 19,403 Times in 7,402 Posts
dodgyken has a reputation beyond reputedodgyken has a reputation beyond reputedodgyken has a reputation beyond reputedodgyken has a reputation beyond reputedodgyken has a reputation beyond reputedodgyken has a reputation beyond repute
Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

1,236/1.03 = 1200
Reply With Quote
The following 3 users would like to thank dodgyken for this useful post:
  #3  
Old 08.02.2013, 11:40
Forum Veteran
 
Join Date: May 2009
Location: suburbs of LA, USA
Posts: 934
Groaned at 11 Times in 10 Posts
Thanked 873 Times in 439 Posts
BrianJW has a reputation beyond reputeBrianJW has a reputation beyond reputeBrianJW has a reputation beyond reputeBrianJW has a reputation beyond repute
Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

so you want to know the equivalent of 1.2m in 25 years from now with 3% inflation?

I make it 2.5m. You are basically applying compound interest.

There would be a formula Im guessing it`s 1.2m * (1.03 to the power of 25) don`t know how to express that on computer.

i just take 1,200,000 x 1.03 year 1, take that answer (1,236,000) and multiply X 1.03 and you get the answer year 2 (1,273,080) in 2 columns repeat this start amount, end amount 25 times you get 2,512,534.

Hope that helps.

ps and if you want 5,000 of todays money in 25 years time you will need 10,469.

Last edited by BrianJW; 08.02.2013 at 11:43. Reason: added ps
Reply With Quote
This user would like to thank BrianJW for this useful post:
  #4  
Old 08.02.2013, 11:46
kiwiguy08's Avatar
Forum Veteran
 
Join Date: Apr 2008
Location: Horgen
Posts: 1,315
Groaned at 28 Times in 20 Posts
Thanked 940 Times in 466 Posts
kiwiguy08 has an excellent reputationkiwiguy08 has an excellent reputationkiwiguy08 has an excellent reputationkiwiguy08 has an excellent reputation
Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

For such a relatively easy calculation why not use this retirement planner:

http://www.smartmoney.com/retirement/planner/

You can say how much money you want in retirement, the interest rate you expect to get, and inflation by year. You can even set the inflation rate for housing transportation, etc.
Reply With Quote
The following 2 users would like to thank kiwiguy08 for this useful post:
  #5  
Old 08.02.2013, 12:24
Momus's Avatar
Member
 
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
Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

Thanks for your replies -

I understand what you mean about the compound interest. Take my $1,200,000. In one year at 3% interest this will be $1,236,000 ( X 1.03)
However, if take $1,236,000 and minus 3% I get $1,198,920 ( X 0.97 )

BUT, as suggested, if I divide by 1.03 instead of multiplying by 0.97 I can work both backwards and forwards with the number - and get my spreadsheet to work. Awesome.

It's really simple when you can "see" it - thanks to both of you for pointing it out

Now, I'm just not sure what to do to get about 2 million bucks asap ...
__________________
It's not God I have a problem with, it's the fanclub ...
Reply With Quote
  #6  
Old 08.02.2013, 12:32
Momus's Avatar
Member
 
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
Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

Quote:
View Post
For such a relatively easy calculation why not use this retirement planner:

http://www.smartmoney.com/retirement/planner/

You can say how much money you want in retirement, the interest rate you expect to get, and inflation by year. You can even set the inflation rate for housing transportation, etc.
Nice, thanks Kiwiguy - that's a super site.
Reply With Quote
  #7  
Old 08.02.2013, 12:40
Chemmie's Avatar
Forum Legend
 
Join Date: Sep 2009
Location: Zurich
Posts: 4,144
Groaned at 33 Times in 29 Posts
Thanked 4,951 Times in 2,233 Posts
Chemmie has a reputation beyond reputeChemmie has a reputation beyond reputeChemmie has a reputation beyond reputeChemmie has a reputation beyond reputeChemmie has a reputation beyond reputeChemmie has a reputation beyond repute
Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

This thread made my friday

beat the Order of Operations threat from a while back
Reply With Quote
This user would like to thank Chemmie for this useful post:
  #8  
Old 08.02.2013, 13:33
Forum Veteran
 
Join Date: Aug 2011
Location: Bern
Posts: 852
Groaned at 5 Times in 4 Posts
Thanked 1,389 Times in 527 Posts
heckenhocker has a reputation beyond reputeheckenhocker has a reputation beyond reputeheckenhocker has a reputation beyond reputeheckenhocker has a reputation beyond reputeheckenhocker has a reputation beyond repute
Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...

Quote:
View Post
Now, I'm just not sure what to do to get about 2 million bucks asap ...
I have some old Turkish lira, some Indian rupees and a couple of Hong Kong dollars if that helps?

but look, you got help with the maths...you can't expect the forum to do everything for you. We've left you the easy bit
Reply With Quote
Reply

Tags
excel, formula, nightmare, pension




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Excel Trikk General off-topic 14 24.10.2012 19:52
Need help with transit planning Tonino Transportation/driving 9 18.07.2012 20:33
Euro 2012 Excel Spreadsheet Coney Island Sports / Fitness / Beauty / Wellness 21 28.06.2012 22:39
2010 World Cup Excel Spreadsheet Coney Island Football/sports 6 27.05.2010 07:13


All times are GMT +2. The time now is 16:11.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO 3.1.0