Skip to main content
Loans sit on the liability side of the balance sheet and represent outstanding principal owed to lenders. Common examples include term loans, mortgage facilities, and revolving credit facilities. Balance sheet forecasting is always a function of last period’s value, additions, and detractions. See BS fundamentals. For loans, this means loan balance last period + new draws in period − principal repayments in period. Where interest is not paid in the period, it accrues to the balance as an additional addition.

​
What approach to use

Driver-based is the right default. Build the amortisation schedule from the total payment and interest rate, and let the interest charge and principal split calculate automatically. When rate assumptions change, the balance updates throughout the forecast. Statistical is not applicable to loans. Balances are contractual, not pattern-driven. Use hardcoded when you have a complete repayment schedule from your lender with exact draws, repayments, and interest charges per period already calculated.

​
Where to forecast loans

  • Supporting sheet: right for most cases. One section per loan facility, with opening balance, total payment, monthly rate, interest, and principal. The balance sheet references the ending balance from each section.
  • Directly on the balance sheet: right for a single simple loan with no interest calculation needed.

​
Forecasting approaches

Set up the supporting sheetCreate one section per loan facility with seven rows: Annual interest rate %, Monthly interest rate %, Starting value, Total payment, Interest, Principal repayment, and Ending value.Annual interest rate %Hardcode the annual rate. This is a fixed assumption for the facility.Monthly interest rate %
= "Annual interest rate %"[0] / 12
Starting valueHardcode the loan balance in the first forecast period. From the second period onwards:
= "Ending value"[-1]
When setting up the supporting sheet, pull the opening loan balance with a formula, so the starting value sits in the actuals layer and feeds the forecast automatically. As you move the Forecast Start date forward, periods that were forecast become actuals, and the formula keeps sourcing each opening balance from the actuals layer with no manual update.
Total paymentHardcode the monthly payment from the loan agreement. This stays constant for a fixed-rate amortising loan.Interest
= "Starting value"[0]
* "Monthly interest rate %"[0]
Interest flows to the P&L as a finance cost. It does not reduce the loan balance.Principal repayment
= "Total payment"[0]
- "Interest"[0]
Ending value
= "Starting value"[0]
- "Principal repayment"[0]
The balance sheet row references this ending value.
Total payment flows to financing activities. Interest flows to operating activities. Keep these separate. Mixing principal and interest in the loan balance is one of the most common errors in three-statement models.

​
FAQ

Most loans require monthly interest payments, which flow to the P&L as a finance cost and do not accumulate on the balance sheet. For loans where interest is deferred or capitalised (PIK interest), the unpaid interest accrues to the loan balance each period. Add an Accrued interest row to the supporting sheet and include it in the ending value formula:
= "Starting value"[0]
- "Principal repayment"[0]
+ "Accrued interest"[0]
The accrued interest row uses the same formula as the regular interest charge. The difference is that it is not paid out in the period, so it stays on the balance sheet rather than flowing to cash. When the interest is eventually settled, model it as a negative entry on the accrued interest row in the period it is paid.
A bullet loan has no principal repayments during the term. The full balance is repaid at maturity. Set repayments to zero for all periods except the final month, where you enter the full outstanding balance as a repayment.
Use a supporting sheet that lists the repayment amount for each period, then reference it into the balance sheet row. This keeps the schedule easy to update if terms change.