Cash Flow · 2026-07-13 · 8 min read

Written and reviewed by Project Financial Advisor · FCA · CGMA · ACMA — Chartered Accountant

Debt Schedule & Loan Amortization: How to Build One

How to build a debt schedule in Excel — the amortization formula, splitting interest from principal, and why that split shifts across the life of a loan.

The debt schedule is the engine room of a financial model. It turns a loan into the interest that hits your income statement, the principal repayment that hits your cash flow, and the closing balance that sits on your balance sheet. Get it wrong and all three statements are wrong. Yet it is one of the most commonly fudged parts of a DIY model — usually with a flat 'interest = loan × rate' that ignores repayment entirely.

The rows every debt schedule needs

That roll-forward is the whole structure. Interest is charged on the opening balance (or an average balance, if you want to be precise about mid-period drawdowns), principal reduces the balance, and the closing figure carries into the next period. Because each row links to the last, changing a single assumption — rate, tenor, drawdown — ripples correctly through the entire forecast.

Amortising, bullet or revolver

Three structures cover most models. An amortising loan repays principal on a schedule across its life, usually as a level payment. A bullet (or balloon) loan pays interest only and repays the full principal at maturity — common in project finance and real estate. A revolver is drawn and repaid flexibly as cash needs move, and is the natural place to put a model's cash 'plug'. Each behaves very differently in the cash flow statement, which is why the model needs the facility type as an explicit input.

The amortization split

For a level-payment amortising loan, the payment stays constant but its composition shifts dramatically. Early on, the balance is large so most of the payment is interest; as the balance falls, interest shrinks and principal accelerates. This is why paying down a loan feels slow at first — and why the split, not just the payment, is what your model must compute.

A worked schedule

Take a $1,000,000 loan at 6% over 10 years. The level annual payment is $135,868. Year 1 charges $60,000 of interest (6% of the opening $1m), leaving $75,868 of principal repayment and a closing balance of $924,132. Year 2 charges interest on that lower balance — $55,448 — so more of the same payment goes to principal. By year 10 the loan is almost gone and interest is a rounding error.

Why the split matters

Interest and principal go to completely different places. Interest is an expense: it reduces profit and, because it is tax-deductible, reduces cash tax too. Principal is not an expense at all — it never touches the income statement and moves only through financing cash flow and the balance sheet. Treating a whole loan payment as a cost overstates expenses and understates profit; treating it all as principal overstates profit and understates the tax shield. Both errors are common, and both break the balance sheet.

Common mistakes

Charging interest on the closing rather than opening balance (a circular reference waiting to happen); forgetting a grace period where interest accrues but principal does not; expensing principal repayments; and omitting the debt entirely from the balance sheet so it never reconciles. A live integrity check catches all four immediately.

Build the debt schedule automatically

EasyFinancialModels builds a full debt schedule from a few inputs — principal, rate, tenor, grace period and facility type — computing drawdown, interest, repayment and closing balance as live Excel formulas, then feeding the three linked statements and the coverage ratios lenders test. Use our cashflow forecasting model free for up to 3 years and let the schedule build itself.

→ Build your cash flow forecasting model free with the Cashflow Forecasting tool

More Cash Flow Forecasting guides

How to Build a Cash Flow Forecast in Excel (Free Template + Steps) · 13-Week Cash Flow Forecast: A Practical Guide for Tight Cash · Direct vs Indirect Cash Flow Forecasting: Which Method to Use · Cash Flow Forecasting for Startups: Runway, Burn Rate & When to Raise · Working Capital Days Explained: DSO, DIO & DPO and Why They Drive Cash

About the author

Every model is built and reviewed by the project's Financial Advisor — a Fellow Chartered Accountant (FCA), Chartered Global Management Accountant (CGMA) and Associate Chartered Management Accountant (ACMA) with around two decades of corporate finance, audit and accounting experience, designing investor-grade financial models across industries. Full credentials and background are available on LinkedIn. More about the author →

← All guides · WACC calculator · DCF calculator · IRR calculator · Inside the 15-sheet model