Real estate

Debt sizing and DSCR

The loan is the smallest number the four tests permit, and the answer is which test that was. Coverage is run year by year against a growing NOI rather than year one repeated, and the balloon is tested against what the property has to be worth, or has to earn, before it can be refinanced.

as entered

Assumptions

base

Property

The property

Net operating income$/yr

Stabilised and underwritten. Build it line by line in the pro forma tool.

NOI growth%/yr

Applied across the term, so the coverage table has a real minimum rather than year one repeated.

Value$

Total cost$

All in, including acquisition costs. Buying below value is exactly the case where loan to cost binds.

The four tests

Maximum loan to value%

Maximum loan to cost%

Set to 0 to turn the test off.

Minimum DSCRx

Minimum debt yield%

NOI over the loan. The one test that needs no opinion of value, which is why a lender falls back on it when it does not believe the appraisal. Set to 0 to turn it off, as most residential lenders do.

Loan terms

Rate%/yr

Termyears

Amortisationmonths

The schedule the payment is computed over, which is longer than the term. The gap between them is the balloon.

Interest onlymonths

Sizing still uses the amortising constant, because a loan sized on the interest only payment breaches its covenant the month amortisation starts.

Refinance at maturity

Take-out loan to value%

Take-out DSCRx

Take-out rate%/yr

Take-out amortisationmonths

Model

Sensitivity grid

Binding constraint: Loan to value

Loan to value binds. The loan is held down by the appraisal, so the argument is about value, not about the operating statement or the rate.

Loan amount

$9,750,000

Minimum DSCR

1.46x

Debt yield

10.26%

Balloon at maturity

$8,601,522

Build

smallest permitted loan first

Calculation build. Each row is one step of the workings, the arithmetic that produced it where there is any, and its value.
Max loan by loan to valuebindingvalue x max LTV	$9,750,000
Max loan by loan to costtotal cost x max LTC	$10,710,000
Max loan by debt service coverageNOI / min DSCR / loan constant	$10,827,482
Max loan by debt yieldNOI / min debt yield	$11,111,111
Loan amountthe smallest of the four tests	$9,750,000
Implied LTV	65.00%
Implied LTC	63.73%
Implied DSCR	1.39x
Implied debt yield	10.26%
Loan constantannual debt service per dollar of loan	7.39%
Annual debt service30y amortisation, heaviest in year 3	$720,389
Minimum DSCR over the termyear 3	1.46x
Balloon at maturityend of year 10the balance the sale or the refinance has to retire	$8,601,522
Principal repaid over the term$9,750,000 - $8,601,522 = $1,148,478	$1,148,478
Projected NOI at maturityyear 11	$1,280,085
Projected value at maturity6.67% cap held flat	$19,201,268
Value required to refinance$8,601,522 / refinance loan to value = $13,233,111	$13,233,111
NOI required for coverage$8,601,522 x refinance constant x refinance coverage = $815,512	$815,512
NOI required for value$13,233,111 x going-in cap rate = $882,207	$882,207
NOI required to refinancethe larger of NOI required for coverage and NOI required for value	$882,207
Take-out loan by value65% LTV	$12,480,824
Take-out loan by coverage1.25x	$13,501,548
Refinance surplus or shortfallthe smaller take-out loan less the balloon	$3,879,302
Cap rate required at that value$1,280,085 / $13,233,111 = 9.67%	9.67%

Amortisation and coverage

10y term on 360 month amortisation

Forecast by year, Y1 to Y2 to Y3 to Y4 to Y5 to Y6 to Y7 to Y8 to Y9 to Y10, containing debt service and coverage by year.
Line item	Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9	Y10
Debt service and coverage by year
Net operating income	$1,000,000	$1,025,000	$1,050,625	$1,076,891	$1,103,813	$1,131,408	$1,159,693	$1,188,686	$1,218,403	$1,248,863
Opening balance	$9,750,000	$9,750,000	$9,750,000	$9,635,750	$9,514,151	$9,384,731	$9,246,986	$9,100,381	$8,944,346	$8,778,275
Debt service	$609,375	$609,375	$720,389	$720,389	$720,389	$720,389	$720,389	$720,389	$720,389	$720,389
Less: interest	($609,375)	($609,375)	($606,139)	($598,790)	($590,969)	($582,644)	($573,784)	($564,354)	($554,318)	($543,636)
PrincipalWorking, Y1: $609,375 - $609,375 = $0	$0	$0	$114,250	$121,599	$129,420	$137,745	$146,605	$156,035	$166,071	$176,753
Closing balanceWorking, Y1: $9,750,000 - $0 = $9,750,000	$9,750,000	$9,750,000	$9,635,750	$9,514,151	$9,384,731	$9,246,986	$9,100,381	$8,944,346	$8,778,275	$8,601,522
DSCRWorking, Y1: $1,000,000 / $609,375 = 1.64x	1.64x	1.68x	1.46x	1.49x	1.53x	1.57x	1.61x	1.65x	1.69x	1.73x

The closing balance in year 10 is the balloon: $8,601,522, or 88.22% of the original loan. It is repaid by a sale or a refinance, not by the schedule.

Sensitivity

interest rate × minimum dscr

Sensitivity table. Rows vary Interest rate, columns vary Minimum DSCR. The base case cell is marked "base case".
Interest rate downMinimum DSCR across	1.15x	1.20x	1.25x	1.30x	1.35x
5.25%	$9,750,000	$9,750,000	$9,750,000	$9,750,000	$9,750,000
5.75%	$9,750,000	$9,750,000	$9,750,000	$9,750,000	$9,750,000
6.25%	$9,750,000	$9,750,000	$9,750,000 base case	$9,750,000	$9,750,000
6.75%	$9,750,000	$9,750,000	$9,750,000	$9,750,000	$9,517,203
7.25%	$9,750,000	$9,750,000	$9,750,000	$9,396,774	$9,048,745

Cells are the sized loan. A flat row means the binding test is on neither axis, which is a finding rather than a fault: a deal held down by the appraisal does not move when you argue about the covenant.

Where the NOI comes from

the pro forma hands it over

Everything here rests on one underwritten NOI. Build it line by line in the rental pro forma, then hand it over: that tool writes the NOI, the value, the basis and the NOI growth rate straight into this one's query string.

Open the rental pro forma →