Rental Property Spreadsheet: Track Rent, Expenses, NOI and Returns in Excel
Set up a rental property spreadsheet in Excel or Google Sheets: property and lease register, monthly rent ledger, expense log by category, NOI, cap rate, cash flow and cash-on-cash return, with a worked two-property example.
A rental property spreadsheet tracks, for each property you own, the rent that was due and collected, every expense by category, and the resulting numbers investors care about: net operating income (NOI), cash flow after the mortgage, cap rate and cash-on-cash return. For a landlord with one to a handful of properties, an Excel or Google Sheets workbook with four linked tabs — properties, leases, rent ledger and expenses — is enough to spot late payments, prepare tax-time totals and see which property actually earns its keep.
This guide belongs to our Rental Property Management section. The spreadsheet is a record-keeping and analysis tool; leases, deposits and taxes follow state and federal rules that you should confirm with a qualified professional.
The four tabs
| Tab | One row per… | Key columns |
|---|---|---|
| Properties | Property or unit | ID, address, type, purchase date, purchase price, closing costs, loan balance, interest rate |
| Leases | Lease | Unit, tenant, start, end, monthly rent, due day, late fee terms, security deposit held |
| Rent ledger | Month and unit | Rent due, amount received, date received, status, days late |
| Expenses | Bill or receipt | Date, property, category, vendor, amount, receipt link |
| Summary | Property | Income, operating expenses, NOI, debt service, cash flow, returns, occupancy |
Expense categories that keep tax time simple
Use a dropdown with consistent categories, for example: advertising, cleaning and maintenance, insurance, legal and professional fees, management fees, mortgage interest, repairs, supplies, property taxes, utilities, HOA dues and travel. US landlords generally report rental income and expenses on Schedule E of Form 1040, so categories that mirror the lines you or your tax preparer use will save time. Keep capital improvements (a new roof, an addition) in a separate category, because they are generally depreciated rather than deducted in one year — confirm the treatment with a tax professional.
Formulas
Rent status and days late
- Status:
=IF(E5>=D5,"Paid",IF(E5>0,"Partial",IF(TODAY()>F5,"Late","Not due")))where D is rent due, E received and F the due date (plus any grace period your lease allows). - Days late:
=IF(AND(E5<D5,TODAY()>F5),TODAY()-F5,0) - Open balance per lease:
=SUMIFS(Ledger!D:D,Ledger!B:B,A5)-SUMIFS(Ledger!E:E,Ledger!B:B,A5)
Apply late fees only as your lease and local law allow; put the fee in its own column rather than editing the rent amount.
Annual totals per property
- Rent collected:
=SUMIFS(Ledger!E:E,Ledger!A:A,A5,Ledger!G:G,2026)(G = year) - Operating expenses (excluding mortgage principal and interest):
=SUMIFS(Expenses!E:E,Expenses!B:B,A5,Expenses!G:G,2026,Expenses!C:C,"<>Mortgage interest")
The return metrics
| Metric | Formula | What it tells you |
|---|---|---|
| Gross yield | Annual scheduled rent ÷ purchase price | Quick screen, ignores costs |
| NOI | Rent collected − operating expenses | Profit from operations before financing and taxes |
| Cap rate | NOI ÷ property value (or price) | Return if bought with cash; compares properties |
| Cash flow | NOI − annual mortgage payments | What is left in your pocket each year |
| Cash-on-cash return | Annual cash flow ÷ cash you invested | Return on your own money, after financing |
| Occupancy | Months rented ÷ months available | How much vacancy is costing you |
Annual mortgage payment: =-PMT(6.75%/12,360,192000)*12. Remember that this payment includes principal, which reduces your loan rather than being an expense — so cash flow understates your total return, while NOI ignores financing entirely.
Worked example: two properties
Property A — single-family home bought for $240,000 with $48,000 down plus $6,000 closing costs ($54,000 cash invested) and a $192,000, 30-year loan at 6.75%. Rent $1,950 per month, occupied all year. Property B — duplex bought for $360,000 in cash, two units at $1,300 each; one unit sat empty for one month between tenants.
| Property A | Property B | |
|---|---|---|
| Scheduled rent (12 months) | $23,400 | $31,200 |
| Rent collected | $23,400 | $29,900 |
| Property tax | $3,100 | $4,800 |
| Insurance | $1,350 | $2,100 |
| Repairs and maintenance | $1,800 | $2,900 |
| Water and trash (owner-paid) | — | $1,400 |
| Operating expenses | $6,250 | $11,200 |
| NOI | $17,150 | $18,700 |
| Gross yield | 9.75% | 8.67% |
| Cap rate | 7.15% | 5.19% |
| Mortgage payments (12 × $1,245.31) | $14,943.72 | — |
| Cash flow | $2,206.28 | $18,700 |
| Cash-on-cash return | 4.09% on $54,000 | 5.19% on $360,000 |
| Occupancy | 100% | 95.8% (23 of 24 unit-months) |
How to read it: Property A has the stronger cap rate, but after a 6.75% mortgage its cash-on-cash return is about 4.09% — the loan payment absorbs most of the NOI, although part of each payment is principal you are building as equity. Property B, bought in cash, returns its cap rate as cash yield; the one-month vacancy cost $1,300. All figures are illustrative; use your own leases, statements and receipts.
A monthly landlord routine
- In the first days of the month, mark rent received from your bank statement.
- Follow up on anything marked Late, in writing, and note the contact.
- Enter the month’s bills and receipts with categories and receipt links.
- Check lease end dates (flag anything within 90 days with
=IF(D5-TODAY()<=90,"Renewal due","")) and plan renewals or turnover. - Quarterly, review the Summary: NOI trend, vacancy and repair spending by property.
Plan recurring upkeep — HVAC service, gutter cleaning, smoke-detector checks — with a preventive maintenance schedule, and check loan details with the loan amortization schedule. If the rentals are part of a business, the totals also feed your profit and loss statement and cash flow forecast.
Security deposits and records
Record each deposit, where it is held and when it was received. State and local laws often regulate deposit limits, where deposits are kept, interest and return deadlines, and they differ widely — check the rules that apply to your property. Keep move-in and move-out inspection photos linked from the Leases tab.
Common mistakes
- Mixing personal and rental money: a separate bank account makes the ledger reconcile easily.
- Treating mortgage principal as an expense: only interest is an operating cost for tax purposes; principal reduces the loan.
- Ignoring vacancy and repairs when judging a property: gross yield flatters every deal.
- Not recording partial payments: small shortfalls compound into large balances.
- Overwritten formulas: lock the Summary tab — see how to protect formulas in Excel.
FAQ
What is a good cap rate for a rental property?
It depends heavily on the market, property type and interest rates. Compare cap rates of similar properties in the same area rather than relying on a single target, and look at cash flow after financing too.
Is the 1% rule useful?
The “1% rule” (monthly rent of at least 1% of the purchase price) is a quick screening shortcut, not an analysis. In the example, Property A rents for about 0.81% of its price and Property B for about 0.72% — the spreadsheet’s NOI and cash-flow figures tell you far more.
Can I track multiple units in one building?
Yes. Give each unit its own ID (B-1, B-2) in the Properties and Leases tabs, and roll them up by building in the Summary with SUMIFS on a “Building” column.
Does the spreadsheet work in Google Sheets?
Yes. SUMIFS, IF, AND, TODAY and PMT all work the same way, and sharing the sheet with a partner or bookkeeper is easy.
Next step: set up the Properties and Leases tabs, enter the last three months of rent and expenses, and let the Summary calculate NOI and cash flow. More tools are in Tools by Industry.