How do I calculate how much to save each month for retirement?

The calculation has two steps. First, find the balance you need on the day you retire. Second, find the monthly saving that grows your current balance to that figure by then. This is arithmetic on stated assumptions, not advice on which accounts or savings rates to use, and the answer moves a lot when the assumptions change.

How do I calculate the target balance for retirement?

The target is the present value, at retirement, of the withdrawals you expect to take. Spending is usually stated in constant dollars, so inflate the first withdrawal to the retirement year, then discount the growing stream at the expected return:

target = W1 × [1 − ((1 + g) / (1 + r))^n] / (r − g)

• W1 is the first withdrawal in dollars at retirement, which is spending × (1 + g)^years to retirement
• g is the annual inflation rate
• r is the annual return as an effective annual rate
• n is the number of years in retirement

This is the present value of a growing annuity, with withdrawals at the end of each year. The formula needs r to be larger than g.

Savings compounded monthly at a nominal 6% grow at an effective annual rate of (1 + 0.06 / 12)^12 − 1, which is 6.17%. Use the effective rate in the target, so that both steps describe the same growth.

How do I use PMT to find the monthly saving?

PMT returns the regular payment that takes a balance from its present value to a future value at a fixed rate:

=-PMT(rate/12, months, -balance, target)

PMT returns a negative number, because the payment is money going out. The minus sign in front makes it positive. Without it, the example below shows −1,424.81.

What does a worked retirement saving example look like?

The assumptions are:

• Current balance: $40,000
• Years to retirement: 30, which is 360 months
• Spending: $50,000 a year in constant dollars, paid at the end of each year of retirement
• Years in retirement: 25
• Inflation: 2.5% a year
• Return: 6.0% a year, compounded monthly on savings, before fees and taxes

Over 360 months, the current balance plus the payments reaches the target. Of the $1.67 million target, $552,932.91 is the current balance plus 360 payments, and $1,119,216.84 is growth.

Sensitivity: at a 5% return the monthly saving rises to $2,036.91, and at 7% it falls to $963.12. The other assumptions stay the same.

What does this retirement saving method leave out?

• Taxes on savings and withdrawals, and fees.
• Other income. Pensions and Social Security reduce what the savings must cover, so subtract them from spending before Step 1.
• Length of retirement. A longer retirement raises the target.
• Timing of returns. A poor run of returns early in retirement can do more damage than the average return suggests.

How do I set up the retirement calculation in a spreadsheet?

Put the assumptions in column B, rows 1 to 6, and the calculations below them:

To test the sensitivity, change B5 to 5% or 7%. B9, B10 and B11 update. Spending in B1 is easiest to set from a monthly household budget. For a year-by-year version of this calculation, see the retirement savings projection.