Rental property income and expense spreadsheet: a 12-month duplex log

A rental spreadsheet is two logs and one roll-up. Here is the layout, the SUMIFS formulas, and a 12-month duplex example worked through to a net figure.

General information only, not tax, legal or accounting advice. Late-fee, deposit and notice rules vary by state, city and lease; check your own. Tax references describe US federal pages on irs.gov as of the date above.

Two logs and a roll-up

Keep one list of every payment you receive and one list of every bill you pay, one row each, and let formulas add them up by month. Do not type monthly totals by hand; a total you typed cannot be traced back to a row.

Income log columns

• Date received
• Property and unit
• Amount
• Type (rent or other)

Expense log columns

• Date paid
• Property, and unit (or "shared" for a cost of the whole building)
• Vendor and amount
• Category, from a fixed dropdown list
• Repair or improvement flag
• Receipt reference

Both logs use the date the money actually moved. Publication 527 (2025) says most individual taxpayers use the cash method, under which you report income "in the year you actually or constructively receive it, regardless of when it was earned." The date received is what places a payment in a year.

Deposits and advance rent

Publication 527 says not to include a security deposit in your income when you receive it if you plan to return it to the tenant at the end of the lease. If you keep part or all of it because the tenant did not meet the lease terms, the amount you keep is income in that year. If an amount called a deposit is to be used as final rent, it is advance rent, and the publication says to include advance rent in income in the year you receive it, regardless of the period it covers.

In practice: keep refundable deposits in a separate list so they do not inflate your rent column, and log advance rent on the date it arrives.

The monthly roll-up

Format each log as an Excel table (select the range, then Ctrl+T) and name them IncomeLog and ExpenseLog. Add a third table with one row per month, holding the first day of the month as a real date in a Month column. Then:

Rent  =SUMIFS(IncomeLog[Amount], IncomeLog[Date Received], ">="&[@Month], IncomeLog[Date Received], "<"&EDATE([@Month],1))
Costs =SUMIFS(ExpenseLog[Amount], ExpenseLog[Date Paid], ">="&[@Month], ExpenseLog[Date Paid], "<"&EDATE([@Month],1))
Net   =[@Rent]-[@Costs]

The two date conditions pick every row from the first of the month up to, but not including, the first of the next month. To see one unit, add one more pair to each formula, for example IncomeLog[Unit], "A".

Worked example: a duplex for 12 months

Unit A rents for $1,450 a month and Unit B for $1,350. Unit B sits empty in September between tenants. In this example the April and August expenses include the two property tax installments, and September includes turnover costs.

Check the arithmetic against the rents:

Unit A  12 x 1,450 = 17,400
Unit B  11 x 1,350 = 14,850
Rent collected     = 32,250
Net = 32,250 - 12,595 = 19,655

The September row shows why a monthly view helps: one vacant month plus turnover costs leaves $70, yet the year is comfortably positive.

What the net number is, and is not

This net is cash left after the expenses you logged. It is not taxable income. On the 2025 Schedule E, line 3 is "Rents received," line 20 is "Total expenses. Add lines 5 through 19," and line 21 subtracts line 20 from line 3. Line 20 includes line 18, "Depreciation expense or depletion," which is computed separately (the Schedule E instructions point to the Form 4562 instructions) and is not in the example above. Line 12 is mortgage interest paid to banks, so log the interest figure from your lender's statement; the Schedule E instructions say the lender should send a Form 1098 by January 31 if you paid $600 or more in interest.

Also log whether each cost is a repair or an improvement. Publication 527 says to separate the two and keep accurate records, because you will need the cost of improvements when you sell or depreciate the property.

Keep the paper behind the rows

The IRS recordkeeping page says that, except in a few cases, the law does not require any special kind of records, and that you may choose any system "that clearly shows your income and expenses." It also says to keep records "as long as needed to prove the income or deductions on a tax return." The receipt reference column points you to the paper; the spreadsheet does not replace it.

What this does not cover

This is not tax advice. It does not calculate depreciation, decide whether a cost is a repair or an improvement, apply passive-loss limits, or handle any state forms. Rules on deposits and records vary by state. There is no bank import, so every row is typed or pasted. Check the figures with a qualified preparer before you file.

Sources

• IRS Publication 527 (2025), Residential Rental Property
• IRS, Instructions for Schedule E (Form 1040), 2025
• IRS, Schedule E (Form 1040), 2025 form
• IRS, Recordkeeping

More landlord guides

• Security deposit ledger template for Excel: columns and a reconciliation check
• Schedule E expense categories spreadsheet: sorting receipts into lines 5-19
• Rental property vacancy rate spreadsheet: physical vs economic vacancy
• Rent roll template for Excel: what a small landlord's rent roll needs
• Rent payment tracker spreadsheet: a late-status formula
• Prorated rent formula in Excel for a mid-month move-in
• Lease expiration tracker spreadsheet with a rent increase preview
• Late fee calculator in Excel: a formula with a grace period
• Duplex profit and loss per unit: splitting shared costs and finding NOI