Free download · .xlsx · no macros
FD Premature Withdrawal Calculator — Free Excel Download
If you have forty deposits to work through, a web form is the wrong tool. This is a premature FD break calculator in Excel: three sheets, every formula visible, all 24 Indian banks pre-loaded, and a break-versus-loan comparison built in. Change the inputs in column B and the penalty, payout and loss recalculate.
fd-premature-withdrawal-calculator.xlsx
9 KB · 3 sheets · Excel 2016+ and LibreOffice · works offline · no email required
What is inside the workbook
Calculator sheet. Seven inputs — deposit amount, booked rate, bank, booking date, maturity date, closure date — and eleven calculated cells covering days held, the card rate for that period, the penalty, the penalised rate, interest paid, payout, interest lost against maturity, and the loan-versus-break answer.
Bank Penalties sheet. All 24 Indian banks and institutions with the penalty for deposits up to ₹5 lakh, the penalty above ₹5 lakh, the minimum holding period and a plain-English description of each bank's rule. SBI, HDFC, ICICI, Axis, Post Office, Bajaj Finance and Shriram Finance are all included.
Card Rates sheet. Every tenure bucket for every bank — about 240 rows — which is what makes the rate lookup work. If your bank changes a rate, update it here and every calculation follows.
The formula, written out
Two formulas cover almost every Indian bank. Both use the penalised rate — that is, the card rate for the period actually held, minus the penalty percentage.
Simple interest on early closure
=Principal*((CardRate-Penalty)/100)*(DaysHeld/365)This is what the bank actually pays you. Quarterly compounding stops the moment the deposit is closed, which is why the payout never matches a compounding calculation.
Quarterly compounding (for comparison)
=Principal*(1+(CardRate-Penalty)/400)^(DaysHeld/91.25)-PrincipalUse this only if you want to see what the same penalised rate would have produced with quarterly compounding. No Indian bank does this on an early closure.
Looking up the card rate for the period held
=LOOKUP(2,1/(('Card Rates'!$A$2:$A$400=$B$7)*('Card Rates'!$B$2:$B$400<=$B$13)),'Card Rates'!$C$2:$C$400)This finds the last row where the bank matches and the bucket is less than or equal to the days held — the highest qualifying tenure bucket. A plain VLOOKUP with approximate match would need the data sorted by a single key, which does not work when you have 24 banks in one table.
Penalty lookup
=IF(Principal>500000,
VLOOKUP(Bank,'Bank Penalties'!$A$2:$D$40,3,FALSE),
VLOOKUP(Bank,'Bank Penalties'!$A$2:$D$40,2,FALSE))Column 2 is the penalty up to ₹5 lakh, column 3 is the penalty above ₹5 lakh. Banks with a flat penalty — HDFC, Axis, most NBFCs — have the same value in both columns, so the IF changes nothing for them.
Worked example, cell by cell
₹10,00,000 at SBI, booked 1 January 2026 at 7.00% for one year, closed 1 September 2026. You can reproduce every figure below by entering the same inputs into the workbook.
| Cell | Label | Value |
|---|---|---|
| B5 | Deposit amount | ₹10,00,000 |
| B6 | Booked rate | 7.00% |
| B13 | Days held | 243 |
| B15 | Card rate for the period held | 6.85% |
| B16 | Penalty | 1.00% |
| B17 | Penalised rate applied | 5.85% |
| B18 | Interest paid | ₹38,947 |
| B20 | Interest if held to maturity | ₹71,859 |
| B21 | Interest lost | ₹32,912 |
A quarter of the loss comes from the penalty itself and three quarters from the tenure downgrade and the four months of interest you never earn. That split is the reason the template shows all three figures separately.
Why your Excel answer may differ from the bank’s
- Day-count convention. Indian banks use actual/365 for term deposits. A bank using actual/actual on a leap year will differ by a few rupees.
- Rate changed after booking. The template carries indicative card rates, not the historical table for the month you booked.
- Promotional or senior-citizen rate. If your booked rate included a special tenor premium, the card rate the bank downgrades to may not include it.
- Penalty threshold. SBI and ICICI double the penalty above ₹5 lakh. If your deposit was booked as a single ₹6 lakh FD, that applies.
- Rounding. Banks round interest to the nearest rupee at each stage; the template rounds only the final figure.
Frequently asked questions
For simple interest: =Principal*((CardRate-Penalty)/100)*(DaysHeld/365). For quarterly compounding: =Principal*(1+(CardRate-Penalty)/400)^(DaysHeld/91.25)-Principal. CardRate is the bank rate for the period actually held, not the rate you booked.
The template stores every bank and tenure bucket on a Card Rates sheet. The card rate cell uses LOOKUP(2,1/((bank range = selected bank)*(days range <= days held)), rate range), which returns the rate on the last matching row — the highest bucket that does not exceed the days held.
Yes. The file is a standard .xlsx with no macros, so it opens in Excel 2016 and later, LibreOffice Calc and Google Sheets. LOOKUP, VLOOKUP and ROUND are supported in all three. Google Sheets may reformat the date cells on import — check them before trusting the day count.
Four usual reasons: the bank used a different day-count convention, the card rate changed after your booking, the branch applied a promotional or senior-citizen rate, or the deposit crossed a penalty threshold such as ₹5 lakh. Compare the card rate the bank quotes on its closure advice with the value in cell B15.
For a Post Office Time Deposit, yes — but only for closures after 12 months, where the rule is the completed-period TD rate minus 2.00%. The template does not model the 6-to-12-month savings-rate treatment. National Savings Certificates have no ordinary early encashment, so there is nothing to calculate.
No. US penalties are charged in days of interest rather than as a rate cut. Use the online CD calculator instead: Penalty = (Balance × APY ÷ 365) × Penalty days, where the penalty days come from the CD term.
Bank lookup list
SBI (State Bank of India) · Punjab National Bank (PNB) · Bank of Baroda · Canara Bank · Union Bank of India · Bank of India · Central Bank of India · Indian Bank · HDFC Bank · ICICI Bank · Axis Bank · Kotak Mahindra Bank · IndusInd Bank · Yes Bank · IDFC First Bank · Federal Bank · RBL Bank · IDBI Bank · AU Small Finance Bank · Equitas Small Finance Bank · Jana Small Finance Bank · Post Office Time Deposit · Bajaj Finance FD · Shriram Finance FD
Penalty rates last verified October 2026. See the methodology for sources.
Related tools
Bank penalty rules last verified . Always confirm the current terms with your bank before closing a deposit.