An SWP calculator tells you a final balance. This free Excel sheet shows every month on the way there: what the fund earned, what you took out, and what is left, so you can see the month the money would run out if the plan is too ambitious.
⬇ Download: SWP calculator in Excel (.xlsx, free)What's in the sheet
- Your inputs: money invested, monthly withdrawal, a yearly raise in the withdrawal (to keep up with prices), expected return, years and inflation.
- A month table for up to 40 years: opening balance, the month's growth, the withdrawal, and the closing balance.
- How many months get the full withdrawal, the total withdrawn, and what is left at the end.
- The steady monthly withdrawal that lasts exactly your chosen term, so you can see how far your plan is above or below it.
- A tax tab for equity funds: the gain inside each year's withdrawals and the capital-gains tax on it.
How the month table works
Each month the balance first grows by a month's return (the yearly rate ÷ 12), then the withdrawal comes out. If the balance is smaller than the withdrawal, the last withdrawal takes what is left and the plan ends. That is the same order our online SWP calculator uses, so the two agree.
An example from the sheet: ₹25 lakh, ₹15,000 a month, an assumed 10% a year, for 20 years. Every one of the 240 withdrawals is paid in full, ₹36 lakh comes out in total, and about ₹69.3 lakh is still left at the end, because ₹15,000 a month is less than the fund earns. The steady withdrawal that would use the money up in exactly 20 years is about ₹24,126 a month.
Now push it: start at ₹30,000 a month, raise it by 5% every year, and assume 8%. The sheet shows the money running out after 96 months, about 8 years, with about ₹34.7 lakh withdrawn. A yearly raise feels small and is the fastest way to empty an SWP.
Tax: only the gain inside each withdrawal
Each withdrawal sells some units. Part of the money is your own cost coming back and part is gain, and only the gain is taxed. The sheet works out the gain with the average cost of your units; the tax department uses first-in-first-out, which can make a small difference.
For an equity fund, units sold within 12 months are short-term: 20%, plus 4% cess. After 12 months they are long-term: 12.5%, plus cess, only on gains above ₹1.25 lakh in the financial year. In the ₹25 lakh example, the first year's withdrawals carry about ₹9,382 of gain, so about ₹1,952 of tax; from year two the yearly gain stays under ₹1.25 lakh and the tax is nil. Debt-fund gains are taxed at your slab instead.
Returns of 8% or 10% are assumptions, not promises. A fund can fall in the years you are withdrawing, and selling units after a fall uses up more of them. Change the return cell and watch how many months the plan lasts; that is the number to plan around.
Prefer it online? SWP calculatorSWP or FD for monthly income?Frequently asked questions
- What is the SWP formula in Excel?
- For a steady withdrawal that uses the money up in a given time: =PMT(annual rate/1200, years*12, -amount, 0, 0). To see month by month, build a table where each month's closing balance = opening balance × (1 + rate/1200) − withdrawal. The free sheet has both.
- How long will my SWP last?
- Enter your amount, withdrawal and expected return in the sheet; the 'months with a full withdrawal' cell shows it. If it is less than years × 12, the plan runs out early.
- Is SWP withdrawal taxable?
- Only the gain inside each withdrawal is taxed, not the whole amount. For equity funds: 20% plus cess on units held 12 months or less, 12.5% plus cess on long-term gains above ₹1.25 lakh in a year.