Pension calculation

Question

I have very difficult problem (for me)

I have to create a pension calculator.

What I know is:

• I am now 30 years old, will stop paying as I am 60 (so I will pay over 30 years) and I will live about 30 years.

• I want to get about 1000 € per month pension BUT including the 2.5 % inflation.

So when I am 60, what is currently 1000 € will be ca. 2097 € and when I am 90, 1000 € today will be ca. 4399 €

Now the question, how much money do I need to get enough money?

It is about 1.157.864 € but I have not calculated the interest of 4.5 % on my money. So it must be less then 1.157.864 €.

My head is exploding....

So, the right question is, how much money I need to havewhen I am 60 to get my last years about 4399 € a month.

Then is the question how my I have to pay monthly to get this amount. And how to calculate it!

If some one can help me, it will be great!

Answer 1

So given a retirement age, a death age, a monthly pension amount, an inflation rate, and an interest rate you want to find out how much money a person would need at retirement to take out the pension amount each month until death.

First, use the inflation rate c and interest rate i to find the effective monthly interest rate x. You need $$\left(1+x\right)^{12}=\frac{1+i}{1+c}$$ and so $$x=\left(\frac{1+i}{1+c}\right)^{1/12}-1$$

In your example, $x\approx0.00161.$ Next, find the number of months $m=12(a_d-a_r).$ (This assumes you retire and die on the same day of different years; you can tweak this as needed if you want to assume, say, retirement in January and death in December.)

For convenience, define $\lambda=1/(1+x)$ as the rate at which future months become cheaper in interest-inflation adjusted terms.

Now the total amount you need is $$\sum_{k=0}^{m-1}p\cdot\lambda^k=p\frac{1-\lambda^m}{1-\lambda}$$

If you want to put that in present dollars rather than retirement year dollars, divide by $(1+c)^y$ where y is the difference between the retirement year and this year.

In your example, that's €273,420.

You could find monthly payments the same way, but I don't recommend that! Earning power is not constant over one's life.

Answer 2

You could write equations for this, assuming your contributions are the same each year or rising at some given rate, but I would just make a spreadsheet. That lets you vary the assumptions much more. For example, maybe you expect to inherit some money, so one year's deposit will be different. Maybe you expect to have children and can make higher deposits before they come along.

Each row will be a year, so you will have 60 rows. The columns will be balance, deposit, inflation this year, cumulative inflation, interest rate, interest earned, withdrawals. Once you write the formulas for the second year, you can just copy them all downward for the 60 lines. Excel has a goal seek function that lets you vary the deposit until the final balance is zero, or you can just vary it by hand until you get close to zero.

Added: If you want a formula, you need to specify how your deposits change over the 30 years. At age $60+n$ you will withdraw $1000(1.025)^{30+n}$ (note, I am applying inflation and interest each year, not every month), so for that year you need $1000(1.025)^{30+n}(1.045)^{-n}$ This can be summed from $n=1$ to $30$ to give $12000(1.025)^{30}\frac{(1.025^{31}-1.025).045}{.025(1.045^{31}-1.045)}$, which should come out $567537$. If your deposits are a fixed amount every year, the one in year k will be worth $1.045^{30-k}$ times as much at age $60$. So we want $d\sum 1.045^k=d\frac{1.045^31-1.045}{.045}=63.752d=567537,$ so your yearly deposit is $8902$

I have done it with hard numbers, but you can probably see where to plug in the variables. You need to think about offsets of $1$-do you make a deposit in year 0, do you make on the last year, etc.