Download Excel Based Retirement Calculator

Reader Pattu  has developed a retirement calculator for his own use, and was interested in sharing it with other OneMint readers as well. I took a look at it, and liked what he’s done, so I’m sharing the calculator here.

Download Retirement Planner

He has also described his thought process, which I’m sharing below.

Retirement planning is a complicated financial goal. When you save for a new car, exotic holiday or even for the education of your children, all or most of the accumulated corpus would get spent when the time for the goal arrives. So it is easier to calculate the corpus required for such goals.

When it comes to retirement planning the corpus calculation is complicated because the corpus does not get spent in one shot. Typically it is allowed to grow at some post-retirement interest rate (usually underestimated for safety) and monthly withdrawals are made from it. For complete financial independence during retirement, these withdrawals or pension must increase according to the post-retirement inflation rate.

So the retirement corpus calculation has to take into account not only the years to retirement, present inflation rate but also post-retirement inflation, number of years in retirement and post-retirement interest rate on the corpus.

Given these input parameters, retirement planning consists of two major steps.

 

1. Calculation of the corpus required:

(a) Compute projected future monthly expenses at the start of retirement. This amount can be taken as the initial monthly pension which will be withdrawn from the corpus.

(b) This monthly pension amount is assumed to increase every year according to the rate of inflation, while the corpus is assumed to grow at some constant interest rate. Using these inputs the corpus is calculated using Excel’s present value calculator (PV).  The inputs to the PV function, the formulae used are a bit technical and can be found here.

(The site also has a nice annuity calculator which will be of interest to people close to retirement).

Since the withdrawals are indexed to inflation, the corpus will decrease each year and become zero at the end of the retirement period given as input. A typical rise and fall of the corpus is shown here.

Retirement Corpus
Retirement Corpus


2. Calculation of the monthly investment amount required:

Once the corpus needed is known this calculation is similar to any other financial goal. It uses the number of years to retirement, estimates of present inflation and annual interest rate, annual increase in investment if any, and amount accumulated so far if any.

The excel based calculator does the above tasks and present an annual cash flow chart of the monthly investment made, monthly expenses, the growth of the retirement corpus prior to retirement and its decrease post retirement. Annual salary is also shown for reference.

Please note:

1. The calculator has been repeatedly tested and checked. However no guarantee is made that it is free of errors. Like every other tool it serves only as an estimate for the monthly investment required and depends on the input parameters. It is meant to be used for education purposes only.

2. For simplicity the pre- and post-retirement inflation rates are taken to be the same. Use a reasonably high value to be safe. A financial expert suggested using 8-9%

3. The pre-retirement interest rate is taken as constant. Since equity allocation would decrease as retirement approaches this would change. The calculator can easily be modified to take into account variable interest rate. Contact me if you need to look at such a calculation.

Download Retirement Planner

Bugs, comments and suggestions are welcome.

Pattu

pattu@iitm.ac.in

Update: Fixed a divide by zero error bug.

54 thoughts on “Download Excel Based Retirement Calculator”

  1. Very impressive work. I am grateful.
    Any possibility to two additional incomes that are differed in time.
    Per example:
    1- A pension that would start at 70; and
    2- Another fixed income that would be come at another age.
    Making them subject each to an individual inflation rate would be fantastic.
    Thanks in advance for considering, whether or not you do it.

  2. Self tanning products can be used at home, or one can take professional help by going to a salon. Now apply your
    basic lotion again, to the areas mentioned
    before. It is preferable to use a moisturizer that is meant for a sunless tanned skin.

  3. Dear Pattu

    I was trying to locate such an excel calculator for quite some time. Excellent. You have made my pension calculations, very easy.

  4. Hi Pattu,

    Shouldn’t your corpus get exhausted within “Estimated years of retirement” ?

    I did some runs for 80 year life span and saw some corpus left so extended the life span and saw the same corpus sustaining me till 100 !

    1. Hi Suraj,

      Yes it should. Can you send me the file so that I can have a look? If you get some time, check my other retirement calculators and see how they fare.

      Thanks
      Pattu

  5. Great excel too. Grateful to u to have shared. Tweaked the parameters to estimate my Term Plan insurance amount also. 🙂

    1. Hi Anand,

      That is interesting. Could I have a look at what you did?
      Btw I have more retirement, insurance and other calculators at
      freefincal.wordpress.com

  6. i am quite opposed to equities being compounded annually.It isnt simple as that.It undermines the total compounding effect.debt too cant be compounded annually.Anyway it always gives a measure of how your money grows.

    1. Understand your point. The alternative is the problem. One can either use Monte Carlo calculators (I have one in my site which you can try) or to use a return lower than long term historical sensex returns. Nothing is perfect though.

Leave a Reply to Sridharan Cancel reply

Your email address will not be published. Required fields are marked *