What formula do you use for NOI in Excel?

There is no NOI function. You need three subtractions. Vacancy is =B3*B4, effective gross income is =B3-B5, and NOI is =B6-B13, where B13 sums the operating expense rows. On the default property that chain runs $48,000 of scheduled rent down to $45,600 of collected income and $28,892 of NOI. Keep every expense positive and subtract the total once.

Can you get net operating income out of QuickBooks?

Not directly. Run a Profit and Loss for the one property, then take out mortgage interest, mortgage principal, depreciation, income taxes, capital improvements and owner draws. Then add the two lines QuickBooks never has: a management fee if you manage the place yourself, and a capital reserve. Neither is money that left the account, and both belong in the statement.

Should an NOI spreadsheet be monthly or annual?

Both, in one sheet. Put twelve month columns to the right of the labels and an annual column at the far end. Formulas live in rows, so each month calculates on its own. Lenders and appraisers work in annual figures, while twelve columns show seasonal repairs and the months a unit sat empty, which an annual total hides.

What formula mistake inflates NOI the most?

Leaving out property management and capital reserves. Drop both from the default property and NOI jumps from $28,892 to $34,820, about 20% too high, which is roughly $91,000 of value it does not have at a 6.5% cap rate. Second is a missing vacancy row, worth another $1,968 of NOI that nobody will ever collect.

Net Operating Income Spreadsheet | Rows and Formulas

A net operating income spreadsheet is about twenty rows and six formulas. Excel has no NOI function and there is nothing to install, so a net operating income template is really just a row order plus what each formula points at. Here is that layout, the formulas with cell references, twelve monthly columns beside an annual total, and the habits that quietly inflate NOI. For the reasoning behind each line, read the five steps behind the NOI formula.

All figures use the site’s default property: a $500,000 house renting for $4,000 a month, 5% vacancy, $6,000 of taxes, $2,500 of insurance, and 18% of collected income for maintenance, reserves and management. It ends at $28,892 of NOI, or $2,407.67 a month.

The rows every NOI sheet needs

Labels in column A, annual numbers in column B. Every expense stays a positive number, subtracted once at the bottom.

Row	Label	Value	Formula
1	Gross scheduled rent	$48,000	typed
2	Other income	$0	typed
3	Gross scheduled income	$48,000	`=SUM(B1:B2)`
4	Vacancy rate	5%	typed
5	Vacancy and credit loss	$2,400	`=B3*B4`
6	Effective gross income	$45,600	`=B3-B5`
7	Property taxes	$6,000	typed
8	Insurance	$2,500	typed
9	HOA, utilities, other	$0	typed
10	Maintenance and repairs	$2,280	`=B6*0.05`
11	Capital reserves	$2,280	`=B6*0.05`
12	Property management	$3,648	`=B6*0.08`
13	Total operating expenses	$16,708	`=SUM(B7:B12)`
14	Net operating income	$28,892	`=B6-B13`
15	Monthly NOI	$2,407.67	`=B14/12`
16	Operating expense ratio	36.6%	`=B13/B6`
17	Purchase price	$500,000	typed
18	Cap rate	5.78%	`=B14/B17`

Three notes on those rows. Row 1 is gross scheduled income at full occupancy, not last year’s collections. Rows 7 to 12 hold only operating expenses, the cost of running the property. The percentages in rows 10 to 12 are common defaults, and average operating expenses for a rental property move with building age and market, so use real quotes where you have them.

Formulas for vacancy, effective gross income and NOI

Five formulas carry the sheet and a sixth turns it into a cap rate. The one that gets anchored wrong is the percentage group. Maintenance, reserves and management are charged against collected income, so they point at B6. Point them at B3 instead and the sheet charges 18% of $48,000, which is $8,640 rather than $8,208, and NOI reads $432 low.

If all you have is top line numbers, three rows will do: gross income $50,000, vacancy 5%, operating expenses $17,500. Effective gross income is $47,500 and NOI is $30,000. That is enough for a first pass on a listing.

Monthly columns with an annual total

January in column C through December in column N. Leave column O blank and put the annual column in P. Row formulas copy sideways: C6 is =C3-C5, D6 is =D3-D5, and so on. In column P, sum only the typed rows, so P1 is =SUM(C1:N1) and never =SUM(C1:P1), which counts the year twice. Let the calculated rows in P use the same formulas as the months, so P6 is =P3-P5 and P14 is =P6-P13.

That gives you a free check. A cell holding =P14-SUM(C14:N14) should read zero. The day it does not, a range is broken or a month is missing a formula. Keep rates in their own cells too, say B21 for the management fee, and point at them with =C6*$B$21 so one edit updates twelve months. Taxes and insurance arrive as one or two bills a year, so spread them at a twelfth a month or single months will swing for no real reason.

Pulling the numbers out of QuickBooks or a property manager report

QuickBooks will not print an NOI line, because NOI is not an accounting output. Run a Profit and Loss for the property, tagging transactions by class or customer so one property comes out alone, then edit the report in both directions.

Take out mortgage interest, mortgage principal, depreciation, income taxes, capital improvements and owner draws. Interest and depreciation catch people, since both sit in the expense section of a P&L and neither belongs here; what NOI leaves out has the list. Property tax stays, income tax goes, and the before or after taxes question covers the difference.

Put in a management fee of 8 to 10% of collected income even if you manage the property yourself, plus a capital reserve. Neither is in QuickBooks, because neither is money that left the account, and both are in the statement a lender or buyer will build. On accrual books, billed rent that was never paid belongs in row 5 as credit loss, not in the expense rows.

A property manager’s owner statement is closer, but its bottom line is a disbursement: rent collected, minus the fee, repairs and anything else paid out, minus money held back. Use the gross rent, fee and repair lines, then rebuild the rest yourself.

Common formula mistakes that inflate NOI

No management fee and no reserve. Drop both and NOI goes from $28,892 to $34,820, about 20% high, which is roughly $91,000 of value the property does not have at a 6.5% cap rate.
No vacancy row. Working from $48,000 of rent instead of $45,600 adds $1,968 of NOI.
Mixed signs. Type expenses as negatives and leave =B6-B13 alone, and the sheet reports $62,308 of NOI on a property that collects $45,600.
A total that stopped growing. Add an expense line below the total and =SUM(B7:B12) ignores it. Use an Excel table instead.
Last year’s tax bill. Counties reassess on sale, so a seller’s figure is often low.

One error runs the other way. A $12,000 roof replacement typed into repairs is a capital expenditure that the reserve line already covers, and leaving it there drops NOI to $16,892 for the year.

When a spreadsheet beats a calculator

A sheet wins when there are several units and you want a row each, when you have twelve months of real statements, when you need three versions of one deal (yours, the lender’s, a stress case), or when you are sending something a lender will read.

A calculator wins for a first look at a listing, for holding the same assumptions across ten properties, and for auditing a sheet you already built. Most wrong NOI figures come from a formula rather than from judgment, and a second tool catches those.

Check your sheet against the NOI Calculator

Run your inputs through the NOI Calculator and compare. It shows every intermediate line in the same order: gross scheduled income, vacancy, effective gross income, each expense, NOI, monthly NOI, the expense ratio and the cap rate. Those lines are your rows, and its totals are your check figures.

Net Operating Income Spreadsheet: How to Lay Out an NOI Statement in Excel