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.

Graphics

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).

Options

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

Enjoy!

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...

The Life Expectancy Variance Monster

After 'age', what would be the most important explanatory factor with regard to mortality rates or constructing life tables?

As actuaries we've demonstrated our innovation capabilities by developing life tables not only based on 'age' and 'gender', but also (two dimensional) on 'time', 'generation' and 'year of birth'. This helped us to extrapolate future mortality rates in order to predict future longevity with more accuracy.

However, despite our noble initiatives, these developments turn out to be insufficient to put the Longevity Variance Monster back in his cage.

Modern 'life expectancy at birth' predictions for periods of 40 to 50 year ahead, lead to 95% confidence intervals of 12 years or more. Unusable outcomes .....

Let's not even discuss more necessary accurate confidence intervals of 99% or more ....

In our attempt (duty?) to moderate and diminish future life expectancy variance, we'll have to develop new instruments.

The more we know which risk factors 'are responsible for the increase in 'life expectancy', the better we can estimate and diminish future variance.

One of those new approaches is to calculate life expectancies on basis of postcodes.

This new insight can be helpful, but there's a much more important risk factor that has to be included in our life expectancy predictions to definitely kill the Longevity Variance Monster:

Self-perception of aging

In a 2002 research "Longevity From Positive Self-Perceptions" by Levy ( et al.) it became undeniable clear that:

• negative self-perceptions diminish life expectancy;
• positive self-perceptions prolong life expectancy.
  

Older people with more positive self-perceptions of aging, measured up to 23 years earlier, lived on average (median survival) 7.6 years longer than those with less positive self-perceptions of aging. This advantage remained after age, gender, socioeconomic status, loneliness, and functional health were included as covariates.

Top 6 Life Expectancy Risk Factors

Here's Levy's top 6 list of risk factors on life expectancy (ordered from greatest to least impact on life expectancy):

1. Age
2. Self-Perceptions of aging
3. Gender
4. Loneliness
5. Functional health
6. Socio-economic status

As we can not change 'age' nor 'gender', let's put some more research on the other risk factors.

Once we achieve to 'explain' the cause of increase of life expectancy on basis of 'new' (soft) risk factors, we - as a society - will also be able to manage life expectancy better (information, education, training, coaching, etc.).

In this way actuaries can help society so that people live longer and stay happy in good health. All on basis of of a sound financial pension and health system, as predicted life expectancy will show a smaller variance.

Help to kill the Life Expectancy Variance Monster.....

Happy 2011, with better expectations and smaller variance!

Sources/related Links:

- Why population forecasts should be probabilistic
- On line Postcode Life Expectancy Tool
- Longevity From Positive Self-Perceptions
- Predicting successful aging (2010)

The first Actuary

As the story goes, insurance began around 1688 at a coffeehouse in London called Lloyds, where shipman discussed and divided their risks.

That 'explains' the birth of non life insurance.

But what about life insurance?

Who developed the first life table?

Answers...
The answer to this question depends on who you ask...

• Definitely Graunt in 1662 (statistical analyzes of data)
  
• Surely De Witt in 1671 (life insurance tables)
• Undoubtedly Halley: 1693 (life insurance tables)

Depending on what you define as a life table, answering this question often leads to a never ending semantic discussion.

Don't worry, there's help... In his Google-book, "A history of probability and statistics and their applications before 1750", Anders Hald explains the origin and development of life tables.

First Life Tables
An indeed, the 'first' life tables, based on more or less empiric data and interest rates were developed at the end of the 17th century.
The first actuary....
However, already in the 3rd century the Roman jurist Ulpian devised a table for the legal conversion of a life annuity to an annuity certain.
It was pointed out by Greenwood that the valuation (duration) of the annuities was deliberately chosen to high, in order to protect the interests of the legal heir.

This would implie that Ulpian not only did a tremendous job by estimating life annuities, but also developed and applied the first primitive 'Solvency Zero' principles...

With his 'simple table', Ulpian was more than ahead of his time.

So, we may rightfully conclude that the one and only first actuary was a jurist: Domitius Ulpianus, alias Ulpian

Strange that it took more than 1500 years to develop more sophisticated life- and annuity tables.

Related links & Sources:

• Early uses of Graunt's Life Table
• A history of probability and statistics and their applications before 1750
• Early Insurance Mechanisms and Their Mathematical Foundations