*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

pattu@iitm.ac.in

*Update: Fixed a divide by zero error bug. *

**Views:**61040

{ 42 comments… read them below or add one }

Hi Pattu/Manshu,

Nice Calculator – I am linking this post in my latest article “When you are not ready for your retirement?”

must appreciate the efforts that he has put in developing this calculator. Out of curiosity, I randomly cross checked some of the formulae. Its awesome!

Thanks for cross checking Subodh. Gives me confidence.

thats amazing..thanks for all effort.

I will check calculator at home (on laptop rather than phone). Nice job.

Thank you Pattu for allowing me to share your calculator on OneMint, and all the other commenters for their observations! Thanks!

Mind blowing work, Tested for one day considering various perameters and it is giving error free reports.

Hats off for your work.

Thanks for the post and thanks for spending tinme to prepare such type of the complicated software and after that made it available FREE for Public.

Thanks and Regards.

Hi Pattu,

Hats off to you. Btw, I have a small query. You have mentioned that the retirement corpus left over will be zero at the end. What was confusing for me was if the second last year the value is 36582087 (referring the attachment for ease) and the yearly expense of the next year should be same right?

ie The figure coming as 1093582 should have been 36582087 /12=3048507 (dividing to make it as post monthly) right? As of now according to the sheet there will be still still 23459103 (36582087 – (1093582 *12) left out.

Thank you….. great effort

–Prajith

Dear Prajith,

Thank you for closely checking the calculator.

1093582 is the post-tax annuity. The pre-tax annuity is higher by the post-retirement tax slab you enter.

To see how the corpus goes to zero, set the post-retirement tax slab to zero.

I checked this now and it works fine.

Thanks again,

Pattu

Pattu, thank you. Got it now.

–Prajith

the table seems quite lopsided because -

1. It fails to take congnizance of receivables post retirement (like rentals pension etc), and therefore, shows geometrical progression of amount to be saved per month

2. Also, as you age, there are many expenses which will not be there to take care of. for instance School/college fees, reduced travelling, reduced expenses on food and clothing etc.

3. Also, the chart assumes that the person concerned will be staying on their own (whether on rented property or owned property is not clarified)

4. Whether fixed assets like Land, house, Flat are to be considered for arriving at “Amount invested so far” and at future value or present.

there are many imponderables which could not be addressed on a assured basis have been left out. The first look at the corpus required would be a severe shock to anyone who is not used to the above numbers and facts.

therefore, ther request is to make it more user friendly, in the sense, crucial factors are factored in. there is one available with me, which i can share which addresses many of the above in a simplistic way.

Dear Mr. Sridharan,

Since you wish to share your calculator with others do share your email on the blog so that requests could be sent to you. I am sure it will benefit others.

I made the calculator for myself and I shared it in the hope that people will build upon it and make something that suits their situation.

As far as I am concerned, you have provided an answer to the deficiencies in the calculator:

“many imponderables which could not be addressed on a assured

basis have been left out”

on closer inspection some of them can be factored in indirectly.

The calculator does not take into account lumpsum payments like gratuity etc. for the simple reason that I don’t wish to. I would like to treat it as an extra that would help me combat the unknown when I retire.

Some expenses will reduce upon retirement. Some will increase. Then there is the unknown. Inflation can be higher than estimated. Equity instruments may under perform, corpus may fall short etc.

Take any mutual find and find out the returns in the last 6 months. What if this number recurs for a couple of years? What would happen to the corpus?

To safe guard against this and other unknowns I would recommend not making an estimate with reduced expenses.

If you pays rent now it can be take as a present expense. If you purchase a house later on, you would have some free money to play with when you retire becuase you overestimated what you needed. That is not a bad thing surely.

Immovable assets should never be part of retirement calculation unless you are going to retire with a corpus earned by selling land close to retirement.

All retirement corpuses made with any calculator are shocking to the uninitiated. Although the factors you have indicated will increase or decrease the investment needed I do not consider them crucial for me.

Thank you for your detailed comments.

Pattu

Sridharan,

Can you please share the calculator which you have as you mentioned in your post above?

Thanks

Jaswinder Singh

Dear Jaswinder,

I developed a retirement calculator based solely on Sridharan’s inputs. You can get it by clicking on my name.

well, i overlooked the fact that it was prepared for your use and may not be applicable at all to me!

therefore, i would like to appreciate your effort in untangling and trying to address issues related to your retirement plan.

well, i found it not suitable for me. i am happy and contended with the one i have drawn myself and am quite sure will suffice. i do not want to get carried away by astronomical numbers like the ones our insurance companies keep throwing at gullible folk in a bid to entice them to take insurance plans!

thanks for taking time out and giving elaborate comments, which i am sure will help many others see the point………

Excellent Post

Dear Pattu,

Thanks a lot for posting this. Your work is very useful, for sure.

However, I have a question which might appear so basic.

What’s meant by “initial equity alloc. (rest is taken as debt)” and next two subsequent rows ?

Thanks in advance.

Rudresh

Dear Rudresh,

Thank you. Initial equity allocation refers to % of investment in shares or mutual funds. This % will remain high when you are young and decrease as you grow older. This version of the calculator only takes into account the initial allocation.

The subsequent rows refer to rate of interest you are likely to earn from equity instruments and debt (ie. from bonds, PPF, FDs etc.) instruments.

Updated and more comprehensive calculators are found in my website.

thanks for the excel software. and your kindness for shearing to all

Thanks. You could download other calculators by clicking on my name.

Good effort, Pattu. Thanks for sharing.

However, it would be great if some of the BIG expenses post retirement are included in the calculations viz child higher education and marriage with specific year of requirement for this biggies since they cannot be built in current monthly expenses.

Another thing, it would be better to arrive at the available corpus at retirement based on current income and savings rather than assuming that i will have required corpus at retirement. This will let the user know if he will have required funds at retirement based on his current income/saving level or what will be the surplus/deficit.

Regards

Raju

Dear Raju,

Thanks. Integrated goal planners with a feature to optimize the monthly investment required are available at the above link. FB id: freefincal

The feature to change the investment amount and recalculate the corpus is also available there. Do visit and let me know your feedback. You could also write to

pattu[AT]iitm.ac.in

PATTU, This is an AWESOME calculator, many financial blogs/planners give such tools only for “paid” clients that too by password-protecting :), its really wonderful to note that you have made it and other such tools too on your website available to all for free. God bless you and your family!

Thanks. Credit to Manshu for publishing this. This is how it all started.

Thanks to you for creating such a detailed calculator. But I have questions on how to use this.

Assuming a person is retiring now with some corpus, how to use this calculator to find out if that corpus is enough to retire? Please let me know. Thanks, Sundar.

There are two options available at:

freefincal.wordpress.com

You could use the ‘retirement calculator for the middle-aged employee’ and set the

‘years to retirement’ as zero.

This is the most suitable for someone retiring immediately

or as a quick check you could use the retirement corpus guesstimator

this will tell you how long your money will last

If you need further clarifications contact me at

pattu[AT]iitm.ac.in

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

Regards,

Sundar.

Thanks. The other goal planners are available in my website. If you send me an email

pattu [AT] iitm.ac.in

I could give you a version in which all the planners are integrated.

It’s really a nice and useful piece of info. I’m satisfied that you just shared this helpful information

with us. Please keep us up to date like this. Thank you for sharing.

The calculation of inflation for first month is wrong.It wrongly uses post retirement additional expense??? Shouldnt it be E5+E7*E5 ..freaks me out

No it is not wrong.

I have added the post retirement additional expenses to present expenses and inflated it to time of retirement. Nothing mathematically wrong about it. The idea is to freak you out. You could always set additional expenses to 0% or a negative %

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.

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.

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

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)

100 to 100000 rate of intarest monthely calculation in 1 month to 10 year . pdf file

100.00 to 100000.00 recaring deposite rate of interist 1 month to 10 year . in pdf file

{ 2 trackbacks }