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.