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

Mar 14, 2012

Life is Nonlinear, so is Risk!

From the day we were born, we've learned to survive in a complex world by applying linear mechanisms in life:
  • On a short time scale things don't change much
  • The future can be predicted by extrapolation of the past
  • Every event now, must have a cause in the past
  • Results are a (linear) combination of events in the past

Linear Thinking
In line with our linear culture, we - actuaries, (risk) managers, investment consultants or asset managers, etc. - have applied this way of linear thinking in our professional field:
  • Mean reversion: Returns continue to go back to an average value over time
  • Volatilities are more or less constant in time
  • Increasing volatility is a good predictor of an upcoming financial crisis
  • Standard deviation is similar to risk or volatility
  • If a distribution is complex, a normal distribution nevertheless will do fine
  • Tail risks are not really interesting or can't be modelled anyway

More detailed psychological linear thinking in the Risk area...

  • Peer Risk: If all other professionals (institutions) are using a certain method or investment strategy, why should I take the risk of developing a new one?
  • First Mover Risk: Why should I act first and carry all research investments?
  • Supervisory Compliant:If the regulator prescribes new regulations, I'll apply those regulations as if it is my own risk appetite.
  • Big Brother Hedge Risk: I base my investment strategy at a save distance on the biggest leader in the market. Might trouble arise, the Regulator first has to deal with my Big Brother.
  • Regulation Risk: Regulation (change) is perceived as a given fact and not viewed or managed as a kind of risk
  • Risk of Free Rate Risk: There must be some kind of risk free interest rate.

Thinking long term and two steps deeper, it's obvious that applying any of the above mentioned linear thinking methods will likely be the nail in the coffin of any financial institution.

Linear thinking and modelling make our daily life more simple. Unfortunately, 'too simple' to cope with financial markets reality on a long term. 

Metamorphosis by Escher...
If we are lucky, (market) circumstances only change slowly and we're able to adapt the value of the variables in our linear models gradually, while  keeping our traditional way of linear thinking and modeling.  We act just like the famous graphic artist Escher shows us in Metamorphosis...

If we are less 'lucky' (as we are in2012), our linear models all of a sudden seem to fail. Covariances and volatility increase. Systemic risk shows up everywhere and a 'risk free rate' turns out to be an illusion. Our risk dashboard is on fire and we'll have to admit: our linear MPT models are failing.

Navigation Risk Parable
Why is it so so hard to admit that our linear models fail?

Suppose you developed a 2D linear (x,y)-navigation app in your Florida flatland office. Your app works fine for years. Than you decide to visit Black Hills & Badlands of South Dakota. Suddenly your app seems to fail in the mountains. Travel times and distances on your display suddenly seem wrong.

You realise you urgently need to develop a nonlinear 3D (x,y,z)-navigation app.... However, you don't do it.

Why not?

Well, first of all your old linear 2D app worked fine for years and on short trips the app still works (approximately) fine.

Besides, nobody of your Californian friends uses a 3D app and developing a new nonlinear app is very expensive.

Well, it's time we realise that most developments in life are in fact nonlinear.
If the stakes are high, like in the investment business, linear models will eventually lead us to a disaster.

Summarised, we might conclude:

If life is Nonlinear, so why aren't our models?

New Solutions
What alternatives do we have for our old linear model?

Although there are many nonlinear models, I'll emphasize on two interesting nonlinear based models in this blog.

I. Predicting economic market crises using measures of collective panic
Is there an adequate predictor of a market crisis?

Using new statistical analysis tools based on complexity theory, the New England Complex Systems Institute (NECSI) performed a new research on predicting market crashes.

As we know volatility is a measure of risk. So one would expect an increase of volatility also to be an adequate predictor of a financial market crash. Unfortunately this is not the case, as is shown in the recent NECSI study. While volatility increases at the beginning of a crisis, it is unreliable as an adequate indicator of a nearby market crash.

What also is not true, is that a market crash is often triggered by market panic justified, or not justified, by external (bad) news.

The NECSI research indicates that it's the internal structure of the market and not an external crises, that's primarily responsible for a market crash.

It turns out that the number of different stocks that move up (U) or down (D) together is an indicator of the mimicry ( 'collective flight'; herding) within the market. When mimicry is high, many stocks follow each other's movements.

This "co-movement" of stocks  is an indicator of a nervous market that is ripe for panic. The existence of a large probability of co-movement of stocks on any given day, is a measure of systemic risk and vulnerability to self-induced panic.

So, rather than measuring volatility or correlation, the fraction of stocks that move in the same direction turns out to be a successful predictor of a market crash..

NECSI researchers showed that a dramatic increase in market mimicry occurred during the entire year before each market crash of the past 25 years, including the recent financial crisis.

II. Worst-Case Value-at-Risk of Non-Linear Portfolios 
We all know that VaR lacks some desirable theoretical properties:
- Not a coherent risk measure.
- Precise knowledge of the distribution function is critical
- Non-convex function of w → VaR minimization intractable
- To optimize VaR we have to resort to VaR approximations
- Normality assumption is unrealistic → may underestimate the actual VaR.

Zymler, Kuhn & Rustem of the Department of Computing Imperial College London now developed a nonlinear alternative for VAR, called

Worst Case Var

Two variations on WCVar lead to practical applications:

  1. Worst-Case Polyhedral VaR (WCPVaR)
    A polyhedral VaR approximation for portfolios containing long positions in European options expiring at the end of the investment horizon

  2. Worst-Case Quadratic VaR (WCQVaR)
    A suitable VaR approximation for portfolios containing long and/or short positions in European and/or exotic options expiring beyond the investment horizon.

Here's an example of WCQVar's results against  WCVar (plain) and the good old 'Monte Carlo Var' we mostly use in linear modeling. This graph needs no further comment.....

Using the WCQVar leads to more realistic modeling results. WCQVar-techniques can also be used for for index tracking leads to spectacular results (see pdf).

Worst-Case Value-at-Risk of Non-Linear Portfolios

After so many years of relative successfully using linear models, it's hard to recognize that we need new models based on new nonlinear approaches.
Therefore we need 'first movers'. Who's willing to take the risk and jump into the nonlinear deep-sea?

Be confident and stay on your happy feet.. after a successful jump, 'herding theory' tells us others will follow...

Sources & Related Links
- Predicting economic market crises using measures of collective panic (PDF)
NECSI Research (2011)
- Self-Induced Panic And The Financial Crisis 
- Worst Case Var Document (PDF; 2011)
- Worst Case Var Document (PDF;2009)
- Order Happy Feet Video 

Mar 4, 2012

EU: Risk Management Alert!

As faithful risk managers and actuaries, we got used to act in a compliant and regulated governance environment. While we are doing our work like buzzy bees, above our heads a disastrous and horrific risk management game takes place, it's called:


In order to end the European financial crisis, a new intergovernmental organisation, the 'European Stability Mechanism' (ESM), will be set up in Luxembourg under public international law .

Euro member countries already agreed and are now waiting for an approval of the EU-countries'  parliaments. The latest version of this ESM treaty has been signed on 2 February 2012 and is scheduled to enter into force on 1 July 2012.

The first ESM guarantee layer has been set at € 700. Here are the sustainable shareholders:

ESM Power & Effects
The ESM severely confines the economic sovereignty.

It violates democratic principles of its member states and provides extensive powers and immunity to the board of ESM Governors without parliamentary influence or control.

It's remarkable and unintelligible that even the European Parliament has no control over the ESM!

In short the power and effects of ESM as defined in the ESM 2012 document, are:

  • ESM may demand an unlimited amount of money from European countries (9.1 and 9.2)

  • In case of a demand, countries have to pay within seven days, without any negotiation or discussion. (9.3)

  • ESM is not accountable for what happens to the money; they’re allowed to make high-risk investments. (24.3)

  • ESM has the power to reduce private customer savings of bank accounts cross country without permission of the countries' parliaments or any interference from the countries' governments. (12)
    In accordance with IMF practice, in exceptional cases an adequate and proportionate form of private sector involvement shall be considered in cases where stability support is provided accompanied by conditionality in the form of a macro-economic adjustment programme.

  • There are no compliance or control measures defined with regard to ESM. Further, ESM has no targets, cost-limitation and enjoys complete immunity. (32)

  • The ministers of Finance will take a seat in the ESM Board of Governors. There they will receive a salary exempt from taxes.

  • The money supplied by all European countries will be used to save mainly the large (too big to fail) France and German banks with loans in weak European countries like Greece, Portugal, Italy and Spain. The people in those countries will not benefit at all from the money supply.

Netherlands Court of Audit Alert
The president of the Netherlands Court of Audit has written an alert letter to her Euro area colleagues and the president of the ECA regarding this new treaty. The aim of the letter is to contribute to the preparation of the next ESM-meeting of Euro area SAIs on 14 March 2012 in Bonn.

The letter addresses the next shortcomings:
  • There is no reference to the use of international audit standards in audits by the Board of Auditors
  • The different types of audit that should be possible for the Board Of Auditors – regularity, compliance, performance, risk management – are not explicitly mentioned
  • The possibilities for open dissemination of audit results by the Board of Auditors are limited. The Board of Auditors can establish one annual report,which the Board of Auditors cannot send itself to national parliaments and SAIs. This has to be done by the Board of Governors. 

It's clear that it looks fishy.....

Let's hope our parliaments will show some governance sense before it's

too late....

Otherwise most of European people's savings will eventually be used to fill the endless financial gap of those European countries that are not able of mastering their own financial future....

And how many countries will that be?????

- ESM 2012 Documen9 (English)
- ESM 2012 Document (Dutch)
- Austria: Objections and Reservations to ESM 
- The EU architecture to avert a sovereign debt crisis (2011) 
- ESM: Technical (PDF/PPT)
- Alert Letter Netherlands Court of Audit 
- Interview (Dutch audio) with Albert Spits

- Dutch protest