Spreadsheets

EMI with part prepayment: calculating the new tenure in Excel

Find the new loan tenure after a part prepayment in Excel using FV and NPER, and compare it with keeping the tenure and lowering the EMI, with a worked example.

Editorial illustration for EMI with part prepayment: calculating the new tenure in Excel

After a part prepayment, you can either keep paying the same EMI and finish sooner, or keep the same end date and pay a smaller EMI. In Excel, NPER gives the first answer and PMT gives the second. Both start from the balance outstanding when you prepay, which FV can calculate from the loan details.

What do I need before the prepayment?

Set up inputs. B1 Loan amount, B2 Annual rate, B3 Original tenure in months, B4 EMI, B5 EMIs already paid, B7 Prepayment amount. The EMI is =PMT(B2/12,B3,-B1).

Say the loan is ₹25,00,000 at 8.5% for 240 months, and you prepay ₹2,00,000 after 24 EMIs. These are sample numbers. The EMI is about ₹21,696.

How do I find the balance outstanding?

In B6, write =FV(B2/12,B5,B4,-B1). FV grows the loan forward by 24 months and subtracts the payments made, so it returns the balance. Here it is about ₹23,96,091. Check it against your lender's statement; the two should be close.

Then in B8 write =B6-B7 for the balance after prepayment: 23,96,091 - 2,00,000 = ₹21,96,091.

How do I get the new tenure at the same EMI?

Use NPER, which returns the number of periods for a given rate, payment and present value:

=NPER(B2/12,-B4,B8)

The payment goes in negative and the balance positive. The answer is about 178.84 months, so you need 179 EMIs, with the last one slightly smaller. Wrap it in ROUNDUP(...,0) for a whole number. The original plan had 240 - 24 = 216 months left, so the prepayment cuts about 37 months, a little over three years.

How do I get the lower EMI at the same tenure?

Recalculate with PMT on the new balance and the months that remain:

=PMT(B2/12,B3-B5,-B8)

That is 216 months on ₹21,96,091, giving about ₹19,885, which is ₹1,811 less than the old EMI of ₹21,696.

Option after the prepaymentInstalmentMonths leftInterest still to pay
No prepayment₹21,696216about ₹22.9 lakh
Same EMI, shorter loan₹21,696about 179about ₹16.8 lakh
Lower EMI, same end dateabout ₹19,885216about ₹21.0 lakh

To fill the last column, use =B4*(B3-B5)-B6 for no prepayment, =B4*B9-B8 for the shorter loan where B9 holds the NPER result, and =B11*(B3-B5)-B8 for the lower EMI where B11 holds the new PMT. In this example the shorter loan saves about ₹6.1 lakh in interest against the original plan and the lower EMI saves about ₹1.9 lakh. Keeping the EMI is the choice that cuts interest most; lowering it eases the monthly burden. Which suits you depends on your cash flow, which only you know.

What if my floating rate changes later?

NPER and PMT assume a constant rate. If the lender revises the rate, change B2 and recalculate using the then-current balance as your new starting point. The sheet is a tool for this moment, not a forecast of the next twenty years.

What if the numbers don't match my lender?

Compare three things: the outstanding principal on the lender's statement, the EMI amount and the rate being applied. A mismatch in the balance usually comes from the way interest between instalment dates is treated, or from charges added to the loan. Ask for a statement of account.

A short routine

  1. Take the outstanding principal from the lender's latest statement and enter it directly in B6 if it differs from FV.
  2. Enter the prepayment and compare both options.
  3. Pay, then save the receipt and the revised schedule.
  4. Record the payment in your regular expenses. In SpendixAI it can be filed under its own category when you import the statement.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports