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.

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

15 comments:

  1. I have several questions:

    1. How dloes the user change the mortality table?
    2. Is there a way to enter past accumulated contributions and use current age as a starting point? (i.e., 401(K) balance currently has $250K, user is currently 55 years old and plans to retire at age 65). Currently, you have to back into the number by guessing an age and contribution rate. Future contributions might not match reality.
    3. How does the user select the asset mix?

    Your spreadsheet is a welcome addition to actuarial applications!

    ReplyDelete
    Replies
    1. Dear Alan,
      (1) simply click on the tab 'Life-Tables' and change the Lx values and name of one of the tables you don't need. Next: select (from the drop down menu) this new table on the calc spreadsheet (Field: Life Table)

      (2) In principle not possible, you could try to split the calculation in two parts.
      One calculation as a one of lump sum ($250) and one normal contribution calculation....

      (3) You can select several default asset mixes schemes by means of the drop down menu 'Selection Scheme' on the calc spreadsheet.

      You can also develop your own Investment scheme and assign a name: Click on 'Investment Schemes Tab' and fill the name and values of a scheme you don't need. Next: select the name of this new scheme on the calc spreadsheet.

      Hope this helps...

      K.r. Jos Berkemeijer

      Delete
  2. Joshua,

    Thanks for the feedback. By bullet point:

    (1) I copied the table name into cell J9 on the Calc tab and noted the projected values changed.
    (2) A simple solution is to either incorporate a new variable representing a starting fund value, which goes into cell E22 on the Calc tab, or remove the write protection on cell E22 and let the user enter a value there. Without this, the calculator is at a disadvantage compared to others I have seen on the Internet.
    (3) I don't follow. Where is the Selection Scheme dropdown (tab name and cell address)?

    Regards,

    Alan

    ReplyDelete
  3. (1) Simply click on Calc tab J9 and a drop-down menu appears.
    The values of this drop-down menu will automatically change if you change the NAME in any of the fields G9-P9 of the Life-Tables tab. Same goes for changing any of the Lx values in the field range G10-P130 of the Life-Tables tab

    (2) You're right. Next release.....

    (3) Click on the field to the right from 'Investment Scheme (nr. 1)' (that's '100-age';F10) and select another investment scheme.

    You can also define your own schemes (just like adding new life tables) under the Investment-Schemes tab. Name Fields B4-K4, Scheme Values: D7-K127.

    General remark: ALL ORANGE FIELDS CAN BE ALTERED BY THE USER

    K.R., Joshua

    ReplyDelete
  4. Updated version 2012.2, including a 'single premium' option at the start of a plan!!

    ReplyDelete
  5. Josh,

    Kudos to the author for adding the single premium option. It gives users more flexibility if they have already accumulated large sums of money towards their retirement.

    With regard to the other two points, I still don't see the dropdowns, so I use a cut-and-paste approach for selecting mortality and investment strategies. It should be noted that I use the Office 2007 edition of Excel. Could this explain why the dropdowns aren't there?

    Regards,

    Alan

    ReplyDelete
    Replies
    1. Yes Alan, that's an Excel 2010 - 2007 Issue:

      http://www.ozgrid.com/forum/showthread.php?t=150080

      I'll see if I can fix this......

      Delete
  6. Alan, can you - or anybody else - check if all drop down menus actually run under Excel-2007. If there are any other Excel-2007 issues, I'll be glad to here...

    Thanks in advance.

    ReplyDelete
  7. Just came back today to see if this issue was resolved. I downloaded the latest model and discovered the dropdowns are working!

    Alan

    ReplyDelete
  8. I think this is among the most significant info for me.
    And i'm glad reading your article. But should remark on few general things, The web site style is ideal, the articles is really nice : D. Good job, cheers
    my site :: last minute weekendje weg nederland

    ReplyDelete
  9. Hi Joshua, is there a way this can be used to estimate present day valuation of a pension? Not that this was your purpose at all, but just wondering. Or wondering if you had or knew of anything similar.

    Thanks, Bruce

    ReplyDelete
    Replies
    1. Hi Bruce,
      calculate the single premium in case of your actual (accrued) pension benefits.

      Kind regards,

      Joshua

      Delete
  10. This is my first time visit at here and i am really pleassant to read all at
    alone place.

    Look at my website: Amazon

    ReplyDelete
  11. Thanks for your sharing of this excel pension calculator.This works well in my case except that I have to do seperate calculation for some long term goals like my kids higher education and kids marriage expenses. Otherwise, it gives me a good idea of if my corpus is enough to retire now. Once again, thank you for sharing this in an open forum for free. By the way,when i use my iPhone scan the qr code on the right of this webpage,it show network error, touch screen reload,why?I wonder the information of the code.Can someone tell me?

    ReplyDelete