English Forum Switzerland

English Forum Switzerland (https://www.englishforum.ch/forum.php)
-   Finance/banking/taxation (https://www.englishforum.ch/finance-banking-taxation/)
-   -   Need help with Pension Planning on Excel Spreadsheet - Formula headache ... (https://www.englishforum.ch/finance-banking-taxation/168969-need-help-pension-planning-excel-spreadsheet-formula-headache.html)

Momus 08.02.2013 12:27

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 :mad: 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 :msnnerd: and will have much less of a headache ...

Matthew

dodgyken 08.02.2013 12:35

Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...
 
1,236/1.03 = 1200

BrianJW 08.02.2013 12:40

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.

kiwiguy08 08.02.2013 12:46

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.

Momus 08.02.2013 13:24

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 :msngrin:

Now, I'm just not sure what to do to get about 2 million bucks asap ...

Momus 08.02.2013 13:32

Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...
 
Quote:

Originally Posted by kiwiguy08 (Post 1792557)
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.

Chemmie 08.02.2013 13:40

Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...
 
This thread made my friday :D

beat the Order of Operations threat from a while back :p

heckenhocker 08.02.2013 14:33

Re: Need help with Pension Planning on Excel Spreadsheet - Formula headache ...
 
Quote:

Originally Posted by Momus (Post 1792598)
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 :msngrin:


All times are GMT +2. The time now is 05:28.

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