Most NPS spreadsheets online still assume the old rule: 60% out as a lump sum, 40% into an annuity. PFRDA changed that for non-government subscribers in December 2025, and the tax rule did not change with it. This free Excel sheet works out your corpus at 60, the lump sum you are allowed, which part of it is tax-free, and the pension.
⬇ Download: NPS calculator in Excel (.xlsx, free)What's in the sheet
- Inputs: your age, monthly contribution, expected return, the share you want in the annuity, the annuity rate and your tax slab.
- Corpus at 60, and the legal minimum annuity share for that corpus.
- The lump sum, split into the tax-free part and the part taxed at your slab, with that tax.
- The monthly pension from the annuity.
The rules the sheet follows
- Corpus up to ₹8 lakh: the whole amount can come out as a lump sum.
- ₹8 lakh to ₹12 lakh: the lump sum is capped at ₹6 lakh; the rest goes to systematic withdrawals or an annuity.
- Above ₹12 lakh: up to 80% as a lump sum, at least 20% into an annuity.
- Tax: only 60% of the corpus is tax-free as a lump sum. Anything you take above that is taxed at your slab, so a 40% annuity still keeps the whole lump sum tax-free.
The formula, with an example
Corpus = FV(return/12, months, −monthly contribution): contributions at the end of each month until 60. ₹5,000 a month from age 30 at an assumed 10% comes to about ₹1.13 crore, from ₹18 lakh paid in. With 40% in an annuity at 6%, the pension is about ₹22,600 a month, and the ₹67.8 lakh lump sum is all tax-free. Take 80% instead and about ₹22.6 lakh of the lump sum becomes taxable.
Prefer it online? NPS calculatorRead: NPS vs PPFFrequently asked questions
- How do I calculate the NPS corpus in Excel?
- =FV(expected return/12, months to 60, -monthly contribution). For ₹5,000 a month for 30 years at 10%, =FV(10%/12,360,-5000) gives about ₹1.13 crore.
- How much of the NPS corpus can I withdraw at 60 now?
- For non-government subscribers under PFRDA's 2025 exit rules: all of it if the corpus is up to ₹8 lakh, a lump sum of up to ₹6 lakh between ₹8 and ₹12 lakh, and up to 80% above ₹12 lakh.
- Is the NPS lump sum tax-free?
- Up to 60% of the corpus. If you take more than that as a lump sum, the extra is taxed at your slab. The monthly pension is taxed as income.
- What return should I assume for NPS?
- It depends on your equity/debt mix and is not guaranteed. Try a few rates in the sheet, such as 8%, 10% and 12%, to see the range.