I developed a spreadsheet that shows you how much money you’ll need as a lump sum to retire. It works by using the present value of an annuity calculation and includes 4 input variables: 1. income needed, 2. inflation rate, 3. investment return, and 4. number of years in retirement.

retirement spreadsheet

Step #1

Figure out how much you’ll need in your first year of retirement (the calculator factors inflation so you only need to come up with year one income). Let’s say you want or need $75,000 in pretax annual income in retirement. Great, figure how much earned income (wages) you’ll be generating, if any. Let’s say that amount is $20,000 in retirement. Next, calculate the amount you’ll receive for Pension/Social Security income. Let’s say that amount is $25,000. Now subtract out the wage income and pension/SS income from the annual income needed.

income needed

The Income Needed from Investments above is the amount you’ll input into the spreadsheet.

Step #2

Input the expected rate of inflation. Now I have 4% in the above example just to be safe. The current rate of inflation is nowhere near that. You may put 2% – 5% in this field depending on your views on future inflation.

Step #3

Input the rate you expect your money to return. You generally won’t make much more than a few percent above the risk free rate plus the rate of inflation. The risk free rate might be around 2.5% and the inflation rate runs around 2.5% so I wouldn’t expect much more than 8% on your money (2.5 + 2.5+ 3%). You could increase this number by taking on more risk.

Step #4

The last step is to input how many years you plan to spend in retirement. This step is a bit morbid because it’s basically the difference between the age in which you retire and your death. When you input this final number, you’ll see how much you’ll need as a lump sum in order to live off your income given the assumptions. There are some columns to the right that show you the drawdown of capital – notice your annual income increases with inflation so that you keep your purchasing power.

Drawdown of Capital 

There’s a piece of information that’s very important when deciding how much you’ll need to retire. That questions is not of little significance. It’s the fact that you may or may not want to leave money to you heirs. That will have a huge impact on how much you’ll need and the type of investments in your portfolio.

1) Draw down to zero – This method will let you retire earlier because you will be tapping into your principle, instead of just relying on interest. The retirement spreadsheet above will draw down capital if needed meaning that if you start at

2) Keep capital intact – You may want to leave some money to your kids or grandkids. This spreadsheet can still work for you. You’ll just need to add the amount you’d like to leave to your heirs in the green cell. If the spreadsheet says you’ll need $370,000 to retire, simply add the amount you’d like to leave to your heirs and this is your magic number.