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...
By Maggid on 3/30/2012 11:42:00 PM
Labels: , , , , , ,

16 comments:

  1. Carlos ArochaApril 2, 2012 at 9:49 AM

    Thanks for posting this!

    ReplyDelete
  2. ActuaryAWFApril 5, 2012 at 9:02 PM

    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. MaggidApril 5, 2012 at 9:27 PM

      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
  3. ActuaryAWFApril 6, 2012 at 7:50 PM

    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
  4. MaggidApril 8, 2012 at 5:18 PM

    (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
  5. MaggidApril 9, 2012 at 12:02 AM

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

    ReplyDelete
  6. ActuaryAWFApril 12, 2012 at 6:44 PM

    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. MaggidApril 19, 2012 at 6:16 PM

      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
  7. MaggidApril 20, 2012 at 10:15 AM

    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
  8. ActuaryAWFMay 31, 2012 at 11:47 PM

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

    Alan

    ReplyDelete
  9. AnonymousJune 15, 2012 at 7:32 PM

    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
  10. AnonymousJuly 28, 2012 at 8:04 PM

    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. MaggidAugust 6, 2012 at 12:40 AM

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

      Kind regards,

      Joshua

      Delete
  11. AnonymousApril 19, 2014 at 2:31 AM

    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
  12. UnknownOctober 16, 2014 at 5:40 AM

    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
  13. فروشگاه اینترنتیFebruary 11, 2018 at 10:03 AM

    i am really pleassant to read all at
    alone place.is 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.
    https://www.baneh.com
    thanks alot

    ReplyDelete

Subscribe to: Post Comments (Atom)