Retail center underwriting template in Excel.
Anchors, shop space, CAM recoveries, and co-tenancy, the way a retail center actually gets underwritten. See the NOI, the loan, and what happens if the anchor goes dark.
A retail center isn't one rent, it's a rent roll.
On an apartment building, the NOI is mostly rent less expenses. On a retail center, half the story is who pays the expenses back. NNN tenants pay their share of taxes, insurance, and CAM, modified gross tenants pay part of it, and empty suites pay nothing, so the owner covers their share. Then there's the anchor: the tenant that pays the most rent, draws the traffic, and often has the other tenants' leases tied to it through co-tenancy clauses. This template underwrites all of it, suite by suite.
The example center is fictional. The workbook models a loan from your inputs. It does not quote a rate or approve anything.
- Work out each tenant's share of taxes, insurance, and CAM from its square feet and lease type.
- See how much of those expenses the owner carries for vacant suites and gross leases.
- Apply a separate vacancy allowance to anchors and shop space, the way underwriting does.
- See the loan on an amortizing payment and on interest-only, with DSCR and debt yield.
- Check the anchors' share of rent, the national tenants' share, and the weighted average lease term.
- Take out any tenant and see whether the center still covers the payment.
Five sheets. Every suite.
PMT, PV, SUMIF, SUMIFS, SUMPRODUCT, INDEX, and MATCH. Drop-down lists on the rent roll. Opens in Excel, Google Sheets, or Numbers. No code runs when you open it.
From the rent roll to the loan in four steps.
- Rent Roll sheet
Enter every suite
Tenant, type (anchor, junior anchor, inline, or pad), credit, square feet, rent per foot, lease type, the year the lease ends, and whether it has a co-tenancy clause. Name an empty suite "Vacant" and enter what it would rent for. Sales are optional, but they unlock occupancy cost and sales per foot.
- Inputs sheet
Enter the expense pools
A year of real estate taxes, insurance, and CAM, plus the admin fee your NNN leases allow on CAM. Then the expenses tenants don't pay back, like roof repairs and legal.
- Inputs sheet
Check the underwriting and enter the loan
Vacancy starts at 3% for anchors and 10% for shop space, with TI/LC at $0.50 a foot for anchors and $1.50 for shop space. The rate is yours to fill. The example uses 7.25% so the formulas work, but it is not a quote.
- Summary, Underwriting, and Anchor Dark
Read the answer
Summary gives the answer on one page. Underwriting shows the work. Anchor Dark shows how much room the center has.
Recoveries in, vacancy out.
Each suite's share of the center is its square feet divided by the center's total. NNN tenants pay that share of taxes, insurance, and CAM plus the admin fee. Modified gross tenants pay their share of taxes and insurance. Gross tenants and vacant suites pay nothing. Base rent plus recoveries is potential gross income. Vacancy comes out separately for anchors and shop space, using the higher of the actual vacancy and the allowance. Then come the expenses, management, reserves, and TI/LC, and what's left is NOI.
Recoveries ÷ (Taxes + Insurance + CAM)
How much of the center's operating costs the tenants pay back. The gap is what vacant suites and gross leases cost the owner every year.
(Base rent + Recoveries) ÷ Sales
How much of a tenant's sales go to the space. Above about 12% to 15%, most tenants are stretched, and the sheet marks them in red.
The loan is set by the NOI. The anchor decides how safe it is.
Two centers with the same NOI can carry very different risk. The Anchor Dark sheet shows what happens to yours if the biggest tenant leaves and the co-tenancy clauses kick in.
A fictional 86,500 SF grocery-anchored center in Chattanooga.
These are the numbers already loaded in the workbook, so you can open it and follow along.
- Square feet
- 86,500
- Suites
- 14, two vacant
- Anchors
- 45,000 SF grocery, 12,000 SF discount apparel
- Value
- $13,900,000
- Taxes, insurance, CAM
- $210,000, $95,000, $185,000
- Rate and amortization
- 7.25%, 25 years
- Minimum DSCR and maximum LTV
- 1.25x, 75%
- Current loan payoff
- $6,900,000
- Base rent
- $1,337,700
- Recoveries
- $454,291
- Shop vacancy
- $104,000
- Owner carries
- $35,709
- Anchors' share of rent
- 53.3%
- National tenants' share
- 66.3%
- Weighted average lease term
- 6.9 years
- Cash out
- $2,019,960 and $3,316,500
| Amortizing | Interest-only | |
|---|---|---|
| DSCR as underwritten | 1.25x | 1.31x |
| DSCR, anchor dark | 0.18x | 0.19x |
| Cash flow per year, anchor dark | −$646,422 | −$612,756 |
The center is 94% leased and the tenants pay back 92.7% of taxes, insurance, and CAM, so NOI holds up well at $986,850. On a 25 year payment, that carries $9,102,000. On interest-only, the loan reaches the 75% value cap at $10,425,000, about $1.3 million more in cash out. The grocery is the whole story, though. It pays 40% of the base rent, its share of the expenses goes back to the owner if it leaves, and six tenants have co-tenancy clauses tied to it. If it went dark, NOI would fall to about $143,000. The good news is its lease runs to 2036, past the seven year loan term, and that's exactly what underwriting will want to see.
Three numbers tell the story.
Look at the recovery ratio, the anchors' share of rent, and the DSCR with the anchor dark.
The owner is carrying the center.
Vacant suites and gross leases are leaving taxes, insurance, and CAM with the owner. Every suite that leases on NNN terms adds its rent and its share of the expenses to NOI.
The anchor is the loan.
Almost every grocery-anchored center fails this test, so the number alone isn't the problem. What matters is how long the anchor's lease runs past the loan term, its credit, and its sales.
Run the Anchor Dark sheet on the junior anchor too. A center that holds up when the second biggest tenant leaves has a lot of room.
Common errors and fixes.
The Anchor Dark sheet says there's no tenant by that name.
The name on the Inputs sheet has to match the Rent Roll exactly, spaces included. Copy it from the Rent Roll to be safe.
Recoveries look too low.
Check the lease type on each suite. A tenant marked Gross pays nothing back. Also check that the CAM admin fee matches what your leases allow.
NOI is lower than the one on your operating statement.
Check the vacancy allowances, management, reserves, and TI/LC. Those lines are usually the difference between an owner's NOI and the one underwriting uses.
What it covers, and what it does not.
- Strip centers, neighborhood and community shopping centers, and grocery-anchored centers, up to 20 suites.
- NNN, modified gross, and gross leases, with recoveries by each suite's share of the center.
- Separate vacancy and TI/LC for anchors and shop space, two payment structures, and an anchor-dark stress test.
Owners use it before a refinance to see the loan the rent roll carries and to know which tenants will draw questions. Owners with a vacant suite use it to see what a new NNN lease adds to NOI.
Buyers use it to test a center's rent roll before they settle on a price.
When you want real terms, send the numbers. Capituro will review the center 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
Retail Property Loans
Long-term loans on strip centers and shopping centers.
OpenInterest-Only Commercial Loans
Interest-only for the full term on stabilized commercial property.
OpenNNN and Net Lease Loans
Single-tenant retail on a net lease.
OpenCommercial DSCR
Long-term loans on retail, office, industrial, and mixed use.
OpenFrequently asked
What does a retail center underwriting template include?+
How are CAM recoveries calculated on a retail center?+
What is a co-tenancy clause?+
What is a good occupancy cost for a retail tenant?+
How much can I borrow on a retail center?+
See your center 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 center and the sponsor.