Financial model review

How to check a debt schedule in Excel

Review a debt schedule in Excel with a worked example covering opening debt, new borrowing, repayments, interest and links to the cash flow forecast.

Descending debt blocks and coin stacks connected by a repayment ribbon beside a separate interest cube

Check a financial model's debt schedule by rolling each facility from opening to closing principal, calculating interest from its stated terms and tracing cash movements into the forecast. Do those checks separately: a loan balance can reconcile while interest uses the wrong rate, or the cash flow can omit a repayment.

Work in a copy and identify the loan agreement or approved assumptions. These invented examples demonstrate spreadsheet checks, not suitable financing terms, covenant compliance or an accounting policy.

Start with one facility and a clear balance definition

Record the currency, opening principal, rate basis, payment dates, maturity and repayment structure. Check whether the displayed balance includes only principal or also accrued interest, fees or other adjustments. A bank statement and a financial reporting carrying amount may use different definitions.

Keep separate facilities identifiable. Combining an amortizing loan and a revolving facility can hide an overpayment behind another facility's drawdown. A negative principal balance needs explanation, rather than automatically becoming extra available cash.

For each period, begin with this simple principal relationship:

Closing principal = Opening principal + Drawdowns - Principal repayments

Add separately identified non-cash or other adjustments if the actual facility needs them. The IFRS Foundation's explanation of financing-liability reconciliations emphasizes connecting opening balances, closing balances and cash and non-cash changes. A worksheet reconciliation is useful even when the model has a simpler planning purpose.

Calculate a complete monthly example

Assume a fictional loan has these explicit terms:

  • Opening principal: EUR 120,000.
  • Additional drawdown on the first day of the month: EUR 30,000.
  • Nominal annual interest rate: 6%, with a monthly rate of 6% divided by 12.
  • Principal repayment at the end of the month: EUR 10,000.
  • Interest paid at month-end, with no fees, capitalization or other adjustments.

For this simplified equal-month convention, interest is charged on EUR 150,000 for the month:

Monthly interest = (120,000 + 30,000) × 6% / 12 = EUR 750
Closing principal = 120,000 + 30,000 - 10,000 = EUR 140,000
Cash paid = 10,000 principal + 750 interest = EUR 10,750

The borrowing receipt is a separate EUR 30,000 cash inflow. The EUR 10,750 payment must not reduce principal by EUR 10,750: only the EUR 10,000 principal component reduces this example's loan balance.

If the next month has no drawdown and the same EUR 10,000 month-end principal repayment, opening principal is EUR 140,000, interest is EUR 700 and closing principal is EUR 130,000. Check that the second column starts from the first column's closing amount.

Match the interest formula to the actual timing

The example's first-day draw and month-end repayment make the interest base explicit. A draw halfway through a month, daily interest, a different day-count convention or a floating rate can require a different calculation. Do not use the average of opening and closing balances simply because it produces a plausible result.

Separate nominal annual and effective annual rates. For a loan that actually uses a nominal 6% rate divided into twelve monthly periods, the monthly input is 0.5%. That conversion is an assumption about these terms, not a universal rule for every loan.

Microsoft's PMT documentation states that the function assumes constant payments and a constant rate, and that rate and period units must align. For a different illustrative loan of EUR 120,000 repaid through 36 equal month-end payments at that monthly rate:

=-PMT(6%/12,36,120000,0,0)

The payment is approximately EUR 3,650.63 before any separate charges. That formula does not describe the earlier EUR 10,000-principal repayment structure. Identify the repayment pattern before selecting a function.

Trace the connections to cash and reporting

Compare every drawdown and cash repayment with the corresponding forecast line. Make sure interest appears once in the cash forecast if it is paid, and that unpaid interest follows the model's stated treatment. Do not infer cash payment merely from an expense row.

Compare closing debt with the reporting balance for the same date and definition. Explain adjustments rather than inserting a plug. Interest classification depends on the applicable framework and circumstances; this review connects the workbook's stated amounts.

The cash flow checking guide covers the overall opening-to-closing cash bridge. Check maturities beyond the last visible forecast period as well: a model may show modest monthly payments while omitting the final outstanding principal.

Run a few controlled tests

  1. Set the example's rate to zero. Interest should become zero while principal still closes at EUR 140,000.
  2. Reduce the first-day drawdown from EUR 30,000 to EUR 20,000. With other assumptions fixed, interest becomes EUR 700 and closing principal EUR 130,000.
  3. Remove the EUR 10,000 principal repayment. Closing principal becomes EUR 150,000; that month's EUR 750 interest is unchanged under the stated month-end timing.
  4. Follow each change into cash, restore the base case and record the expected and observed results.

For a real facility, also test its limit, repayment boundary and maturity behavior against documented terms. A visible covenant ratio does not prove the model uses the lender's contractual definition.

Use automated observations within their scope

FinModelCheck does not validate loan terms, reconcile debt, calculate interest, evaluate covenants or recalculate Excel. It inspects supported saved errors, broken references, external workbook links, embedded numeric coefficients, broken defined names and saved manual calculation metadata. A coefficient such as division by 12 may be intentional and requires context.

Use the checker and example report alongside the manual tests. Online results contain score and type counts; the complete PDF is free for the current valid owning browser session without registration. The provisional score does not certify a debt schedule.

Frequently asked questions

Is a repayment always principal plus interest?

No. Structures differ. Separate the contractual components, fees and any unpaid amounts before checking how the model records them.

Can I sum monthly closing debt to get annual debt?

No. Closing debt is a balance at a date. Use the relevant year-end balance; sum period movements only when that is the measure required.

Does a zero reconciliation residual prove the debt model is correct?

No. It checks one relationship. Incorrect terms, timing or linked cash formulas can still produce a zero residual.

Keep reviewing

Continue with a related guide.

Browse all guides