Rental Property Spreadsheet: NOI, Cash Flow and Cap Rate

Three numbers

What a rental property spreadsheet has to answer

A rental property spreadsheet usually gets asked three different questions at once. Is this a good property? Will it put money in my pocket? And what will it do to my taxes? Each question has its own number, and most of the confusion around rental analysis comes from reading one of them as if it answered another.

The example at the top of this page is a fictional duplex bought for $320,000 with 25 percent down. We use it all the way through so every figure can be checked against the sheet. If you manage more than one unit, the rent roll template guide covers the unit-by-unit view this analysis starts from.

The property

Net operating income: what the property earns

Start with the rent the units could bring in over a year: two units at $1,450 a month is $34,800. Take off an allowance for vacancy, because no property is let every day of every year; at 5 percent that is $1,740, leaving $33,060 of effective rent.

Then subtract the costs of running the building: property tax, insurance, repairs, any utilities the owner pays, and a reserve for the big items like a roof or a boiler that arrive every few years. In the example these come to $10,440. What is left, $22,620, is net operating income.

Notice what is not in that list: the mortgage. Net operating income describes the property, not the way you paid for it, so two buyers with different loans get the same figure. That is what makes it the right number for comparing one building with another.

Cap rate

Cap rate, and what it can and cannot tell you

Divide net operating income by the price and you have the cap rate: $22,620 over $320,000, about 7.1 percent. It is a way of saying what the property would return if you bought it with cash, and it is useful for comparing buildings at different prices.

What it cannot tell you is what you will actually receive once any mortgage is paid. A cap rate is only as honest as the operating costs behind it, too: leave out the reserve or the vacancy allowance and the rate jumps, which is exactly how listings end up quoting numbers the buyer never sees.

Your pocket

Cash flow after the loan: what reaches you

If the purchase is financed, the loan payments come out next. The example assumes a $240,000 mortgage over 30 years at 6.75 percent, which works out at about $1,557 a month, or $18,680 a year. Subtract that from net operating income and the owner is left with $3,940 a year, against the full $22,620 a cash buyer would keep. That gap is the cost of the loan, and it is worth seeing in black and white before deciding to borrow. The same property that returns 7.1 percent on its price returns $3,940 on the $86,000 of cash actually put in, the down payment plus closing costs. That is a cash-on-cash return of 4.6 percent.

Our Rental Mortgage & Refinance Calculator ($29, Google Sheets and Excel) runs this step for your own rental: enter the mortgage and the rent once, and it gives the payment, how much of it the rent covers, the payoff date and the interest over the life of the loan, and what an extra payment each month saves.

If a lender is offering a lower rate on that loan, the question becomes whether the closing costs pay for themselves. Our rental property mortgage calculator guide works the refinance break-even through on a real rental, and shows why a fresh 30-year term breaks even fastest and saves the least.

The same property that returns 7.1 percent on its price returns $3,940 on the $86,000 of cash actually put in.

Work out the payment with Excel’s PMT function, which Microsoft describes as calculating “the payment for a loan based on constant payments and a constant interest rate”. Mind the units: for monthly payments, Microsoft’s own example divides the annual rate by 12 and multiplies the years by 12. Our amortization schedule in Excel guide shows the full payment table if you want to see how much of each payment is interest.

One caveat keeps this number honest. Part of every loan payment repays principal, which you get back as equity when you sell. So cash flow after the loan understates the property’s return, and cash-on-cash is a measure of spending money rather than of the whole return.

Taxes

Depreciation: real on the tax return, not in the bank

The IRS lets you deduct the cost of a rental building over time. Its Publication 527 explains that “you recover the cost of income-producing property through yearly tax deductions”, lists a 27.5-year recovery period for residential rental property under the general depreciation system, and states that “you can’t depreciate the cost of land because land generally doesn’t wear out, become obsolete, or get used up.”

So the sheet splits the price into land and building first. Assuming $64,000 of the example’s price is land, the building’s $256,000 over 27.5 years gives $9,309 a year. That figure matters for the tax return, and it is why a property with positive cash flow can show a much smaller taxable profit. But it is not money arriving or leaving, so it sits on its own line and is never added to cash flow.

Keeping it current

From purchase analysis to a sheet you run the property on

Most people build this sheet once, before buying, and never open it again. It is more useful the other way round. Keep the same layout, add an actual column next to each estimate, and fill it from the year’s real rent and bills. Within a year you will know whether your vacancy and repair assumptions were right, which is the knowledge that makes the next purchase analysis trustworthy.

Between the analysis and the first rent payment sits the escrow, where most surprises in the numbers arrive: inspection credits, appraisal gaps, a closing date that moves. Our real estate transaction checklist walks the escrow timeline deadline by deadline.

What a property management spreadsheet adds

A purchase analysis looks at one property once. A property management spreadsheet keeps the same numbers live for every door you own, month after month. For a landlord with a handful of units it can do the tracking half of property management software without a subscription, as long as it holds five things:

• Properties: what you paid, what each is worth today and the loan left, so equity and cap rate stay current.
• A rent roll: every unit, let or empty, with tenant, lease start and end, rent, deposit and due day. Our rent roll template guide covers the columns.
• A rent ledger: each payment logged the day it arrives, against the month it pays for, so a late payment cannot hide a missed one.
• An expense log: each bill on the day it is paid, tagged to a property and a category, so the year-end figures are a sum rather than a hunt through bank statements.
• Profit and loss per property: NOI, cash flow after the loan and cap rate for any period, built from the ledger and the expense log instead of the estimates above.

Three figures turn that log into a small KPI dashboard: rent collected against rent due this month, occupancy (units let against units owned), and leases ending in the next 60 days, because a renewal settled early is a vacancy avoided. Keep security deposits on their own line. IRS Publication 527 says a deposit you plan to return is not income when you receive it; only the part you keep becomes income, in the year you keep it.

For timing within the year, when the insurance renews or the property tax falls due, the cash flow forecast template guide shows how to lay costs out by the month they are paid. Everything else we have written for landlords and agents is on the real estate hub.

When the plan is to sell rather than hold, the first document a viewing needs is a visitor record; our open house sign in sheet guide covers what to put on it.

If you only need the sheet itself, our free open house sign-in sheet is ready to copy into Google Sheets, download for Excel or print in Letter or A4.

If you would rather start from a finished file, our Landlord & Rental Property Manager ($59, Google Sheets and Excel) works out each property’s profit and loss for any period, with NOI, cash flow and cap rate, from rent and costs logged as they happen. A rent tracker colours every unit paid, part paid or unpaid across twelve months, and a What If tab says how far the rent can fall before the year turns red. It is also one of three sheets in our Landlord Spreadsheet Kit, with a cash flow forecast and a rental mortgage and refinance calculator: all three for $109, against $147 bought one by one.

A rental property spreadsheet that keeps these three numbers apart will talk you out of some deals and into better ones. Tell us in the comments which assumption surprised you most once you owned a property, vacancy, repairs or something else; we read every reply.

FAQ

Common questions, answered briefly

People also ask

Other questions, briefly answered