How to Build a Loan Amortisation Schedule in Microsoft Excel Using PMT, PPMT, and IPMT

Whether evaluating commercial equipment financing, corporate bond liabilities, or consumer residential mortgages, financial modelers require clear visibility into how debt obligations amortize over time. While a simple loan payment formula determines the fixed periodic installment, each payment is composed of two shifting elements: an interest charge that declines over time, and a principal reduction that increases over the loan duration.

Microsoft Excel provides a specialized suite of native financial functions—PMT, PPMT, and IPMT. By combining these functions into a structured table, you can build a dynamic, self-auditing loan amortisation schedule that calculates exact payment splits across any loan term.

Understanding the Financial Function Suite

Excel models fixed-rate amortizing loans using three complementary formulas:

• PMT (Payment): Calculates the total fixed periodic payment required to retire the loan balance.

  =PMT(rate, nper, pv, [fv], [type])

• PPMT (Principal Payment): Calculates the portion of a specific payment allocated directly toward reducing the loan principal.

  =PPMT(rate, per, nper, pv, [fv], [type])

• IPMT (Interest Payment): Calculates the interest portion charged on the remaining loan balance for a specific payment period.

  =IPMT(rate, per, nper, pv, [fv], [type])

Setting Up the Loan Parameters Block

In a new Excel worksheet, establish a dedicated parameters block in cells A1:B5:

• B1: Loan Amount (PV): 250000 (e.g. £250,000)
• B2: Annual Interest Rate: 0.06 (6.00%)
• B3: Loan Term in Years: 5
• B4: Payments per Year: 12 (Monthly payments)
• B5: Total Payment Periods (nper): =B3 * B4 (60 months)
• B6: Periodic Monthly Rate: =B2 / B4 (0.50% per month)

Calculating the Fixed Monthly Payment with PMT

In cell B7, calculate the total monthly payment:

=-PMT(B6, B5, B1)

Note: The leading minus sign (-) converts Excel’s standard cash outflow convention (which returns negative numbers) into a positive reporting value. For a £250,000 loan at 6% over 5 years, the monthly payment is £4,833.20.

Building the Dynamic Amortisation Table

In row 9, construct your schedule table headers across columns A through F:

1. Period (A): Numbered 1 through 60 (or dynamically generated using =SEQUENCE(B5)).
2. Beginning Balance (B): For Period 1, reference =B1. For subsequent rows, reference the previous period’s ending balance.
3. Total Payment (C): Fixed monthly installment:

   =$B$7

4. Principal Paid (D): Calculated using PPMT:

   =-PPMT($B$6, A10, $B$5, $B$1)

   Where A10 references the current period number.
5. Interest Paid (E): Calculated using IPMT:

   =-IPMT($B$6, A10, $B$5, $B$1)

6. Ending Balance (F): Calculated as Beginning Balance minus Principal Paid:

   =B10 - D10

Verifying Mathematical Integrity

A properly structured amortisation schedule satisfies two rigorous mathematical checks:

• Period Sum Identity: In every row, Principal (D) + Interest (E) must equal the total payment in column C.
• Zero Terminal Balance: In the final row (period 60), the ending balance must reach exactly £0.00.

By leveraging Excel’s financial functions, you eliminate manual interest tracking errors and provide clear, audit-ready debt schedules for corporate budgeting and personal financial planning.