# How long will my savings last in Excel

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

**Excel:** `=NPER(B4/12,B3,-B2)/12` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Retirement savings | Value |
| Savings | 600000 |
| Monthly withdrawal | 3500 |
| Annual return | 0.05 |
| Years it lasts |  |

Formula in B5: `=NPER(B4/12,B3,-B2)/12`

Result: 25.1

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

## Other ways

- `=NPER(B4/12,B3,-B2)`: The number of months. (About 301 months.)
- `=B2/B3/12`: Without 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/how-long-will-savings-last