Finxora Logo
Sign In
6 Excel Formulas for a Clear Loan Amortization Schedule
Back to Blog

6 Excel Formulas for a Clear Loan Amortization Schedule

FINXORA
5 min read
excel
finance
amortization
loan
modeling
formulas

Build a loan amortization schedule with PMT, IPMT, PPMT, CUMIPMT, CUMPRINC and EFFECT, using consistent signs, monthly periods and practical checks.

Start with the loan assumptions

An amortization schedule separates each repayment into interest and principal and tracks the outstanding loan balance. Excel can prepare this schedule for a loan with constant interest and regular payments. The calculations are also helpful when preparing an Indian business loan projection, provided the model matches the proposed repayment terms.

The example below uses a hypothetical loan of Rs 10,00,000, nominal annual interest of 12%, 60 monthly instalments and payments at the end of each month. It assumes the full amount is disbursed at the start, interest is calculated using a monthly rate and there are no fees, moratorium, rate changes or prepayments. These assumptions are important limitations, not minor formatting choices.

Set up one input block

Enter the principal in B2 as 1000000, annual nominal rate in B3 as 12%, payments per year in B4 as 12 and total payments in B5 as 60. Use B6 for payment timing: 0 means the end of the period and 1 means the beginning. This example uses 0 throughout. Keeping inputs together reduces accidental differences between formulas.

The monthly rate is B3/B4. Do not enter 12 when the cell should contain 12%, and do not combine a monthly rate with a term expressed in years. Use absolute references for the input cells when copying schedule formulas. The period number should change as the formula moves down the table.

1. PMT calculates the regular instalment

=-PMT($B$3/$B$4,$B$5,$B$2,0,$B$6)

PMT uses the rate per period, number of periods and present loan value to calculate a constant payment. The fourth argument is the desired future balance, set to zero. With positive principal, Excel returns the payment as a negative cash flow. The minus sign outside the function displays it as a positive outflow amount for this schedule.

For the example, the monthly payment is approximately Rs 22,244.45. This is the principal-and-interest instalment under the stated assumptions. Processing fees, insurance, taxes or other charges are not automatically included. Do not describe the result as the lender's complete cost of credit without considering those additional cash flows.

2. IPMT separates a period's interest

=-IPMT($B$3/$B$4,A10,$B$5,$B$2,0,$B$6)

If A10 contains 1, this calculates the first payment's interest using the same loan assumptions. The first month's interest in the example is Rs 10,000 because the opening balance is Rs 10,00,000 and the monthly rate is 1%. The period argument must run from 1 to 60; it is not a date or a year number.

Interest normally decreases as the outstanding balance reduces in this constant-rate structure. If the schedule instead shows the same interest every month, check whether a fixed first-period reference has been copied throughout. The function should use the current row's payment period while retaining the common loan input references.

3. PPMT separates a period's principal

=-PPMT($B$3/$B$4,A10,$B$5,$B$2,0,$B$6)

PPMT calculates the principal portion of the payment. For the first month, principal repayment is approximately Rs 12,244.45. Together, positive interest and principal should equal the positive PMT amount, subject to display rounding. Subtract this positive principal amount from the opening balance to obtain the closing balance.

Use consistent signs. Mixing a negative principal result with a formula that subtracts principal will make the balance increase. It is acceptable to use signed cash flows throughout a model, but the balance roll-forward must follow that convention. The formulas here deliberately display payment, interest and principal as positive amounts.

4. CUMIPMT totals interest over a period range

=-CUMIPMT($B$3/$B$4,$B$5,$B$2,1,12,$B$6)

This totals interest for payments 1 through 12, including both endpoints. It is convenient for annual projection schedules, but payment periods are not automatically Indian financial years. If the loan begins partway through a financial year, map actual payment dates to April-March reporting periods and use the relevant range for each year.

CUMIPMT requires the timing argument and uses a positive present value in this example. For a zero-interest loan, calculate zero interest directly rather than relying on a cumulative function that expects a positive rate. Sum the detailed schedule as a cross-check whenever annual interest feeds the projected profit and loss account.

5. CUMPRINC totals principal repayment

=-CUMPRINC($B$3/$B$4,$B$5,$B$2,1,12,$B$6)

CUMPRINC totals the principal repaid over the selected payment range. The total across periods 1 to 60 should equal the original Rs 10,00,000 when the target closing balance is zero. For an annual balance sheet, reconcile opening debt less principal repayments plus any new drawdowns to the closing debt balance.

Principal repayment is a financing cash outflow and a reduction in the loan liability. It is not an operating expense in the profit and loss account. Keeping this distinction clear prevents a common projection error: charging the full EMI as an expense while also recognising interest separately.

6. EFFECT converts nominal to effective annual interest

=EFFECT($B$3,$B$4)

At 12% nominal annual interest compounded monthly, the effective annual rate is approximately 12.6825%. EFFECT describes the impact of compounding under those inputs. It does not include processing charges or irregular cash-flow timing, so it is not automatically an all-in annual borrowing cost. It also does not replace the monthly rate used in the example's instalment formulas.

Assemble and verify the schedule

Create columns for period, opening balance, payment, interest, principal and closing balance. Start with period 1 and opening principal. Use PMT, IPMT and PPMT for the payment components, then calculate closing balance as opening balance minus principal. Each later row opens with the previous row's closing balance.

  • Confirm payment equals interest plus principal in each row.
  • Check that total principal equals the amount borrowed.
  • Check the final balance is close to zero before display rounding.
  • Reconcile cumulative functions with sums of the detailed rows.
  • Keep full precision in calculations and round only for presentation.

Floating rates, staged disbursements, daily interest, irregular payments and moratoria need a schedule reflecting the actual contract. For related calculations, visit Finxora tools. For a broader bank proposal, Finxora's project report software includes EMI repayment schedules and DSCR alongside projected statements. Compare any output with the lender's terms before using it for repayment planning.

Store the assumed first repayment date with the inputs. This helps reviewers understand why an annual summary contains fewer than twelve payments in its opening year.

Frequently Asked Questions

Published on February 14, 2026

Updated on July 24, 2026

Back to Blog

Need bank-ready financials? Create a CMA report or a project report with Finxora in minutes.