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.
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.
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.
Bugs, comments and suggestions are welcome.
Pattu
Update: Fixed a divide by zero error bug.
Rd scheem ROI details of every month
Dear Sir,
Nice work on pension calculator.
Nice Article….will share
Just downloaded the looks really good…will enter the details and test it.
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.
Allen Maloney, these options have been incorporated in different forms in my website.
Excellent post. I certainly appreciate this site.
Stick with it!
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.
100.00 to 100000.00 recaring deposite rate of interist 1 month to 10 year . in pdf file
100 to 100000 rate of intarest monthely calculation in 1 month to 10 year . pdf file
Dear Pattu
I was trying to locate such an excel calculator for quite some time. Excellent. You have made my pension calculations, very easy.
Thanks. You could also check out my other retirement calculators and investing calculators in my blog (freefincal.com)
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 !
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
Great excel too. Grateful to u to have shared. Tweaked the parameters to estimate my Term Plan insurance amount also. 🙂
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
Yeah, if possible, I’d like to see it too. Thank you.
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.
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.