EMI & Working-Capital Loan Tracker in Excel

Last updated: 27 June 2026 · EMI formula, amortisation schedule and a free template.

An EMI tracker in Excel calculates your equated monthly instalment with the PMT function, then builds a full amortisation schedule — splitting every EMI into interest and principal with IPMT and PPMT — so you see each due date, the closing balance, and exactly how much of each payment is interest. Add a due-date alert column and it doubles as a calendar so no EMI slips.

Key takeaways

• PMT(rate, nper, −principal) computes the monthly EMI from loan amount, tenure and rate.
• Always use the monthly rate: =annual rate / 12, and tenure in months.
• IPMT gives the interest portion of any EMI; PPMT gives the principal portion — they sum to the EMI.
• The amortisation schedule shows the balance falling and interest shrinking month by month.
• A due-date + days-to-due column turns the tracker into an EMI calendar so payments are never missed.
• For an overdraft / cash-credit (working-capital) limit, interest is on the used balance — track drawdowns and daily interest, not a fixed EMI.

Fact box. An EMI (Equated Monthly Instalment) is a fixed monthly payment that stays the same across the tenure, but its split changes: early EMIs are mostly interest, later EMIs are mostly principal. The amortisation schedule is what makes that split visible.

What is the EMI formula in Excel?

Excel's built-in PMT function calculates the EMI directly — no manual interest maths:

EMI = PMT(monthly_rate, tenure_months, -loan_amount)

For a ₹10,00,000 loan at 12% per year for 36 months: =PMT(12%/12, 36, -1000000) → the monthly EMI.

Three rules to get it right:

1. Rate must be monthly: divide the annual rate by 12 (12%/12 = 1%).
2. Tenure must be in months (3 years = 36).
3. Enter the loan amount as negative (or negate the result) so the EMI shows as a positive number.

Fact box. PMT assumes a fixed (reducing-balance) interest loan with equal instalments — the standard for business term loans. For flat-rate quotes, convert to the effective reducing rate first, or the EMI will be understated.

How do I split each EMI into interest and principal?

Use IPMT (interest) and PPMT (principal) for any given instalment number n:

Interest in month n  = IPMT(monthly_rate, n, tenure_months, -loan_amount)
Principal in month n = PPMT(monthly_rate, n, tenure_months, -loan_amount)

For every n, IPMT + PPMT = EMI. Interest is highest in month 1 and falls each month; principal does the reverse. This split is what your books and your tax computation need — interest is a deductible business expense; principal repayment is not.

How do I build the amortisation schedule?

Lay one row per instalment:

Steps:

1. Put the inputs in named cells: loan amount (P), annual rate, tenure, start date.
2. Compute the EMI once with PMT and reference it in column C.
3. Row 1 opening balance = loan amount; closing = opening − principal.
4. Each later row's opening = = the row above's closing.
5. Copy down for the full tenure — the closing balance should reach ≈ ₹0 on the last row.
6. Add a due-date alert: Days to EMI = Due Date − TODAY(), with conditional formatting (amber within 5 days, red if overdue).

This schedule also tells you the total interest over the loan (=SUM of the interest column) — useful for comparing two loan offers.

How is a working-capital / cash-credit loan different?

A term loan has a fixed EMI; an overdraft (OD) or cash-credit (CC) working-capital limit does not. You draw and repay freely up to a sanctioned limit, and interest is charged only on the outstanding (used) balance, usually monthly.

Track it differently:

1. Log every drawdown and repayment with dates — running balance like a party ledger.
2. Estimate monthly interest ≈ average outstanding × monthly rate (banks compute on daily balances; this approximates it).
3. Watch headroom: Limit − Outstanding is the cash you can still draw.
4. Feed the interest into your cash-flow forecast as a monthly outflow.

A free EMI and loan tracker template is available to download, no sign-up — enter the loan inputs and the amortisation schedule and due-date alerts build themselves.

How Ankeshan helps: Ankeshan keeps every loan EMI and working-capital limit inside Excel — amortisation, interest-vs-principal split and upcoming due dates update automatically, and EMIs flow into your cash-flow forecast. It's launching soon; join the waitlist.

Frequently asked questions

What is the EMI formula in Excel? =PMT(annual_rate/12, tenure_in_months, -loan_amount). Use the monthly rate and tenure in months, and enter the loan as a negative so the EMI returns positive.

How do I separate interest and principal in an EMI? Use IPMT(rate, n, nper, -principal) for the interest part of instalment n and PPMT(rate, n, nper, -principal) for the principal part. They always add up to the EMI.

Why is most of my early EMI interest? In a reducing-balance loan, interest is charged on the outstanding balance, which is highest at the start. So early EMIs are mostly interest and later ones mostly principal, even though the EMI amount stays fixed.

How do I track an overdraft or cash-credit limit? Don't use a fixed EMI. Log each drawdown and repayment as a running balance, charge interest on the used amount monthly, and track remaining headroom (limit minus outstanding).

Is the template free? Yes — free to download, no sign-up.

Sources

• Microsoft Excel function reference — PMT, IPMT, PPMT, EDATE, TODAY: support.microsoft.com.
• General practice for loan amortisation and working-capital (OD/CC) accounting.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: Cash Flow Statement & Forecast in Excel » · Daily Cash Position Sheet in Excel » · Accounts Payable Tracker in Excel » · Cashflow pillar »