Showing posts with label Pension Calculator. Show all posts
Showing posts with label Pension Calculator. Show all posts

Nov 11, 2013

QIS: Longevity Risk Sharing

In a recent discussion about the future and fundamentals of the Dutch pension system I discussed the importance of solidarity.

As expected, the participants quickly came up with the various forms of solidarity, including solidarity between:
– higher and less educated people
– women and men
– old versus young people

Longevity Risk Sharing
Remarkably non of the participants had any idea about the financial impact of one of the most fundamental forms of risk sharing in case of a life annuity: Longevity Risk Sharing. Let's call it in general 'mortality solidarity'.

When asked, most participants strongly underestimated the impact of mortality (mortality share) as part of the yearly payment in the form of a life annuity. On the other hand, they overestimated the impact of 'return'.

Some of the participants had the idea that they would be 'better of' with a traditional individual investment plan in combination with a little more investment risk (and return) ...

Life Annuity Composition
So let's do a mini QIS (Quantitative Impact Study) of 'mortality solidarity' by examining the development of the composition of an annual lifetime annuity, regarding three basic elements: Mortality, Return and Desaving.

Here is the result for a Dutch man, age 65, with a lifetime annuity based on an average 5% yearly return:

Translated in table form:

Yearly Payment CompositionCumulative Composition
AgeMortality Return DesavingMortality Return Desaving

As is clear from the table above :
  • Already at the start the start of the annuity, at age 65, 16% of the yearly payment is due to mortality risk sharing and 'only'  51% is related to the 'return'.
  • As a pension member continues to live, the  'mortality share' of the annual payment increases. At the age of 83 already 50% of his annuity is due to mortality effects and the 'return share'  is already down to 22%.
  • As from age 77 of, the 'mortality effect' on the annual payment exceeds the 'return effect'.

From some simple calculations, we can conclude that longevity (mortality) solidarity is a fundamental part of a life annuity.

Make your calculations with other interest rates, ages or life tables with the Pension Calculator (Excel).

You may download the pension calculator HERE


Nov 17, 2012

Pension for Contribution

People are lost if it comes down to their pension. A recent (2012) Friends Life survey found that 68% of Britons do not know the collective value of their pension funds.....

This result is in line with a Dutch 2011 survey, that concludes that 66% has no knowledge of their pension.

Pension illiteracy is clearly a worldwide phenomenon. Pensions are a 'low interest' product. Unfortunately - nowadays - in the double sense of the latter words.

As an actuary, people often ask me at a birthday party : I'm paying a 1000 bucks contribution each year for my pension, but does it pay out in the end? Can you tell me?

Unfortunately most actuaries, including myself, answer this question by telling that this is a difficult question to answer straightforward and that the pension outcome depends on topics like age, mortality, return, inflation, gender, indexation, investment scheme, asset mix, etc., etc.....

To make a breakthrough in this pension communication paradox, let's try to create more pension insight with a simple approach. But remember - as with everything in life - the word 'simple' implies that we can not be complete as well as consistent at the same time. After all, Kurt Gödel's incompleteness theorems clearly show that nothing in life can be both complete and consistent at the same time.

Thanks to God and Gödel, we can stay alive on this planet by simplifying everything in life to a level that our brains can comprise. We'll keep it that way in this blog as well.

How much pension Benefits for how much contribution?
First thing to do, is to give the average low pension interested person on this planet an overall hunch on what a yearly investment of a 1000 bucks(first simplification: S1)until the pension age of 65 year (S2) delivers in terms of a yearly pension as of age 65 in case of an average pension fund.

If we state 'bucks' here, we mean your local general currency. We denote 'bucks' here simply as $, or leave it out. So $ stands for €, ¥ , £ or even $ itself.

Now let's calculate for different pension contribution start ages (S3)what a yearly contribution of $ 1000 (payable in months at the beginning of each month; S4), pays back in terms of a yearly pension (payable in months at the end of each month; S5) on basis of a set of different constant return rates (S6). The calculation is on a net basis (so without costs; S7), a Dutch (2008) mortality table (S8) and without any inflation (S9), any pension indexation (S10), any contribution indexation (S11), or any tax influence (S12).

Here's the simple table we're looking for:

Yearly Pension at age 65 on basis of 1000 yearly contribution
Pension Indexation=0%, Contribution Indexation=0%, Inflation: 0%
StartNet Yearly Return Rate

In a graphical view on a logarithmic pension benefits scale, it looks something like this:

To illustrate what is happening, a simple example:
When you join your pension fund at age 40 and start saving $ 1000 a year (the first of every month: $ 83.33) until your 65, you'll receive a yearly pension benefit of $ 4092 yearly ( $ 341 at the end of every month) from age 65 of, as long as you live.

From this table, we can already draw some very basic conclusions:
  • To build up a substantial pension, it pays out if you start early in life
  • The pension outcome is heavily dependent on the yearly return of your pension fund
  • Most pension funds operate on basis of a 'general employee and/or employer contribution' instead of individual employee contributions.
    This implies that younger employees pay more than they should have paid on an individual basis and older employees less. In other words, younger employees subsidize older employees. How much more, you can derive from the tables above and by comparing the individual contributions to the general contribution level of the pension fund.

Pension Indexation
As we all want to protect our pension against inflation, let's calculate the outcome of a 'real pension' instead of a 'nominal pension'. As long term yearly inflation rates vary between 2% and 3%, we make the same calculation as above, but now the yearly pension outcome (as from age 65) will be indexed with 3% (fixed) at the end of every year and the yearly contribution paid, will also be yearly indexed with 3%.
Here's the outcome:

Yearly Pension at age 65 on basis of 1000 yearly contribution
Pension Indexation=3%, Contribution Indexation=3%, Inflation: 0%
StartNet Yearly Return Rate

To get grip at the comparison between a real and a nominal pension, we express the real pension (3% Indexed Pensions and Contribution) as a percentage of the nominal pension:

Yearly Pension at age 65 on basis of 1000 yearly contribution
'3% P&C-Indexed Pensions' as percentage '0% P&C-Indexed Pensions'
StartNet Yearly Return Rate

From this last table we can conclude that if you start saving for your pension below the age of 40 your indexed savings weight up to the indexed pension. Above the age of 45 it is the other way around.

The above figures are the kind of figures (magnitude) you'll find on your benefits statements. You can compare in practice whether your benefit statement is in line with the above tables....

The Inflation Monster
In the last given example, pension is 3% inflation protected as from the moment of retirement.

However, if pension is not also yearly fully indexed (in this case: 3%) during the contribution period, there still is a major potential inflation erosion risk left.

In this case it's interesting to examine what the value of a 3% indexed pension in combination with a 3% indexed contribution is worth in terms of actual money, as inflation would continue at a constant 3% level each year. Here's the answer:

Yearly Pension at age 65 on basis of 1000 yearly contribution
Pension Indexation=3%, Contribution Indexation=3%, Inflation: 3%
StartNet Yearly Return Rate

What we notice is a substantial inflation erosion effect as the pension fund participants get younger.
Let's zoom in on an example to see what we can achieve with these tables.

  • From table 2 we can conclude that - at a 4% return rate - a 40 year old starting pension fund member, with a $ 1000 dollar yearly 3% indexed contribution will reach a 3% yearly indexed pension of $ 4344 yearly at age 65.
  • From table 4 we can subsequently conclude that, based on an inflation rate of 3%, this $ 4344 pension has a 'real' value of $ 2075, if it's expressed in the value money had when the participant was 40 years old (so, at the start).
  • From table 4 we can also conclude that in order to 'compensate' inflation erosion for this pension member, the pension fund has to achieve a return of around 7.4%.
    This follows from simple linear interpolation:
    7,4% = 7% + 1% * (4344-3997)/(4959-3997)

I'll leave other examples to your own imagination.

The effect of a constant inflation on a pension is devastating, as the next table shows

Inflation Erosion
  • Pension indexation=3%
    as of age 65
  • Contribution indexation=3%
  • Inflation=3%
From table 5 it becomes clear that Inflation erosion is indeed substantial.
If you have a fully indexed pension from age 65 (who has?) of and you're N years away from your retirement, an inflation of i% will erode your pension with E%. In formula:
Set inflation to 3%. If you're 40 years old and about to retire at 65, you've got 25 years (N=25=65-40) ahead of you.

If your pension of let's say $ 10,000 a year is not indexed during this period, you can buy with this $ 10,000 no more than you could buy today with $ 4,800.

Your pension is eroded due to inflation with 52% = 1- 1.03^-25. So only 48% is left.....

I trust these tables and examples contribute a little to your pension insight. Just dive into your pension, it's financially relevant and certainly will pay out!
Remember that all results and examples in this blog are approximations and simplifications on a net base (no costs or taxes are included). In practice pension funds or insurers have tot charge costs for administration, asset management, solvency, guarantees, mortality risk, etc. . This implies that in practice the results could differ strongly with the results as shown in this blog. The examples in this blog are therefore for learning and demonstration purposes only.

The above calculations were made in a few minutes with help of the Excel Pension Calculator that was developed in 2011 and updated in 2012.
With help of this pension planner you can calculate all kind of variations and set different variables, including different mortality tables (or even define your own mortality table).

You can download the pension calculator for free and make your own pension calculations.
More information about pension calculating with this simple pension calculator at:

Enjoy your pension, beware of inflation....

Links & Sources:

Mar 30, 2012

Excel Pension Calculator

Why isn't there just a simple pension Excel calculator on the internet, so I can do my own pension planning?

Well..., from now on there is!

Simply download the Excel Pension Calculator (allow macro's !!) and get an idea of how much you'll have to invest to end up with the pension benefit level of your dreams.... or less... ;-)

Or..., just fill in how much you can afford to invest monthly and see for yourself what pension benefit level is within reach, based on expected return rates, investment methods and inflation.

Just to give a small visual impression of the calculator...

Press on 'Calc' buttons to calculate the variable to the left, while leaving all other variables constant.

Also some modest graphics are available. A small example....
Take a look at the next graph that shows how your yearly pension is yearly  funded by:
  1. the yearly desavings (= dissavings) from your saving account
  2. the yearly addition from the pension fund (= estimated savings of pension fund members that will die in this year)
  3. the yearly return on your saving account

Notice the immense impact of the (yearly increasing) addition of your pension fund (= savings of the active members who are expected to die in a particular year and contribute to the savings of your account) compared to the other components (desavings and returns).

The calculator offers several interesting options:
  • Set the calculator to 'Saving Account' instead of 'Pension Fund' to notice the difference in outcomes between these two systems.
  • Switch to the life table of your choice (p.e.  the country where you live)
  • Set and name your own personal Life Table or Investment Scheme
  • Simulate longevity effects by manipulating the Life Table Age Correction field

The Excel Pension Calculator has much more features. More than I can handle in this blog. Just download the calculator and play with it to really touch base and to learn what pension is all about....

- Download the Excel Pension Calculator


Disclaimer: This pension Calculator is just for demonstration purposes. The accuracy of the calculations of this calculator is not guaranteed nor is its applicability to your individual circumstances. You should always obtain personal advice from qualified professionals. Also take notice of the disclaimer in the Excel Pension Calculator.

P.S. I : On request a Quick Start tip
1. Download Calculator and open Excel Spreadsheet
2. Don't forget to"Enable Macros" !! 
3. Enable iterative calculation; Set Max. Iterations=1000, Max. Change=0.4
3. Change 'Start Age  Contribution' to your actual age
4. Notice that the amount 'Saving Surplus at age 120:' changes
5. Press the 'Calc' button next to 'Contribution' to calculate your Contribution
6. Or, Press the 'Calc' button next to 'Pension'  to calculate your yearly pension
7. Set any other Field as you like and press any of the 'Calc' Buttons   

P.S. II : New update, version 2012.2 on April 4,  including a single premium option.
P.S. III: New update, version 2012.3 on April 20, drop down menus (under Excel-2010) now also operate under Excel-2007 versions...