Cellwise Start free

Excel guide · 3 min read

How to calculate break-even occupancy in Excel

Quick answerBreak-even occupancy = (operating expenses + annual debt service) / gross potential rent. In Excel, with yearly expenses in D2, debt service in J2 and gross potential rent in I2: =(D2+J2)/I2, formatted as a percentage. Oak Court needs about 69.7% of its potential rent to cover its costs and loan payments.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHIJKL
1PropertyUnitsAvg rent / monthOperating expenses / yrLoanRateYearsCurrent occupancyGross potential rentDebt service / yrBreak-even occupancy
2Oak Court1214506200011000000.065300.9520880083432.9791010.69651809914
3Birch Flats81250410006500000.0675250.87512000053891.0989670.79075915805
4Cedar Row2011009800016000000.07300.9264000127738.079070.85506848134

Download this sheet as an Excel practice file (.xlsx)

Step 1: Gross potential rent

The sheet lists three rental properties: units, average monthly rent, yearly operating expenses and the loan on each. Gross potential rent is what the property would collect in a year with every unit rented at its current rent: =B2*C2*12 in I2. Oak Court: 12 x $1,450 x 12 = $208,800.

If you have other regular income such as parking or laundry, some people add it to the bottom of the division. Decide once and do the same for every property, or the percentages won't compare.

=B2*C2*12

Result: 208800 shows as $208,800 with Currency format

208800: Oak Court's rent for a year at full occupancy.

Step 2: Yearly debt service with PMT

Debt service is the loan payment, principal and interest, for a year. PMT works it out from the rate, the number of payments and the loan amount: =-PMT(F2/12,G2*12,E2)*12 in J2. F2/12 is the monthly rate and G2*12 the number of monthly payments.

PMT returns a negative number because it's money going out; the minus sign in front makes it positive. Oak Court's $1,100,000 loan at 6.5% over 30 years costs about $83,433 a year. If you already know the monthly payment from the loan statement, use that x 12 instead, since the real loan may differ from a textbook one.

=-PMT(F2/12,G2*12,E2)*12

Result: 83432.979101 shows as $83,433 with Currency format

About 83433: Oak Court's loan payments for a year.

Step 3: Break-even occupancy and the cushion

Add expenses and debt service and divide by gross potential rent: =(D2+J2)/I2 in K2, copied down. Oak Court 69.65%, Birch Flats 79.08%, Cedar Row 85.51%.

Compare it with current occupancy in H. The gap is your cushion: =H4-K4 for Cedar Row is about 0.045, so it's 4.5 percentage points above break-even. Oak Court has about 25.3 points, Birch Flats about 8.4. Cedar Row is the one where losing a single tenant matters.

Occupancy here is rent-based: the share of potential rent you collect. It matches unit occupancy only when units rent for about the same. If one unit is a large penthouse, an empty penthouse costs you more than an empty studio.

=(D4+J4)/I4

Result: 0.85506848134 shows as 85.5% with Percent format

About 0.855: Cedar Row must collect 85.5% of its potential rent to cover costs and the loan.

How many units must stay rented

A percentage is hard to act on. Multiply by the unit count and round up, because you can't rent part of a unit: =ROUNDUP(K4*B4,0). Cedar Row needs 18 of its 20 units rented; Oak Court 9 of 12; Birch Flats 7 of 8.

Always ROUNDUP, not ROUND. 17.1 units rounds to 17 with ROUND, and 17 units leaves you short.

=ROUNDUP(K4*B4,0)

Result: 18

18: units Cedar Row must keep rented to break even.

What-if: a rent increase or a higher rate

Put the assumptions in input cells. With a 3% rent change in N2, Cedar Row's break-even is =(D4+J4)/(I4*(1+N2)), about 83.0%. If the loan resets to the rate in N3 (7.75%), it becomes =(D4-PMT(N3/12,G4*12,E4)*12)/I4, about 89.2%, leaving less than one point of room at 90% occupancy.

Expenses usually rise over time too, so check the expense line as well. These are ways to test your own numbers, not investment advice: a lender's or adviser's view of a property also depends on reserves, capital repairs and the loan terms.

=(D4-PMT(N3/12,G4*12,E4)*12)/I4

Result: 0.89223920889 shows as 89.2% with Percent format

About 0.892: Cedar Row's break-even occupancy if its rate goes to 7.75%.

Common mistakes

Leaving out debt service

Expenses / rent alone is the operating break-even. With a loan, add a year of payments, or you'll think you're safe when you aren't.

Using monthly figures on one side and yearly on the other

Make every input yearly: rent x 12, payment x 12, expenses for the year.

Rounding the units down

Use ROUNDUP. Any part of a unit means one more unit must be rented.

Using PMT's negative result as-is

Put a minus in front of PMT so debt service is a positive cost.

FAQ

What is the break-even occupancy formula?

(Operating expenses + annual debt service) / gross potential rent. In Excel: =(D2+J2)/I2, with debt service from =-PMT(rate/12,years*12,loan)*12.

What is a good break-even occupancy?

Lower is safer, because it leaves more room between break-even and actual occupancy. What counts as acceptable depends on the market and the lender; compare the cushion across your own properties.

Does break-even occupancy include property taxes and insurance?

Yes, they're operating expenses. Include everything you pay to run the property in the expense figure, then add debt service.

Is break-even occupancy the same as break-even ratio?

They're used for the same calculation: costs plus debt service divided by gross potential income. Some people divide by gross operating income instead, so check which one a report uses.

Functions used

Related guides

Updated