The Multifamily Operating Expense Ratio Calculator in Excel That Catches a Money Pit Before You Buy It

A broker sends you an eight-unit building. The pro forma shows $96,000 in gross rents, $28,000 in expenses, and a 7.2% cap rate at the $860,000 asking price. On paper it cash flows beautifully. You run the numbers, you like them, and if you buy on those numbers you will spend the next three years wondering where your money went. The problem is hiding in one figure the broker did not label clearly. At $28,000 of expenses on $96,000 of income, the pro forma is claiming an operating expense ratio of 29%. Real multifamily buildings do not run at 29%. A multifamily operating expense ratio calculator in Excel is how you catch that in ten minutes instead of at your first property tax bill.

The operating expense ratio is the fastest way to tell whether a rental deal is what the seller says it is. It compresses the entire operating budget into one number you can benchmark against thousands of other buildings. When that number sits far below normal, you are not looking at a well-run property. You are looking at a pro forma with expenses left out. This article builds the calculator, shows you the benchmark it has to clear, and names the specific costs sellers quietly delete to make a money pit look profitable.

What the Operating Expense Ratio Actually Measures

The operating expense ratio, or OER, is total operating expenses divided by effective gross income. Effective gross income is your gross potential rent minus vacancy and credit loss, plus other income like laundry, parking, and pet fees. Operating expenses are everything it costs to run the building: property taxes, insurance, management, repairs, maintenance, the utilities you pay, landscaping, trash, and reserves for big replacements.

Two things are deliberately excluded, and this is where new investors go wrong. Operating expenses do not include your mortgage payment, and they do not include depreciation. Debt service is a financing decision, not a property cost, so it lives below the net operating income line. Depreciation is a tax entry, not a cash outflow. Leave both out. If you fold your mortgage into the ratio, you are measuring your loan, not the building, and the result compares to nothing.

The formula itself is trivial. The discipline is in what you feed it. If your income cell is =B6-B7+B8, meaning gross rent minus vacancy plus other income, and your expense total is =SUM(B12:B23), then the ratio is simply =B25/B9, formatted as a percentage. One division. The entire value of the model is whether rows 12 through 23 are honest.

The 50 Percent Rule Is a Lie Detector, Not a Budget

Every experienced multifamily investor carries one number in their head: operating expenses run about half of gross income. That is the 50 percent rule. Across a full year, for a typical small-to-mid multifamily building, roughly 50 cents of every rent dollar goes to operating costs before the mortgage gets paid.

The rule is not a budget. You do not use it to plan the year. You use it as a screen. When a seller hands you a pro forma claiming a 29% OER, the 50 percent rule tells you instantly that something is missing, because well-run buildings land in the 35 to 45% range and older small buildings run higher than that, not lower. A ratio in the twenties is not a great deal. It is a warning that reassessed taxes are not in the numbers, or management is not in the numbers, or nobody set aside a single dollar for the roof.

Here is the benchmark to keep next to the calculator. Compare your computed OER against the row that matches the building, and treat anything meaningfully below the range as a claim you have to prove, not a bargain you get to keep.

The 50 percent rule gets you to a fast yes-or-no on whether to keep reading a listing. The calculator is what you build once the listing survives the rule, so you can see exactly which line is off.

Building the Calculator in Excel

Lay the sheet out in three blocks: an income block at the top, an expense block in the middle, and the ratio and flags at the bottom. Enter the numbers from the seller's actual statements, not the pro forma, and the sheet does the rest.

The income block is short. Gross potential rent is what the building collects at full occupancy. Vacancy and credit loss is what you lose to empty units and non-payment, never zero, and 5 to 8% is a realistic floor even on a full building. Other income is laundry, parking, storage, and fees.

The expense block is where deals live or die. List every category on its own row so nothing hides inside a lump sum. The two lines investors most often skip are professional management and capital reserves, and those two are exactly what turns a fake 29% into a real 48%.

Now the payoff cells. The operating expense ratio is =B23/B9, which on these numbers is 48.9%, not the 29% the broker showed. Add a benchmark flag so the sheet screams when a deal fails the 50 percent screen: =IF(B25>0.5,"HIGH, verify or renegotiate",IF(B25<0.35,"LOW, expenses likely understated","IN RANGE")). That single formula turns a spreadsheet into a filter you can run across ten listings in an afternoon.

Two more numbers finish the model. Net operating income is =B9-B23, and that is the figure a building actually sells on. Divide NOI by the asking price with =B26/B30 to get the true cap rate. On the honest expenses, this deal's NOI is $47,450 and the real cap rate at $860,000 is 5.5%, not 7.2%. The 1.7 points of cap rate the seller invented is worth real money, which is the next section.

The Expenses Sellers Forget, and What They Cost You

Understated expenses are not a rounding error. They are a valuation error, because you buy income property on a multiple of NOI. Overstate NOI by leaving out costs and you overpay by that overstatement divided by the cap rate. Here is the same building side by side, the seller's pro forma against the numbers the calculator forces you to use.

The NOI gap is $20,550. At a market cap rate of 6.5%, that gap is worth =20550/0.065, about $316,000 of value. The seller is asking $860,000 for a building that, underwritten honestly, is worth closer to $730,000 at the same cap rate. If you had trusted the pro forma, you would have overpaid by six figures and financed the mistake for thirty years.

Three lines cause almost all of it, and the calculator catches all three. Property taxes on the seller's old assessed value instead of your purchase price. Management priced at zero because the current owner self-manages, which tells you nothing about what the building costs to run when you hire it out or value your own hours. And capital reserves set to nothing, as if the roof installed in 2004 will last forever. Force each of those onto its own row and the fake ratio cannot survive.

What to Do With the Number Once You Have It

An OER calculator does three jobs. It screens listings fast so you stop wasting weekends on deals that fail the 50 percent rule. It arms your offer, because a 49% real OER against a 29% claimed one is a written, defensible reason to negotiate the price down to the value the honest NOI supports. And it protects you after closing, because the same sheet becomes your annual budget benchmark, telling you the month your real building starts drifting above its range so you fix a leak before it becomes a trend.

Build the shell once and you can run any multifamily deal through it. The trap is not the math, which is one division. The trap is the blank rows, the expenses a seller left off the page and hoped you would too. A calculator with every line item pre-built, benchmark ranges wired in, and the 50 percent flag firing automatically is what keeps a good-looking money pit from becoming your money pit.

If you would rather not wire the vacancy math, the reserve formulas, the reassessed-tax logic, and the OER flags from a blank sheet on every deal, the SheetCraft Rental Property Analyzer ships with the full expense schedule, the operating expense ratio benchmark, and true cap rate already built in. Drop in the seller's statements, watch the ratio land next to the range it should be in, and know within minutes whether the deal is real or just dressed up to look that way.