Download Excel Based Retirement Calculator

by Guest Blogger on June 21, 2011

in Retirement Planning

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.

Views: 65456


Get free daily updates in your email:






{ 42 comments… read them below or add one }

Hemant June 21, 2011 at 9:05 am

Hi Pattu/Manshu,

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

Reply

Subodh June 21, 2011 at 10:50 am

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!

Reply

pattu June 21, 2011 at 11:07 am

Thanks for cross checking Subodh. Gives me confidence.

Reply

Hema June 21, 2011 at 11:01 am

thats amazing..thanks for all effort.

Reply

Himanshu Bansal June 21, 2011 at 10:07 pm

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

Reply

Manshu June 22, 2011 at 2:21 am

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

Reply

P.N.Jani June 22, 2011 at 7:38 am

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.

Reply

Prajith June 30, 2011 at 6:09 pm

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

Reply

pattu June 30, 2011 at 10:30 pm

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

Reply

Prajith July 1, 2011 at 6:28 am

Pattu, thank you. Got it now.
–Prajith

Reply

Sridharan July 4, 2011 at 5:20 pm

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.

Reply

pattu July 5, 2011 at 10:09 pm

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

Reply

Jaswinder Singh May 15, 2013 at 2:17 pm

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

Thanks
Jaswinder Singh

Reply

pattu May 15, 2013 at 7:49 pm

Dear Jaswinder,

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

Reply

Sridharan July 7, 2011 at 12:59 pm

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………

Reply

lakshmipathy June 19, 2012 at 7:25 pm

Excellent Post

Reply

Rudresh August 28, 2012 at 5:53 pm

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

Reply

pattu August 31, 2012 at 5:24 pm

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.

Reply

santhosh iyer n September 8, 2012 at 1:02 pm

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

Reply

pattu September 8, 2012 at 8:59 pm

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

Reply

Raju September 11, 2012 at 1:01 pm

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

Reply

pattu September 11, 2012 at 4:57 pm

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

Reply

Chaitanya October 27, 2012 at 7:50 am

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!

Reply

pattu October 27, 2012 at 6:39 pm

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

Reply

Sundararajan December 22, 2012 at 12:42 am

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.

Reply

pattu December 22, 2012 at 5:26 am

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

Reply

Sundararajan December 26, 2012 at 7:50 pm

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.

Reply

pattu December 26, 2012 at 7:54 pm

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.

Reply

pension plan definition February 3, 2013 at 10:28 pm

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.

Reply

Santhosh March 3, 2013 at 10:37 am

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 :)

Reply

pattu March 7, 2013 at 5:26 pm

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 %

Reply

Santhosh March 3, 2013 at 1:18 pm

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.

Reply

pattu March 7, 2013 at 5:28 pm

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.

Reply

Anand K April 27, 2013 at 5:38 pm

Great excel too. Grateful to u to have shared. Tweaked the parameters to estimate my Term Plan insurance amount also. :-)

Reply

pattu April 27, 2013 at 8:38 pm

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

Reply

Manshu April 27, 2013 at 9:05 pm

Yeah, if possible, I’d like to see it too. Thank you.

Reply

Suraj September 3, 2013 at 3:52 pm

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 !

Reply

pattu September 3, 2013 at 9:24 pm

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

Reply

J S Jolly October 16, 2013 at 4:34 pm

Dear Pattu

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

Reply

pattu October 17, 2013 at 9:03 am

Thanks. You could also check out my other retirement calculators and investing calculators in my blog (freefincal.com)

Reply

JAYDEV BISWAS July 12, 2014 at 2:50 pm

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

Reply

JAYDEV BISWAS July 12, 2014 at 2:52 pm

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

Reply

Leave a Comment

{ 2 trackbacks }

Previous post:

Next post: