Apartment DSCR calculator in Excel.
Test a loan on an apartment building the way a lender will: NOI against the payment, a minimum coverage ratio, rate sensitivity, and a vacancy stress.
Apartment lenders do not just divide.
Most DSCR calculators online are built for single-family rentals. One rent, one mortgage payment, divide. Apartment lenders rebuild your income with a vacancy allowance, deduct management and reserves whether you pay them or not, and test the result against an amortizing payment.
This workbook does that for a 5+ unit building. If the loan you have in mind does not clear the minimum, it tells you the loan that would, and the rate at which your request just squeaks through. The example building is fictional.
- Find out whether a loan clears a 1.20x or 1.25x minimum before you apply, not after the appraisal.
- See the largest loan the building's income supports at the rate you enter, amortizing and interest-only.
- Read the cushion or shortfall in dollars, so you know how much to cut the request or how much rent has to rise.
- Watch DSCR move across five rates, a half point apart.
- Stress the building at 10%, 15%, and 20% vacancy and find the break-even occupancy.
- Size an offer before the earnest money goes hard.
Three sheets. A yes or a no.
Pale-yellow cells are yours to fill. Everything else is a formula you can read and follow.
From rent roll to coverage in four steps.
- Inputs sheet
Enter the income
Every unit at its lease rent for twelve months, vacant units at market. The vacancy allowance defaults to 5%, the floor most lenders use even on a full building.
- Inputs sheet
Enter the expenses
Use what you actually pay, with two exceptions. Use the tax bill the building will carry after your purchase, not the seller's. And enter management even if you self-manage.
- Inputs sheet
Set the loan to test
Loan amount, rate, and amortization. The example uses 7% so the formulas work, but it is not a quote. 1.20x is a reasonable minimum for a bank. Some investor programs go lower on smaller loans.
- DSCR sheet
Read the answer
The Coverage section says yes or no. The section below it says what loan would be a yes.
DSCR is one division. NOI is the hard part.
- Gross scheduled rent
- $528,000
- Other income
- $18,000
- Vacancy (5%)
- −$27,300
- Effective gross income
- $518,700
- Operating expenses
- −$194,000
- Reserves ($250 × 40)
- −$10,000
- Net operating income
- $314,700
Amortizing DSCR
Uses the fully amortizing payment. This is what most lenders test, even when the loan has an interest-only period.
Interest-only DSCR
Uses the interest-only payment. This is what your cash flow actually looks like during that period.
Maximum loan
NOI divided by the minimum DSCR gives the debt service the building can carry. Rate and amortization turn that into a loan amount.
Break-even occupancy
The occupancy at which NOI exactly covers the payment. Above it, the building carries the loan. Below it, you do.
A fictional 40 unit in Columbus.
These are the numbers already loaded in the workbook, so you can open it and follow along.
- Units
- 40
- Gross scheduled rent
- $528,000
- Other income
- $18,000
- Operating expenses
- $194,000
- Reserves
- $250 per unit
- Price
- $4,600,000
- Loan requested
- $3,400,000
- Rate and term tested
- 7%, 30 years
- Minimum DSCR
- 1.20x
1.16xInterest-only
1.32x
Amortizing DSCR is 1.16x and interest-only DSCR is 1.32x, against a 1.20x minimum.
- NOI
- $314,700
- Monthly payment
- $22,620
- Annual debt service
- $271,443
- Rate that just clears
- 6.66%
- Break-even occupancy
- 87%
- Loan-to-value
- 74%
| Rate | DSCR | Maximum loan |
|---|---|---|
| 6.00% | 1.29x | $3,645,000 |
| 6.50% | 1.22x | $3,457,000 |
| 7.00%Your rate | 1.16x | $3,284,000 |
| 7.50% | 1.10x | $3,125,000 |
| 8.00% | 1.05x | $2,978,000 |
| Vacancy | NOI | DSCR |
|---|---|---|
| 5%Base | $314,700 | 1.16x |
| 10% | $287,400 | 1.06x |
| 15% | $260,100 | 0.96x |
| 20% | $232,800 | 0.86x |
The building drops below 1.00x at 15% vacancy. Worth knowing before signing a contract with a 74% loan in the assumptions.
Three ways to get to yes.
When the request does not clear, the DSCR sheet tells you how far off it is. In the example, any one of these closes the gap.
Cut the loan to $3,284,000, the most the income supports at 7%.
Raise rents or trim expenses until NOI covers the payment at 1.20x.
Below this rate, the full $3,400,000 request clears the minimum.
Common errors and fixes.
DSCR is lower than you expected.
Check the vacancy allowance, management, and reserves. Those three lines are the usual difference between an owner's NOI and a lender's.
The maximum loan is far below the request.
The building may be priced off a cap rate the current rate cannot support. That is a purchase price problem, not a lender problem.
Interest-only DSCR looks fine, amortizing does not.
Most lenders test the amortizing payment. Plan on that one.
The rate that clears shows 0%.
The request is so far above what the income supports that no positive rate works. Reduce the loan.
What it covers, and what it does not.
- One building and one loan at a fixed rate.
- Coverage on both the amortizing and interest-only payment.
- A per-unit replacement reserve allowance.
Buyers use the DSCR sheet to size an offer before the earnest money goes hard. Owners use it to see whether a refinance at today's rates would still cover, and by how much.
The vacancy stress is the table to look at when a building has two or three leases rolling in the same quarter.
When the numbers look right, send the rent roll, the trailing twelve months, and the loan you have in mind. Capituro will review the request with a rate that reflects the property and the sponsor.
Your working copy.
Review the inputs, formulas, and limits before adapting it. Keep the original and validate formulas after every change.
- Direct download
- No account
- Editable file
- Opens in Sheets and Numbers
Where this leads next
5 to 10 Unit DSCR
Long-term debt for smaller apartment buildings, sized on the rent roll.
Open10+ Unit Apartment Refinance
Refinance larger apartment buildings on the property's income.
OpenApartment Cash-Out Refinance Calculator
Size a cash-out refinance by LTV and DSCR, and see cash to you.
OpenRefinance or Hold Calculator
See whether a refinance pays for itself before the current loan ends.
OpenFrequently asked
What is a good DSCR on an apartment building?+
Why does the calculator deduct management when I manage the building myself?+
Does an interest-only period raise my DSCR?+
Where does the rate come from?+
Does this replace a lender's sizing?+
Send the rent roll for a real sizing.
We will review the request with a rate that reflects the property and the sponsor, rather than the placeholder on the Inputs sheet.