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. 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 🙂

    1. 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 %

  2. 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.

    1. 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.

  3. 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.

    1. 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

  4. 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!

  5. 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

    1. 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

  6. 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

    1. 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.

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

  8. 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.

    1. 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

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

      Thanks
      Jaswinder Singh

      1. Dear Jaswinder,

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

  9. 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

    1. 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

  10. 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.

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

  12. 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!

  13. Hi Pattu/Manshu,

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

Leave a Reply

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