Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →To build an EMI calculator with prepayment in Excel, combine PMT for the regular installment with an amortization schedule that separately calculates interest, scheduled principal, extra payments, and the closing balance. The model below supports a one-time prepayment, recurring extra payments, tenure reduction, and interest-savings comparisons.
It is a planning model—not an official payoff quote. Your lender may use daily interest, different rounding, fees, floating rates, or a different rule for reducing EMI versus tenure.
As an Amazon Associate I earn from qualifying purchases.
What this Excel calculator shows
- Regular EMI
- Monthly interest and scheduled principal
- One-time and recurring prepayments
- Closing balance after each payment
- Total interest and total payments
- Interest saved compared with making no prepayment
- Estimated payoff period
It can be adapted for home, personal, auto, or education loans, provided the loan follows a periodic reducing-balance calculation.
How EMI is calculated
For a monthly reducing-balance loan:
EMI = P × r × (1+r)^n ÷ ((1+r)^n−1)
Pis the loan principal.ris the periodic interest rate, such as an annual nominal rate divided by 12.nis the total number of payments.
Excel’s equivalent is:
=-PMT(AnnualRate/PaymentsPerYear,TermYears*PaymentsPerYear,LoanAmount)The minus sign displays the payment as a positive number. Microsoft’s PMT documentation explains that the rate and number of periods must use the same units. PMT calculates principal and interest; it does not automatically include fees, taxes, insurance, or reserves.
#1 Best Overall
- Profitability calculations; cash flow function Calculates NPV and IRR for uneven cash flows
- Time-value-of-money and Amortization keys solve problems including: pension calculations, loans, mortgages, etc.
- Ideal calculator for students, managers and statisticians
- Built-in functionality : List-based one- and two-variable statistics with four regression options: linear, logarithmic, exponential and power
- The BA II Plus calculator is approved for use on the following professional exams: Chartered Financial Analyst exam. GARP Financial Risk Manager (FRM) exam. Certified Management Accountants exam
1. Create the input section
Put these labels in column A and the values or formulas in column B:
| Cell | Input | Example |
|---|---|---|
| A2/B2 | Loan Amount | 1,000,000 |
| A3/B3 | Annual Interest Rate | 8.5% |
| A4/B4 | Term in Years | 20 |
| A5/B5 | Payments per Year | 12 |
| A6/B6 | Regular EMI | Formula |
| A7/B7 | Recurring Extra Payment | 0 |
| A8/B8 | One-Time Prepayment | 100,000 |
| A9/B9 | One-Time Prepayment Period | 24 |
In B6, enter:
=-PMT(B3/B5,B4*B5,B2)For a monthly loan, B5 is 12. Do not use an annual rate and a monthly number of periods together without converting the rate. For example, =PMT(8.5%,240,1000000) is not the correct monthly-rate setup for an 8.5% nominal annual rate.
2. Build the amortization schedule
Start the table in row 14 with these headings:
| Column | Heading |
|---|---|
| A | Period |
| B | Opening Balance |
| C | Interest |
| D | Scheduled EMI |
| E | Scheduled Principal |
| F | Prepayment |
| G | Total Payment |
| H | Closing Balance |
| I | Cumulative Interest |
| J | Cumulative Prepayment |
Use row 15 as the opening state before the first payment:
A15: 0
B15: =$B$2
C15: 0
D15: 0
E15: 0
F15: 0
G15: 0
H15: =$B$2
I15: 0
J15: 0Enter these formulas in row 16 and copy them down. The formulas assume that the regular EMI is paid first and the prepayment is made afterward.
Period
=A15+1Opening balance
=H15Interest
=B16*($B$3/$B$5)This is a simple periodic reducing-balance calculation.
Scheduled EMI
=MIN($B$6,B16+C16)The MIN prevents the final scheduled payment from exceeding the balance plus that period’s interest.
Scheduled principal
=MIN(B16,MAX(0,D16-C16))Prepayment
=MIN($B$7+IF(A16=$B$9,$B$8,0),MAX(0,B16+C16-D16))This combines the recurring extra payment in B7 with the one-time amount in B8 when the period equals B9. The cap prevents overpayment.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Total payment
=D16+F16Closing balance
=MAX(0,B16+C16-D16-F16)Cumulative totals
I16: =I15+C16
J16: =J15+F16For a 20-year monthly loan, copy the formulas through at least 240 periods. You can apply conditional formatting to rows where the closing balance is zero, or add a status column:
=IF(B16=0,"Paid Off","Active")3. Add one-time and recurring prepayments
A prepayment is an extra amount applied to the outstanding balance. In this model:
Rank #2
- PROFESSIONAL FINANCIAL CALCULATOR : Built-in TVM, IRR, NPV. Engineered for business analysts, real estate investors, accountants, and finance students.
- ADVANCED CASH FLOW & AMORTIZATION : Execute time value of money, break-even analysis, depreciation schedules, and bond pricing. Trusted for professional exam prep", MBA coursework, and banking certifications.
- CATIGA CF-300 : Flip-open hard case with a snap-close design for a secure fit. Compact and portable: designed for daily professional use in office, classroom, or on-site.
- ALL-IN-ONE FOR PROFESSIONALS : From NPV/IRR for real estate analysis to statistical calculations for business analysts. Handles probability, linear regression, and complex financial formulas.
- MORTGAGE, LOAN & INVESTMENT CALCULATOR : Covers bond pricing, loan amortization, investment analysis, and exam-level computations. Your go-to accounting calculator, business calculator, and real estate calculator in one device.
Closing balance = Opening balance + Interest − Scheduled EMI − PrepaymentA one-time payment is controlled by:
IF(Period=OneTimePrepaymentPeriod,OneTimePrepayment,0)A recurring payment is simply added to the prepayment input. The formula above caps the total extra payment at the amount still due after the regular EMI.
Earlier prepayments generally save more interest than the same amount paid later because they reduce the balance used to calculate more future interest. That is a model result, not a guarantee of the lender’s exact saving.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →4. Choose what happens after prepayment
Tenure reduction: keep the EMI unchanged
The schedule above is a tenure-reduction model. The regular EMI remains unchanged, extra principal reduces the balance, and the loan ends earlier. This is often the simplest way to model maximum interest reduction for a given prepayment, but do not assume every lender applies prepayments this way.
Confirm whether your lender reduces the tenure, reduces the EMI, or changes both.
EMI reduction or recast: keep the maturity date
If the lender recalculates the EMI after a prepayment, the new installment is based on the balance after that prepayment and the remaining number of periods:
=-PMT(AnnualRate/PaymentsPerYear,RemainingPeriods,NewBalance)For example, if H16 is the balance after the current period and L16 contains the remaining periods:
K16: =-PMT($B$3/$B$5,L16,H16)The next row’s scheduled EMI can refer to the recalculated amount:
=MIN(K15,B16+C16)This approach requires consistent treatment of whether the prepayment occurs before or after the period’s EMI. Use one convention throughout the workbook.
Why the choice matters
| Option | Main benefit | Main drawback |
|---|---|---|
| Reduce tenure | Usually produces greater interest savings when the EMI stays unchanged | Does not provide as much monthly cash-flow relief |
| Reduce EMI | Lowers the required monthly payment | May save less interest if the maturity date remains unchanged |
| Hybrid treatment | May balance cash flow and faster payoff | Must be modeled using the lender’s actual recalculation rules |
5. Calculate payoff period and interest saved
Useful summary formulas include:
Total payments:
=SUM(G16:G500)
Total interest:
=SUM(C16:C500)
Total prepayments:
=SUM(F16:F500)
Payoff periods:
=COUNTIF(H16:H500,">0.01")Use a second schedule with prepayments set to zero as the baseline. Then calculate:
Rank #3
- HP 10BII+ FOR STUDENTS & PROFESSIONALS – This HP calculator is built for business, finance, accounting, and statistics courses. Perfect for learners and professionals who need to solve common financial problems quickly without memorizing formulas or relying on spreadsheets.
- 100+ FUNCTIONS FOR REAL WORLD MATH – Quickly solve time value of money, interest rates, loan payments, NPV, IRR, cash flows, and more. The 10bII+ also includes probability distributions for statistics courses—a feature not often found in financial calculators.
- ALGORITHMIC INPUT WITH DEDICATED KEYS – This high-school/college calculator uses algebraic and chain logic with minimal keystrokes. Layout appears the same as standard calculators for easy learning. Dedicated keys give quick access to commonly used financial and statistical functions
- APPROVED FOR MAJOR EXAMS – The HP 10bII+ algebra calculator is permitted for use on SAT, PSAT/NMSQT, and AP tests. An ideal statistics calculator and business calculator for school finance and accounting students preparing for class, coursework, or standardized exams.
- INCLUDES TRAVEL CASE, CLEANING CLOTH & BATTERIES– Slim, durable, and easy to keep on hand or store in a backpack or locker. Includes a protective case, cleaning cloth, and batteries so it’s ready out of the box. Large screen with clear contrast (non-backlit) is easy to read during exams or lectures.
Interest saved = Baseline total interest − Prepayment total interestComparing only the old and new EMI is not enough. Total interest and the number of payments show the financial effect more clearly.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitches6. Create a no-prepayment comparison
The easiest method is to duplicate the amortization sheet and set the recurring and one-time prepayment inputs to zero. Keep the same loan amount, rate, term, payment frequency, timing, and rounding rules.
With no prepayment, the balance should reach approximately zero at the original term. If the baseline schedule does not amortize correctly, fix it before interpreting any savings.
Using IPMT and PPMT instead
Excel can calculate the scheduled interest and principal portions with:
=-IPMT($B$3/$B$5,A16,$B$4*$B$5,$B$2)
=-PPMT($B$3/$B$5,A16,$B$4*$B$5,$B$2)Microsoft documents PPMT as calculating the principal portion of a payment, with per between 1 and nper. These functions describe the original fixed-payment loan; they do not automatically model an extra principal payment or a changed balance. For that reason, the direct balance-based formulas are usually easier to adapt.
Recommended Free Tools
Google Sheets also supports PMT, IPMT, and PPMT. Its documentation for PPMT and IPMT likewise requires consistent rate and period units.
Timing assumptions you must state
The formulas in this guide assume the payment sequence is:
- Interest accrues for the period.
- The scheduled EMI is applied.
- The extra payment is applied to the remaining balance.
Some loans apply a prepayment before the regular installment, while others calculate interest daily from actual payment dates. A mid-period payment can therefore produce a different result even when the stated rate and loan amount are identical.
Common errors and fixes
Negative EMI
Excel’s financial functions use cash-flow signs. Use =-PMT(...), or use a consistent alternative sign convention throughout the workbook.
Rank #4
- Solves time-value-of-money calculations such as annuities, mortgages, leases, savings, and more
- Performs cash-flow analysis for up to 32 uneven cash flows with up to 4-digit frequencies
- Calculates various financial functions: Net Future Value Net present Value Modified Internal Rate of Return Internal Rate of Return Modified Duration Payback Discounted Payback
- The Texas Instruments BAII Plus Professional features an Automatic Power Down (APD) function for extended battery life
- Prompted display guides you through financial calculations showing current variable and label. Ten-digit display
Wrong annual or monthly rate
For a nominal annual rate and monthly payments, use:
=-PMT(8.5%/12,240,1000000)Dividing by 12 is not automatically correct for every effective-rate convention. Match the lender’s stated rate methodology.
Negative closing balance or excessive final payment
Use MIN for the scheduled payment and prepayment, and MAX(0,...) for the closing balance. Do not round the underlying balance in every row; format it to two decimals instead.
Small difference caused by rounding
If the lender rounds interest each month, you can test a lender-rounding version:
Free tools Windows power users keep installed
One-click scans. No signup required.
=ROUND(B16*($B$3/$B$5),2)Rounding interest, principal, or balances at different stages can create a small final difference.
Prepayment appears in the wrong period
Check whether the period number starts at 0 or 1, whether B9 contains the intended payment number, and whether the lender applies the prepayment before or after the regular EMI.
#NUM! or invalid period errors
Check that the rate, term, and payment frequency are valid; that the payment period is at least 1; and that IPMT or PPMT does not receive a period greater than the loan’s original number of periods.
Floating-rate and irregular loans
A single fixed-rate PMT formula is not sufficient when the interest rate changes. Add an annual-rate column to the schedule and calculate each row’s interest using that period’s rate, for example:
=B16*(C16/$B$5)Here, column C would need to contain the applicable annual rate rather than the interest result, so use a separate rate column in the actual workbook to avoid conflicting headings.
Best Value
- Brand New in box; The product ships with all relevant accessories
- Dedicated keys allow easy access to common financial and statistics functions
- Easy-to-use design provides business, finance and statistical calculations fast
- Specially designed to meet the mathematical needs
A date-based schedule may be needed for fortnightly payments, arbitrary payment dates, daily interest, deferred first installments, grace periods, interest-only periods, or balloon payments. Also model fees separately. The basic schedule does not include prepayment charges, processing fees, insurance, taxes, late fees, escrow, or reserves.
Payment allocation can also differ. A lender may apply money to fees or accrued interest before principal, so an extra payment may not reduce principal immediately by the amount entered.
Validation checklist
- With prepayments set to zero, the balance reaches approximately zero after the original term.
- Interest is generally highest at the beginning of a standard amortizing loan and declines as principal falls.
- Adding a prepayment does not increase total interest in the tenure-reduction model when no fee is included.
- The same prepayment made earlier generally saves more interest than when made later.
- The final payment does not exceed the remaining balance plus interest.
- Total principal repaid equals the original loan amount, subject to rounding.
- Total payment equals scheduled payments plus prepayments and separately modeled fees.
- The result is compared with at least one lender statement or trusted loan calculator.
Excel and browser-based alternatives
You do not need to buy desktop Excel to build this basic calculator. Excel for the web is available through a Microsoft account, although desktop-only features, automation, add-ins, and some workbook capabilities can differ.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchMicrosoft also provides official mortgage and amortization templates. Inspect and modify any template before relying on it for a lender-specific prepayment calculation.
Microsoft 365 Personal is useful if you need desktop Excel, local workbooks, and broader spreadsheet features. Pricing and availability vary by country and can change.
Google Sheets is a practical browser-based alternative for collaboration. Its core financial functions are similar, but Excel-specific templates, VBA macros, formatting, and advanced features may not transfer perfectly.
Financial decisions beyond the spreadsheet
A lower modeled interest bill does not automatically make prepayment the best use of cash. Before making a large payment, compare the guaranteed interest avoided with any prepayment fee, your emergency-fund needs, higher-interest debt, investment alternatives, applicable tax treatment, and the liquidity you give up. The sheet estimates loan mechanics; it cannot decide which option is best for your circumstances.
Recommended Free Tools
Finally, ask the lender for the applicable payment-allocation rule and whether the prepayment will reduce EMI, tenure, or both. Use the lender’s amortization statement or payoff quote as the final authority.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
