Rental Property Cash Flow Spreadsheet: Monthly NOI and Cash Flow per Property (Excel & Google Sheets)

Short on time? The Rental Property Tracker ($19) is the finished, tested version of what this tutorial builds.

Most landlords know their rent. Far fewer know, month by month, what each property actually puts in their pocket after repairs, insurance, taxes and the mortgage. A property can look fine in January and quietly lose money over the year.

This tutorial builds a small cash-flow sheet that answers that question for every property, using four standard formulas. It works the same in Excel, Google Sheets and LibreOffice. We tested every formula below by recalculating a sample workbook and comparing it to the numbers in the worked example.

The two numbers that matter

• NOI (net operating income) = income minus operating expenses (repairs, insurance, property tax, utilities, management). The mortgage is not an operating expense. NOI tells you how good the property is, regardless of how you financed it.
• Cash flow = NOI minus debt service (your full mortgage payment, principal and interest). This is what actually lands in your bank account.

Capital improvements (a new roof, a furnace) are neither. Keep them out of both numbers and track them separately, because they are depreciated for taxes rather than expensed.

1. A category table with a Type column

Make a tab called Categories. Every category gets exactly one type: Income, Operating, Debt or Capital.

Deciding the type once, in this table, means you never have to think about it again when you log a transaction.

2. The transactions tab

One row per payment in or out, amounts always positive (the type decides whether it adds or subtracts):

The two formula columns:

E2:  =IFERROR(VLOOKUP(C2, Categories!$A$2:$B$50, 2, FALSE), "?")
F2:  =DATE(YEAR(A2), MONTH(A2), 1)

Fill both down. Column E looks up the type, and shows ? for a misspelled category so you can spot it immediately. Column F turns any date into the first day of its month, which is what the monthly summary matches on. Format column F as mmm yyyy.

If column F shows #VALUE!, the date in column A was typed or pasted as text. Re-enter it as a real date (in Google Sheets, Format → Number → Date). Text dates are the number one reason a SUMIFS total comes out too low.

3. The monthly cash-flow table

On a Cash Flow tab, type the property name in B1. Put the months down column A starting in A4, and the five headers in row 3: Income, Operating, NOI, Debt service, Cash flow.

A4:  =DATE(2026, 1, 1)
A5:  =EDATE(A4, 1)

Fill A5 down to A15 to get all twelve months. Then the five columns:

B4:  =SUMIFS(Transactions!$D$2:$D$2000, Transactions!$B$2:$B$2000, $B$1, Transactions!$F$2:$F$2000, $A4, Transactions!$E$2:$E$2000, "Income")
C4:  =SUMIFS(Transactions!$D$2:$D$2000, Transactions!$B$2:$B$2000, $B$1, Transactions!$F$2:$F$2000, $A4, Transactions!$E$2:$E$2000, "Operating")
D4:  =B4-C4
E4:  =SUMIFS(Transactions!$D$2:$D$2000, Transactions!$B$2:$B$2000, $B$1, Transactions!$F$2:$F$2000, $A4, Transactions!$E$2:$E$2000, "Debt")
F4:  =D4-E4

Fill row 4 down to row 15. The $B$1 is fixed so every row reads the same property; $A4 keeps column A fixed so each row reads its own month. To see another property, just change the name in B1 (or copy the tab once per property).

Add a totals row and the expense ratio:

B16: =SUM(B4:B15)
B17: =IF(B16=0, 0, C16/B16)

Copy B16 across to F16. B17 is the operating expense ratio: operating expenses as a share of income. Format it as a percentage.

Worked example

Sample transactions for Maple St in the first quarter of 2026: rent of $1,450 every month, a $50 late fee in February, a $220 repair in February, the $900 annual insurance premium in March, a $980 mortgage payment every month, and a $7,800 new roof in March. The sheet also holds a $1,600 rent payment for Oak Ave, which must not show up in Maple St's numbers.

Operating expense ratio for the quarter: 1,120 ÷ 4,400 = 25.5%.

Three things this example shows:

1. March is negative because the annual insurance premium landed in one month. That's normal, and it's exactly why a monthly view beats a glance at the bank balance.
2. The $7,800 roof is not in any column. It's typed as Capital, so it doesn't distort NOI or cash flow. Your preparer will depreciate it.
3. Oak Ave's rent is ignored because the property filter in $B$1 excludes it.

Common mistakes

• Putting the mortgage in operating expenses. Your NOI will look terrible and won't be comparable to cap rates you see in listings. Keep debt separate.
• Negative amounts for expenses. With this setup every amount is positive; the Type column decides the sign. Mixing signs double-subtracts.
• Forgetting irregular bills. Property tax, insurance and HOA special assessments are lumpy. Look at the full-year total, not one month.
• Ignoring vacancy and capital reserves. A common rule of thumb is to set aside a few percent of rent each for vacancy and future capital items. Our free rental property calculator lets you model both before you buy.

Related tutorials

• How to track rental income and expenses by Schedule E line: the same transactions tab, summarized for taxes.
• Build a rent roll that flags late rent automatically.
• Cap rate vs. cash-on-cash vs. DSCR: a worked rental example.

Want it done for you?

The Rental Property Tracker has this built in for up to 10 properties: a filterable dashboard with monthly income vs. expenses, cash flow and expense ratio, plus a rent roll with late-rent flags, a deposit ledger, a mileage log and a Schedule E summary. It works in Excel and Google Sheets with no macros.

Not tax advice. Confirm treatment of specific items with the IRS instructions or your tax professional.

Free tool: Free Rental Property Calculator: Cash Flow, Cap Rate, Cash-on-Cash, DSCR

Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.