How much can a nest egg pay out each month for a set number of years? PMT turns a present lump sum into a level withdrawal that runs the balance to zero.
The example
$500,000 paying out over 25 years at 4%.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Lump sum | $500,000 |
| 3 | Monthly payout | $2,639 |
The formula
The formula:
How it works
How it works:
- The savings are the present value; enter it negative so the payout is positive.
PMT(rate/12, years*12, -lumpSum)returns the level monthly payout that draws the balance to zero over the term.- A higher return or shorter term means a larger payout; the remaining balance keeps earning between withdrawals.
- To leave a balance at the end, add it as the future value argument.
Run it forever? For a payout that never depletes the principal (a perpetuity), the sustainable withdrawal is just balance × rate — see the perpetuity recipe. PMT is for a fixed term that ends at zero.
Try it: interactive demo
Lump sum, years, return.
Variations
Leave a balance
Future value:
How long will it last?
Solve NPER:
Perpetuity payout
Never depletes:
Pitfalls & errors
Sign convention. Lump sum negative so the payout is positive.
Return assumption. The payout depends on a steady rate — market swings change the real outcome.
Inflation. A level payout loses purchasing power over a long term.
Practice workbook
Frequently asked questions
How much can my savings pay out each month in Excel?
How do I leave money at the end?
How long will my savings last at a fixed withdrawal?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class