Balance-weighted DSCR, LTV and debt yield from a loan tape, why they differ from the pool's aggregate ratios, and the tail the average hides.
The weighted average DSCR of a CMBS pool is each loan's DSCR multiplied by its share of the pool balance, summed. On an illustrative ten-loan, $1,000.0M conduit pool the result is 1.47x, and the same method gives a weighted average LTV of 67.46 per cent. The average is the least informative figure on the tape: 55.5 per cent of the pool is interest-only, which lifts it by 0.14x, and a 20 per cent fall in cash flow pushes 9.0 per cent of the balance below 1.00x while the average still reads 1.17x.
Worked in full in CMBS and CRE CLOs by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
Every presale report and term sheet leads with these averages, so it is worth knowing exactly what they are, how they differ from the pool's aggregate ratios, and which numbers on the tape to read instead.
| Loan | Balance | NCF | Debt service | DSCR | LTV | Debt yield |
|---|---|---|---|---|---|---|
| Office tower (IO) | 150.0 | 13.50 | 9.00 | 1.50x | 65.2% | 9.0% |
| Regional mall (IO) | 120.0 | 13.20 | 7.20 | 1.83x | 72.7% | 11.0% |
| Full-service hotel | 100.0 | 12.00 | 7.19 | 1.67x | 66.7% | 12.0% |
| Multifamily (IO) | 110.0 | 8.40 | 6.60 | 1.27x | 62.9% | 7.6% |
| Industrial (IO) | 90.0 | 7.65 | 5.40 | 1.42x | 60.0% | 8.5% |
| Suburban office | 95.0 | 8.70 | 6.83 | 1.27x | 73.1% | 9.2% |
| Retail strip | 80.0 | 7.60 | 5.76 | 1.32x | 69.6% | 9.5% |
| Self storage (IO) | 85.0 | 7.65 | 5.10 | 1.50x | 60.7% | 9.0% |
| Mixed use | 90.0 | 7.20 | 6.48 | 1.11x | 72.0% | 8.0% |
| Limited-service hotel | 80.0 | 9.60 | 5.76 | 1.67x | 72.7% | 12.0% |
| Pool | 1,000.0 | 95.50 | 65.32 | 1.47x | 67.46% | 9.55% |
Step 1: each loan's ratios. DSCR is underwritten net cash flow over annual debt service: for the office tower, 13.50 over 9.00, or 1.50x. LTV is balance over appraised value; debt yield is NCF over balance.
Step 2: weight by balance. Each loan's weight is its balance over the pool: 150.0 of 1,000.0 is 15.0 per cent for the office tower.
WA DSCR = Σ (balancei × DSCRi) ÷ Σ balancei = 1.47x
In Excel: =SUMPRODUCT(Balance,DSCR)/SUM(Balance)
The same SUMPRODUCT on LTV gives 67.46 per cent, and on debt yield 9.55 per cent.
Divide the pool's total NCF by its total debt service and you get a different number: 95.50 over 65.32 is 1.46x. Divide total balance by total value and LTV is 67.11 per cent, not 67.46. The gap is small here and can be larger on a pool with wide dispersion, but the reason matters more than the size.
Industry convention is the balance-weighted figure, so quote it, but know that it is a statistic about loans, not about the pool's cash.
Interest-only loans. Five loans, 55.5 per cent of the balance, pay interest only, so their debt service runs at the 6.00 per cent coupon rather than the 7.19 per cent amortising constant. Tested on the amortising constant, the pool's weighted average DSCR is 1.33x, not 1.47x. When comparing pools from different vintages, put them on the same constant first.
The tail. The mixed-use loan, 9.0 per cent of the pool, sits at 1.11x and breaks 1.00x on a 10.1 per cent fall in cash flow, while the pool in aggregate can lose 31.6 per cent. And 38.5 per cent of the balance carries an LTV above 70 per cent, which no average reveals.
| NCF haircut | WA DSCR | Lowest loan | Balance below 1.25x | Balance below 1.00x |
|---|---|---|---|---|
| 0% | 1.47x | 1.11x | 9.0% | 0.0% |
| 10% | 1.32x | 1.00x | 37.5% | 0.0% |
| 20% | 1.17x | 0.89x | 70.0% | 9.0% |
| 30% | 1.03x | 0.78x | 88.0% | 46.5% |
Concentration. The three largest loans are 380.0 of the 1,000.0, or 38.0 per cent of the pool. Ten loans is a small pool, but even a conduit of fifty often has a top ten above half the balance, and a weighted average dominated by three loans says more about those three properties than about the pool.
At a 30 per cent haircut the average is still above 1.00x, yet 46.5 per cent of the balance is not covering its debt service. Defaults happen loan by loan, so it is the distribution, not the mean, that drives the losses the lower classes absorb. Which classes those are is set by the stack: see how credit enhancement is calculated in CMBS.
The common mistake is to compare a pool's weighted average DSCR with a covenant or a threshold as if it were a loan. A 1.47x pool is not a pool of 1.47x loans. A second mistake is to trust the NCF column itself: it is the issuer's underwritten figure, usually above trailing actual cash flow, and the rating agencies cut it before running their own ratios. Rebuild the weakest loans' NCF from the operating statements, and then rerun the averages.
Compute the balance-weighted averages because everyone quotes them: 1.47x, 67.46 per cent and 9.55 per cent here. Then report three things beside them: the DSCR on a common amortising constant (1.33x), the shares of balance below 1.25x DSCR and above 70 per cent LTV, and the share below 1.00x under a 20 per cent haircut (9.0 per cent). The book's twenty-loan tape, the stack and the cross-check of the tape are in the free workbook for this case.
No. Weighting by balance gives 1.47x on the illustrative pool; total NCF of 95.50 over total debt service of 65.32 gives 1.46x. The aggregate equals a DSCR weighted by debt service, not by balance. For debt yield the two are identical, 9.55 per cent, because balance is the ratio's own denominator.
They raise it, because debt service is interest alone. In the example 55.5 per cent of the pool is interest-only at a 6.00 per cent coupon against a 7.19 per cent amortising constant. Put every loan on the amortising constant and the weighted average DSCR falls from 1.47x to 1.33x.
The average alone does not answer it. A pool at 1.47x can hold a loan at 1.11x that breaks 1.00x on a 10.1 per cent cash flow fall. Look at the share of balance below 1.25x, 9.0 per cent here, and how it grows under stress: 70.0 per cent of the balance is below 1.25x after a 20 per cent NCF cut.
This article is one calculation from CMBS and CRE CLOs. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: real estate investing, finance and fund management → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.