LANDLORD BOOKKEEPING
How to Build a Rental Property Income and Expense Spreadsheet
One transactions tab, expense categories that line up with Schedule E, and a summary that shows net operating income and cash flow per property. Here is the structure, the formulas and a calculator to check a property in one minute.
STRUCTURE FIRST
Five tabs that keep a rental ledger clean
The most common mistake is one tab per month or one tab per property. It looks tidy for a few weeks, then totals stop matching and nobody knows which copy is right. Keep every transaction in one table and let the summary tabs do the slicing.
Properties
One row per property: a short ID (OAK-12), address, purchase date, purchase price, units and ownership share. Everything else refers to the ID.
Transactions
Every rent payment, fee, repair and bill, one per row. This is the only tab you type into day to day.
Categories
The list of income and expense categories, each mapped to a Schedule E line. Drop-downs in the transactions tab read from here.
Monthly summary
Income, expenses, NOI and cash flow by property and month, calculated with SUMIFS.
Annual / tax summary
Totals per Schedule E line per property for the year, ready to hand to your preparer.
THE TRANSACTIONS TAB
Columns every rental transaction needs
| Column | Example | Why it matters |
|---|---|---|
| Date | 2026-03-01 | Drives monthly and yearly totals. Use real dates, not text. |
| Property ID | OAK-12 | Lets one table hold every property. Pick from a drop-down. |
| Type | Income / Expense | Keeps signs simple: enter every amount as a positive number. |
| Category | Repairs | Maps to a Schedule E line through the Categories tab. |
| Amount | 185.00 | Always positive; the Type column decides whether it adds or subtracts. |
| Payee / payer | City Plumbing | Answers "what was this?" a year later. |
| Method | Bank transfer | Helps reconcile with statements. |
| Receipt link | Drive / OneDrive link | The IRS expects records that support what you report. |
| Notes | Kitchen tap, unit 2 | Context for repairs vs. improvements and split costs. |
CATEGORIES THAT MATCH THE RETURN
Rental expense categories mapped to Schedule E
If your categories mirror the lines on Schedule E (Form 1040), tax time becomes a copy-paste job instead of a re-sort of 300 receipts. These are the Part I lines on the 2025 form.
Line names from the 2025 Schedule E (Form 1040), Part I. Line 20 is total expenses (lines 5 through 19). Check the current year's form before filing.
THE CATEGORY PEOPLE GET WRONG
Repairs vs. improvements
The IRS draws a line between costs that keep the property working and costs that make it better. Repairs are expensed in the year you pay them; improvements must be capitalized and depreciated over time (27.5 years for residential rental buildings under MACRS GDS, per Publication 527). Give improvements their own category so they never land in "Repairs" by accident.
Keeps it in working order
Fixing a leaking tap, patching drywall, repainting between tenants, replacing a broken window pane, fixing a lock.
Betters, restores or adapts it
A new roof, a replacement HVAC system, adding insulation, a kitchen remodel, a new deck. Record them in an "Improvements" category with the date placed in service.
LET THE SUMMARY DO THE WORK
Formulas for the monthly and annual summary
Assume the transactions table is named Tx with columns Date, Property, Type, Category and Amount. In the summary, put the property ID in A2, the first day of the month in B1 and a category in C1.
| Result | Formula (Excel and Google Sheets) |
|---|---|
| Income for the month | =SUMIFS(Tx[Amount],Tx[Property],$A2,Tx[Type],"Income",Tx[Date],">="&B$1,Tx[Date],"<"&EDATE(B$1,1)) |
| Expenses for the month | =SUMIFS(Tx[Amount],Tx[Property],$A2,Tx[Type],"Expense",Tx[Date],">="&B$1,Tx[Date],"<"&EDATE(B$1,1)) |
| One category for the year | =SUMIFS(Tx[Amount],Tx[Property],$A2,Tx[Category],C$1,Tx[Date],">="&DATE(2026,1,1),Tx[Date],"<"&DATE(2027,1,1)) |
| Net operating income | =Income − (Expenses − Mortgage interest − Improvements) — build it from the category totals so financing and capital costs stay out. |
Google Sheets tables use the same Table[Column] style after Convert to table; with plain ranges, replace Tx[Amount] with $E:$E and so on. The $ signs keep the property column and the month row fixed as you fill the grid; see how absolute references work.
IS THE PROPERTY WORKING?
NOI, cash flow, cap rate and cash-on-cash return
NOI = Rent collected − Operating expensesOperating expenses exclude mortgage payments and improvements. NOI describes the property, not the loan.
Cash flow = NOI − Mortgage payments (principal + interest)What actually reaches your account. Principal is not a tax deduction, but it is still cash out.
Cap rate = NOI ÷ Property valueCompares properties regardless of how they were financed.
CoC = Annual cash flow ÷ Cash investedDown payment, closing costs and initial repairs: the return on your own money.
Operating expenses ÷ Rent collectedA quick health check to compare months and properties.
Effective rent = Scheduled rent × (1 − vacancy %)Plan with a vacancy allowance, then compare with reality.
WORKED EXAMPLE
One single-family rental, one year
Rent is $1,800 a month with a 5% vacancy allowance. Operating costs: property tax $2,400, insurance $1,200, repairs $1,500, utilities $600 and 8% management on rent collected. The mortgage payment is $950 a month. Purchase price $250,000; cash invested $60,000.
Illustrative numbers- Rent collected
- $20,520 $21,600 × 95%
- Operating expenses
- $7,341.60 incl. $1,641.60 management
- NOI
- $13,178.40 before the mortgage
- Annual cash flow
- $1,778.40 NOI − $11,400 mortgage
- Cap rate
- 5.27% $13,178.40 ÷ $250,000
- Cash-on-cash
- 2.96% $1,778.40 ÷ $60,000
CHECK A PROPERTY
Rental property cash-flow calculator
Enter annual figures for one property. The calculator runs in your browser and saves nothing.
TRY IT
NOI, cash flow and returns for one rental
Use the same numbers you would put in the Properties and Transactions tabs. Leave a cost at 0 if it doesn't apply.
Illustrative estimate. Taxes, depreciation and loan terms are not included.
AVOID THESE
Rental bookkeeping mistakes that cost money
- !Mixing personal and rental moneyA separate bank account per rental business makes every transaction easy to prove.
- !Recording the whole mortgage payment as an expenseOnly the interest is a Schedule E expense. Split each payment into interest and principal.
- !Counting deposits as rentDeposits you plan to return aren't income. Track them separately.
- !Putting a new roof under "Repairs"Improvements are capitalized and depreciated, not expensed in one year.
- !No receiptsLink a photo or PDF to every expense row as you enter it, not in April.
- !Updating once a yearTen minutes a month keeps totals reconciled with bank statements and catches missed rent early.
READY-MADE LEDGER
Prefer a rental spreadsheet that's already built?
The structure above is enough to start. If you'd rather skip the setup and get dashboards for several properties, review the current product page for features and requirements.
Rental Property Income & Expense Spreadsheet Template in Excel and Google Sheets
The current listing describes tracking income and expenses for up to 100 properties and booking channels by pasting transaction data, a property-specific dashboard, monthly, annual, custom and comparison dashboards, a 5-year dashboard with projections, profit goals and budgeted expenses, and an ROI calculator. Confirm the latest details on the product page.
- Many properties and booking channels in one file
- Monthly, annual and 5-year dashboards
- Works in Excel and Google Sheets
Product features, included files and compatibility are listed on the product page and may change. Desktop or laptop use is recommended for spreadsheet editing.
Compare all rental property spreadsheetsFAQ
Rental property spreadsheet questions
What should a rental property income and expense spreadsheet include?
A properties list, one transactions table with date, property, type, category, amount, payee and a receipt link, a category list mapped to Schedule E lines, and monthly and annual summaries built with SUMIFS.
What expense categories should landlords use?
For U.S. rentals, mirror Schedule E Part I: advertising, auto and travel, cleaning and maintenance, commissions, insurance, legal and professional fees, management fees, mortgage interest, other interest, repairs, supplies, taxes, utilities, depreciation and other. Add separate categories for improvements and security deposits.
Is the mortgage payment a rental expense?
Only the interest portion is reported as an expense on Schedule E. Principal is not deductible, although it still reduces your cash flow.
How do I calculate cash flow on a rental property?
Subtract operating expenses from rent collected to get net operating income, then subtract the full mortgage payment (principal and interest). The result is your cash flow before income taxes.
Can I track several properties in one spreadsheet?
Yes. Give each property an ID, keep every transaction in one table with a Property column, and use SUMIFS with the property ID in the summary tabs.
Are security deposits rental income?
Not when you receive them if you plan to return them, according to IRS Publication 527. Amounts you keep, for example for damage, become income when you keep them.
Sources and further reading
- IRS: About Schedule E (Form 1040), Supplemental Income and Loss
- IRS: Instructions for Schedule E (Form 1040)
- IRS Publication 527: Residential Rental Property
- Microsoft Support: SUMIFS function
- Google Docs Editors Help: SUMIFS
IRS forms and publications checked September 2026 (2025 Schedule E). This article is general educational information, not tax, legal or investment advice.
