Commercial property loan calculator in Excel.
Enter the rent roll tenant by tenant. See the NOI the way underwriting builds it, the loan it carries, and how much of the rent is up for renewal before the loan ends.
On commercial property, the leases are the building.
An apartment building has a hundred small leases that turn over every year. A retail center or an office building has a handful of big ones, and each one can end on a single date. That's why underwriting on commercial property sets aside money for re-leasing space, holds a vacancy allowance even when every suite is full, and looks hard at what happens if the biggest tenant leaves. This workbook builds the NOI that way, shows the loan it carries on two payment structures, and lays out every lease ending during the loan.
The example property is fictional. The workbook models a loan from your inputs. It does not quote a rate or approve anything.
- Build NOI from the rent roll with vacancy, management, reserves, and re-leasing costs.
- See the loan the income carries and the loan the value allows, and which one is the limit.
- Compare an amortizing payment against interest-only on the same property.
- Check the DSCR, the debt yield, and the LTV on each structure.
- See how much base rent comes up for renewal during the loan, year by year.
- Know the largest tenant's share of the rent before anyone else asks.
Four sheets. One rent roll.
PMT, PV, FV, SUMIF, and SUMPRODUCT. Data validation on the inputs. Opens in Excel, Google Sheets, or Numbers. No code runs when you open it.
From the rent roll to the loan in four steps.
- Inputs sheet
Enter the property
Property type, total square feet, and what you think it's worth today. Enter the year the loan would close, so the rollover lines up.
- Inputs sheet
Enter the rent roll
One row per suite: tenant, square feet, annual base rent, and the year the lease ends. Name a vacant suite "Vacant" and enter what it would rent for. The sheet checks that your suites add up to the building.
- Inputs sheet
Enter income and expenses
Expense reimbursements are what tenants pay back for taxes, insurance, and CAM. Operating expenses are what the property pays, not counting management. Vacancy starts at 10%, management at 4%, reserves at $0.20 a foot, and re-leasing costs at $1.00 a foot, because that's where underwriting usually starts on a multi-tenant property. Change them if your property is different.
- Summary, Loan, and Rollover
Read the answer
Summary gives the answer on one page. Loan shows the work. Rollover shows every lease that ends during the loan.
Rent roll in, loan out.
The workbook adds up base rent on every suite, takes out the higher of the actual vacancy and the minimum vacancy allowance, and adds back expense reimbursements. Then it subtracts operating expenses, management, replacement reserves, and money set aside for tenant improvements and leasing commissions. What's left is NOI. NOI divided by the minimum DSCR is the most the property can pay each year, and the rate turns that into a loan on each structure. Each loan is then capped at the maximum LTV.
The higher of actual vacancy and Base rent × Minimum vacancy
A fully leased center still gets a vacancy allowance. A center with a big empty suite gets the real number.
NOI ÷ Loan
A second test on commercial loans. It ignores the rate and the amortization and asks how much income stands behind every dollar of loan.
The loan answers how much. The rollover answers how safe.
A center with half its rent ending in the first three years is a different loan than one with long leases, even at the same NOI. The Rollover sheet shows which one you have.
A fictional 64,000 SF retail center in Tulsa.
These are the numbers already loaded in the workbook, so you can open it and follow along.
- Square feet
- 64,000
- Suites
- 10, one vacant
- Value
- $11,800,000
- Base rent, all suites
- $1,118,500
- Expense reimbursements
- $255,000
- Operating expenses
- $290,000
- Rate and amortization
- 7.25%, 25 years
- Minimum DSCR and maximum LTV
- 1.25x, 75%
- Current loan payoff
- $5,400,000
- Loan term
- 7 years
- NOI
- $844,384
- Amortizing payment
- $56,292
- Interest-only payment
- $53,469
- Amortizing DSCR
- 1.25x
- Interest-only DSCR
- 1.32x
- Debt yield
- 10.84% and 9.54%
- Cash out
- $2,232,240 and $3,273,000
- Largest tenant's share
- 38.1%
| Rate | Amortizing loan | Interest-only loan | Interest-only adds |
|---|---|---|---|
| 6.25% | $8,533,000 | $8,850,000 | $317,000 |
| 6.75% | $8,147,000 | $8,850,000 | $703,000 |
| 7.25%Your rate | $7,788,000 | $8,850,000 | $1,062,000 |
| 7.75% | $7,452,000 | $8,716,000 | $1,264,000 |
| 8.25% | $7,139,000 | $8,187,000 | $1,048,000 |
The center is 95% leased, but the 10% vacancy allowance still applies, so underwriting counts $111,850 of vacancy instead of the $54,000 suite that's actually empty. After management, reserves, and $64,000 a year for re-leasing, NOI is $844,384. On a 25 year payment, that carries $7,788,000. On interest-only, the income could carry more than the 75% value cap, so the loan stops at $8,850,000. That's about $1.04 million more in cash out after the $5.4 million payoff. The rollover is the part to watch: 61.9% of the occupied base rent ends inside the seven year term, and the grocery anchor, 38.1% of the rent, ends in year eight, just after the loan does.
Two numbers on the Rollover sheet matter most.
Look for "Share of occupied base rent" and "Largest tenant's share of base rent."
Renewals are the story.
Expect questions about each tenant's plans, how long they've been there, and what the space would re-lease for. Extensions signed before you apply help more than anything else.
That tenant is the loan.
When one tenant pays a third or more of the rent, the strength of that tenant and the date their lease ends carry as much weight as the NOI. A lease that runs past the loan term is the best answer.
The shaded rows on the Rollover sheet are the years inside the loan term. Change the loan term on the Inputs sheet and the shading moves.
Common errors and fixes.
The note says the square feet don't match.
A suite is missing from the rent roll, or the building square feet is off. Add the suite, or enter it as "Vacant" with market rent.
NOI is lower than the one on your operating statement.
Check the vacancy allowance, management, reserves, and re-leasing costs. Those four lines are usually the difference between an owner's NOI and the one underwriting uses.
A lease doesn't show on the Rollover sheet.
The lease end year is blank, or it's more than twelve years out. Month-to-month tenants go in as the year after closing.
What it covers, and what it does not.
- Multi-tenant and single-tenant retail, office, medical office, industrial, and mixed use.
- Up to 16 suites, a vacancy floor, reserves, and re-leasing costs.
- One fixed rate, an amortizing payment and interest-only, a coverage test, and a maximum LTV.
Owners use it before a refinance to see the loan the rent roll carries and which leases will draw questions. Owners with a big lease ending soon use it to see what an extension does to the picture.
Buyers use it to test a rent roll before they settle on a price.
When you want real terms, send the numbers. Capituro will review the request and come back with both structures side by side.
Your working copy.
Review the inputs, formulas, and limits before adapting it. Keep the original and check the formulas after every change.
- Direct download
- No account
- Editable file
- Opens in Sheets and Numbers
Where this leads next
Commercial DSCR
Long-term loans on retail, office, industrial, and mixed use.
OpenInterest-Only Commercial Loans
Interest-only for the full term on stabilized commercial property.
OpenNNN and Net Lease Loans
Single-tenant net lease properties.
OpenInterest-Only vs 30 Year Calculator
The same comparison for apartment buildings.
OpenFrequently asked
How much can I borrow on a commercial property?+
Why does underwriting use a vacancy allowance when my property is full?+
What are TI/LC reserves?+
What is debt yield?+
Does this work for a single-tenant property?+
See your property with a real rate.
Send the numbers. We'll come back with the amortizing and interest-only structures side by side, at a rate that reflects the property and the sponsor.