2

I found a retirement fund calculator on web:

                 Inflation rate:       2%          0%          2%          0% 
           Expected return rate:       5%          0%          0%          5%
                    Current age:       30          30          30          30
                 Retirement Age:       65          65          65          65
         Years after retirement:       15          15          15          15
            Monthly expenditure:   $10000      $10000      $10000      $10000
                                ----------  ----------  ----------  ----------            
Required fund at retirement age: $2961797    $1800000    $4150191    $1307837

I'd like to know how I can reproduce the calculation formulas in Excel?

  • 3
    Plain compound interest formula. – littleadv Dec 5 '13 at 4:34
  • @littleadv could you please post the compound interest formula in an answer? – ohho Dec 9 '13 at 2:02
  • Being european, I would use A = P*(1 + r)^(n*t) where A is the value after t years, P is the initial amount (the principal), n is the number of compounding periods per year, and r is the periodic rate, r = ((1 + APR/100)^(1/n)) - 1 where APR is the annual percentage rate. – Chris Degnen Dec 9 '13 at 10:47
  • @Chris Degnen I was looking at your answer, how different would this be for the US inflationary rate and using US dollars. Just what would have to changed to use this in the US. Thanks – Bob Brooks Apr 5 '15 at 15:14
  • @BobBrooks As long as you use the right type of interest rate it will work. My calculation uses the effective interest rate which can easily be calculated from a nominal rate. – Chris Degnen Apr 5 '15 at 17:02
5

I wasn't able to get a formula that matched those numbers precisely, but I think I got close enough to what you are looking for.

If your first column of numbers was B1:B6 the formula in B7 would look like this:

=PV((1+B2/1+B1-1)/12,15*12,-FV(B1,B4-B3,,-B6),,1)

The first argument to the PV function is the inflation adjusted rate of return, the second is the number of monthly expeditures, the third is the inflation adjusted monthly expenditure, and the fifth signifies money required upfront.

The fv function calculates inflation adjusted monthly expenditure from the inflation rate, years until retirement, and the current value of the monthly expenditure.

I don't know what I missed but my numbers are close, so you can get a good idea from this formula.

| improve this answer | |
5

I have put together a method for assessing the size of the pot. It uses the european method of compounding though.

I realise it doesn't use Excel, (actually I'm using Mathematica), but the calculations are fairly legible and it should be quite straightforward to implement the method using other tools.

The calculation assumes the pension payouts are in present value terms, so if you wanted £10,000 in 35 years, accounting for inflation at 2% that would be £19,998.90.

I've gone through the steps first with small numbers:-

Planning to retire in 4 months and draw monthly income of £1000 (present value) for 3 months, adjusted for inflation. APR is 8% and inflation is 4%. What should the pot be?

enter image description here

enter image description here

Therefore need a pot at month 4 that can deliver these payment at months 4, 5 & 6 with APR = 8%

enter image description here

So for the root calculation t[0] is 1, t[1] is 0.5 and t[2] is 0.

Ref: http://en.wikipedia.org/wiki/Rate_of_return#Internal_rate_of_return

enter image description here

Checking: amounts remaining in the pot after month n, using monthly rate m

enter image description here

Pot is empty after month 6, so everything seems to work.

Edit

After reviewing this calculation, it can be done more simply:-

inf = 0.04;
i = (1 + inf)^(1/12) - 1

0.00327374

apr = 0.08;
m = (1 + apr)^(1/12) - 1

0.00643403

calc = Function[n, 1000 (1 + i)^n (1 + m)^-(n - 4)];
discountedpayouts = Map[calc, {4, 5, 6}]

{1013.16, 1009.98, 1006.81}

pensionpot = Total[discountedpayouts]

3029.94

The same method can be used for the calculation over 50 years.

End of edit

Now running a generalised version with £10,000 (present value) payouts with inflation at 2% and APR 5%, as specified by the OP.

enter image description here

This finds a pot requirement of £2,922,808 which is a little different to the OP's result, most likely due to the difference between the US and EU formulations of APR and compounding methods.

Switching inflation and APR to 0% yields £1,800,000.

Inflation at 2% and APR at 0% yields £4,188,098.

Inflation at 0% and APR at 5% yields £1,279,041.

I'll try to post an inflation based calculation for the deposits later.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.