How to Model a Property Loan in Excel: LTV, ICR and DSCR

To model a property loan in Excel, set up the loan terms, build a schedule of opening balance, drawdowns, interest, repayments and closing balance, then test the lender's covenants: loan to value (LTV), interest cover ratio (ICR) and debt service cover ratio (DSCR). Here is each step with a worked example, including a stress test.

The quick version

• A loan model needs the loan amount, interest rate, fees, repayments and term.

• LTV = loan ÷ value. ICR = income ÷ interest. DSCR = income ÷ (interest + scheduled repayments).

• Test covenants in a base case and in a downside case.

• If a covenant breaks, your model should show how much you would need to repay to fix it.

What do you need?

• The loan amount, or the LTV used to size it

• Interest: a reference rate (such as SONIA) plus the lender's margin, fixed or floating

• Fees: arrangement, exit and commitment fees

• The repayment profile: interest only, amortising, or repaid at sale or refinance

• The covenant levels agreed in the term sheet

How do you model a loan, step by step?

1. Put the loan terms in an inputs area.

2. Create the schedule: opening balance, drawdown, repayment, closing balance. Closing = opening + drawdown − repayment.

3. Calculate interest: opening balance × (reference rate + margin) ÷ periods per year.

4. Add fees on the dates they are paid.

5. Add the covenant calculations below the schedule.

6. Add conditional formatting so a breach turns red.

7. Add checks: the loan is fully repaid by maturity, and the balances reconcile.

LTV, ICR and DSCR: a worked example

A property is valued at £10,000,000. LTV is 60%, so the loan is £6,000,000. Assume an all-in interest rate of 6.5% (illustrative: reference rate plus margin), interest only, plus 1% a year amortisation for the DSCR test. Net operating income is £600,000.

How do you stress-test the covenants?

Say the covenants are LTV no higher than 65% and ICR no lower than 1.5x. Now stress the deal: value falls 10% to £9,000,000 and income falls 8% to £552,000.

To cure the LTV breach you would repay £150,000 (the loan must fall to 65% of £9,000,000 = £5,850,000). To cure the ICR breach, interest must fall to £552,000 ÷ 1.5 = £368,000, which means a loan of £5,661,538, a repayment of £338,462. So you would need about £338,000 to fix both. A good model works out the cure amount for you. Cure amounts are often held in a cure account.

Fixed or floating, and what is hedging?

A floating loan pays the reference rate plus margin, so the cost moves with the market. A fixed loan fixes the reference rate for some or all of the term. Borrowers often use a swap (exchanging floating for fixed) or a cap (setting a maximum rate) to manage the risk. Model the cost of the hedge as well as its effect on interest. See our guide to key debt terms for the definitions.

What happens at refinance?

At refinance, a new loan is drawn and the old loan is repaid on the same date. Check the new loan against the lender's tests, and include any exit fee on the old loan and arrangement fee on the new one.

How big a loan will a lender give you?

Lenders usually size a loan at the lowest of several limits. Using the same property (value £10,000,000, NOI £600,000, interest 6.5%), here are three common limits:

• LTV limit of 65%: maximum loan £6,500,000.

• ICR limit of 1.5x: interest can be at most £400,000, so the maximum loan is £400,000 ÷ 6.5% = £6,153,846.

• Debt yield limit of 9% (NOI ÷ loan): maximum loan £6,666,667.

The lowest is the ICR limit, so the loan is sized at about £6,150,000, which is 61.5% of value. Higher interest rates reduce the loan an ICR test allows, which is why borrowing costs change how much you can borrow as well as your returns.

How do you show covenants in the model?

Add a row for each test with the covenant level beside it, then a row that shows Pass or Breach. Use conditional formatting to turn a breach red. Add a summary at the top that shows the lowest headroom across the hold period, because lenders look at the weakest year, not the average.

A checklist before you send a loan model

• Is the loan fully repaid by the end?

• Are all fees included?

• Does the interest rate match the term sheet?

• Do the covenant tests use the same definitions as the loan agreement?

• Have you tested a downside case and included hedging costs?

Common mistakes

• Using annual income against quarterly interest in covenant tests.

• Forgetting fees, hedging costs or exit fees.

• Creating circular references by charging interest on a balance that depends on that interest. Use opening balances, or switch circularity on deliberately.

• Only testing the base case.

Frequently asked questions

What is the difference between ICR and DSCR?

ICR compares income with interest only. DSCR compares income with interest plus scheduled loan repayments, so it is lower when a loan amortises.

What is LTV?

Loan to value: the loan amount divided by the value of the property, shown as a percentage.

What happens if you breach a covenant?

It depends on the loan agreement. Common results include a cash trap, a requirement to repay part of the loan or add equity, default interest or, in serious cases, enforcement. Read your own terms.

Want to learn how to do this properly?

The Debt and Financing for Real Estate course covers term sheets, drawdowns, fees, hedging, LTV, ICR, DSCR and cure accounts, with a debt modelling test at the end.

• Explore the Debt and Financing for Real Estate course

• Join the EiP Academy for all nine courses

Keep learning

• Key definitions in debt terms

• Development finance and rolled-up interest

• Sensitivity analysis in Excel