Spreadsheet Practice

How long will my savings last in Excel

Formula (Excel and Google Sheets)

=NPER(B4/12,B3,-B2)/12

Use NPER with the withdrawal as a payment you receive: =NPER(B4/12,B3,-B2)/12 gives the number of years.

Result on the example
25.1 in B5
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B5. Change any input and the result updates, or edit the formula to experiment.

How it works

This is a loan in reverse: the savings are money you have put in (negative) and the withdrawals are money coming back to you (positive). NPER counts the months until the balance reaches zero, about 301, and dividing by 12 gives 25.1 years. The balance keeps earning 5% while you draw it down.

=NPER(B4/12,B3,-B2)/12

Result25.10742165

  1. B45.0%

    The value in B4.

  2. B4/120.004166666667

    The / operator divides.

  3. B3$3,500

    The value in B3.

  4. B2$600,000

    The value in B2.

  5. -B2-600000

    Makes the value negative.

  6. NPER(B4/12,B3,-B2)301.2890598

    NPER: Number of periods to pay off or reach a target.

    • rateB4/120.004166666667
    • pmtB3$3,500
    • pv-B2-600000
  7. NPER(B4/12,B3,-B2)/1225.10742165

    The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =NPER(B4/12,B3,-B2)/12. We ran it in Google Sheets mode on the same data and it returns 25.1.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=NPER(B4/12,B3,-B2)301.3Different resultThe number of months. About 301 months.
=B2/B3/1214.3Different resultWithout any return, the savings simply divided by the withdrawals. Only 14.3 years without growth.

Common mistakes

  • If the withdrawal is no more than the monthly return ($2,500 here), the money never runs out and NPER returns #NUM!.
  • Inflation raises withdrawals over time. Use a real return (return minus inflation) for a more cautious answer.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

How long will my savings last

In B5, calculate how many years the savings in B2 last if you withdraw B3 a month while the rest earns the return in B4.

Questions

How much can I withdraw to make it last 30 years?

Use PMT: =PMT(B4/12,30*12,-B2).

How do I allow for inflation?

Use the real return: =NPER((B4-0.025)/12,B3,-B2)/12 with 2.5% inflation.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.