> ## Documentation Index
> Fetch the complete documentation index at: https://docs.francis.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Forecast loans

> How to model the loan balance in Francis, including opening balance, interest, principal repayments, and new draws.

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](/masterclasses/budgeting-forecasting/bs-forecasting/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

<Tabs>
  <Tab title="Driver-based">
    **Set up the supporting sheet**

    Create 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 %**

    ```typescript theme={null}
    = "Annual interest rate %"[0] / 12
    ```

    **Starting value**

    Hardcode the loan balance in the first forecast period. From the second period onwards:

    ```typescript theme={null}
    = "Ending value"[-1]
    ```

    <Note>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.</Note>

    **Total payment**

    Hardcode the monthly payment from the loan agreement. This stays constant for a fixed-rate amortising loan.

    **Interest**

    ```typescript theme={null}
    = "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**

    ```typescript theme={null}
    = "Total payment"[0]
    - "Interest"[0]
    ```

    **Ending value**

    ```typescript theme={null}
    = "Starting value"[0]
    - "Principal repayment"[0]
    ```

    The balance sheet row references this ending value.

    <Note>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.</Note>
  </Tab>

  <Tab title="Statistical">
    Statistical is not applicable to loans. Repayments are contractual; historical patterns do not predict future loan activity.
  </Tab>

  <Tab title="Hardcoded">
    Enter management estimates directly on the balance sheet for each period: the expected draw, repayment, and closing balance. This works when you have a known schedule and do not need the interest and principal calculation built into the model.

    Document the facility name, original principal, maturity date, and interest rate in a formula note. Hardcoded values require a manual update if terms are renegotiated.
  </Tab>
</Tabs>

## FAQ

<AccordionGroup>
  <Accordion title="How do I model accrued interest on a loan?">
    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:

    ```typescript theme={null}
    = "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.
  </Accordion>

  <Accordion title="How do I model a bullet loan?">
    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.
  </Accordion>

  <Accordion title="How do I handle a loan with a variable repayment schedule?">
    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.
  </Accordion>
</AccordionGroup>