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.
Assumptions
The property
Stabilised and underwritten. Build it line by line in the pro forma tool.
Applied across the term, so the coverage table has a real minimum rather than year one repeated.
All in, including acquisition costs. Buying below value is exactly the case where loan to cost binds.
The four tests
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.
Loan terms
The schedule the payment is computed over, which is longer than the term. The gap between them is the balloon.
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
Model
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.47x
Debt yield
10.14%
Balloon at maturity
$8,601,522
Build
| Max loan by loan to valuevalue x max LTV | $9,750,000 |
|---|---|
| Max loan by debt service coverageNOI / min DSCR / loan constant | $10,703,399 |
| Max loan by loan to costtotal cost x max LTC | $10,710,000 |
| Max loan by debt yieldNOI / min debt yield | $10,983,778 |
| Loan amountthe smallest of the four tests | $9,750,000 |
| Implied LTV | 65.00% |
| Implied LTC | 63.73% |
| Implied DSCR | 1.37x |
| Implied debt yield | 10.14% |
| Loan constantannual debt service per dollar of loan | 7.39% |
| Annual debt service | $720,389 |
| Minimum DSCR over the term | 1.47x |
| Balloon at maturitythe 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 maturity | $1,377,017 |
| Projected value at maturity | $20,894,714 |
| 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 = $872,097 | $872,097 |
| NOI required to refinancethe larger of NOI required for coverage and NOI required for value | $872,097 |
| Take-out loan by value | $13,581,564 |
| Take-out loan by coverage | $14,523,936 |
| Refinance surplus or shortfallthe smaller take-out loan less the balloon | $4,980,042 |
| Cap rate required at that value$1,377,017 / $13,233,111 = 10.41% | 10.41% |
Amortisation and coverage
| Line item | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 | Y7 | Y8 | Y9 | Y10 |
|---|---|---|---|---|---|---|---|---|---|---|
| Debt service and coverage by year | ||||||||||
| Net operating income | $988,540 | $1,021,854 | $1,056,290 | $1,091,887 | $1,128,684 | $1,166,721 | $1,206,039 | $1,246,682 | $1,288,696 | $1,332,125 |
| 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: $988,540 / $609,375 = 1.62x | 1.62x | 1.68x | 1.47x | 1.52x | 1.57x | 1.62x | 1.67x | 1.73x | 1.79x | 1.85x |
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 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,408,135 |
| 7.25% | $9,750,000 | $9,750,000 | $9,660,651 | $9,289,087 | $8,945,047 |
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.