Rental Property Other Income Tracker in Excel: The NOI Hiding in Your Deposit Column

Dana owns an 8-unit building in Greensboro. Her rent roll says $8,400 a month. Her bank statements average $8,935. She has never reconciled the $535 gap because she knows roughly where it comes from: the laundry machines, the six reserved parking spaces, three pet rent addenda, four storage lockers, and the occasional late fee. It shows up in the account, so she assumes it counts. A rental property other income tracker in Excel is the thing she does not have, and it is costing her about $91,000.

Not in cash. In value. When she refinances or sells, an appraiser will build a stabilized net operating income from documents, not from her memory of what the deposits included. Income that cannot be traced to a category, a lease clause, and a bank line gets discounted or dropped entirely. The $535 a month she is genuinely collecting turns into a rounding error in someone else's underwriting spreadsheet.

This is the least glamorous line on an operating statement and the one with the highest ratio of dollars to effort. Rent takes a tenant, a turnover, and a market. Other income takes a spreadsheet.

The Money Hiding in Your Deposit Column

Here is Dana's actual ancillary income, broken out the way it should have been from day one:

The recurring block is $495 a month, or $5,940 a year. At a 6.5% cap rate, that block is worth =5940/0.065, which is $91,384 of building value. The late fees are real money but they are not capitalized at full weight by a careful underwriter, and you should not plan on them being.

Now run the comparison that matters. If Dana keeps doing what she is doing, she hands the appraiser twelve months of bank statements with mixed deposits and gets credited for base rent plus whatever the appraiser is willing to infer, typically zero to a token laundry allowance. Call it $15,000 of recognized value. If she spends one hour building a categorized log and twelve months feeding it five minutes a week, she hands over a trailing-twelve schedule that ties to the bank and gets credited near the full $91,384. One hour plus roughly four hours of data entry across the year buys somewhere around $76,000 of appraised value. There is no rehab line item on earth with that return.

The same math runs in reverse on a purchase. A seller who shows you $6,420 of other income with no ledger behind it is asking you to pay $91,000 for a claim. Ask for the category detail. If it does not exist, underwrite the income at zero and let them prove you wrong.

Recurring vs Non-Recurring: The Split That Decides Your Valuation

Most landlords who do track other income make one mistake: they dump it all into a single "Other Income" line. That is worse than useless when an underwriter sees it, because a lender cannot tell whether your $6,420 is contractual monthly income or one lucky year of lease break fees. Faced with an undifferentiated number, the conservative move is to haircut all of it.

Split the taxonomy on day one:

Two consequences follow from this table. First, your tracker needs a category field with a controlled list, not a free-text notes column. Second, the recurring flag lives on the category, not on the transaction, so it can never be entered inconsistently.

There is a tax consequence too. Every dollar in this table is ordinary rental income on Schedule E, including the fees people mentally file as reimbursements. Security deposit forfeitures applied to unpaid rent are income in the year applied. If your only record is a deposit line labeled "misc," your CPA is guessing, and guesses in that direction get corrected during an audit at your expense.

Building the Tracker: Three Sheets That Take an Hour

Sheet 1: Income_Log

One row per transaction. No summaries, no formatting cleverness, no merged cells.

Column G is never typed. It reads the category table:

=XLOOKUP($D2,Categories!$A:$A,Categories!$B:$B,"UNMAPPED")

The "UNMAPPED" fallback is the entire point. When somebody types "Pet Rent " with a trailing space, or invents "pet fee" on a Tuesday, that row lights up instead of silently vanishing from every rollup downstream. Pair it with a conditional format on =$G2="UNMAPPED" and you catch the typo in the same session you made it, not eleven months later when the appraiser's number comes back low.

Sheet 2: Categories

Eight to twelve rows, set up once. Column A is the category name, B is Recurring or Non-recurring, C is the expected monthly total for the property, D is the proof document, E is the tenant-facing rate.

Column C is what turns a log into a control. You are asserting what you should be collecting: 6 parking spaces at $25 means $150, every month, no exceptions. Anything less is a leak, and the dashboard will say so.

Sheet 3: Dashboard

Categories down column A, months across row 4 as real dates (07/01/2026, not the text "July"). Every cell is the same formula:

=SUMIFS(Income_Log!$E:$E,Income_Log!$D:$D,$A5,Income_Log!$A:$A,">="&B$4,Income_Log!$A:$A,"<="&EOMONTH(B$4,0))

Column N holds the trailing-twelve total per category, =SUM(B5:M5). Then three cells do the actual work.

Recurring annual income:

=SUMIF(Categories!$B$2:$B$13,"Recurring",$N$5:$N$16)

Capitalized value of that income, with your market cap rate in B2:

=SUMIF(Categories!$B$2:$B$13,"Recurring",$N$5:$N$16)/$B$2

This is the number to look at when you are deciding whether to add three more storage lockers for $1,800. At a 6.5% cap, $15 a month per locker across three lockers is $540 a year and $8,308 of value. That is a 4.6x return on the build cost, realized the day you sell, and it never appears in a cash-on-cash calculation.

Collection variance against expectation:

=IF(Categories!$C5=0,"",(N5/12-Categories!$C5)/Categories!$C5)

Flag it so you do not have to read percentages: =IF(O5<-0.05,"UNDER-COLLECTING","OK"). Anything more than 5% below the contractual rate means either a tenant stopped paying, a lease was renewed without carrying the addendum forward, or somebody forgot to bill.

The Three Leaks the Log Catches in Month One

Every landlord who builds this finds the same three problems. They are not exotic. They are the direct consequence of ancillary income living in nobody's job description.

Leak 1: the addendum that died at renewal. Unit 3B signed a pet addendum at $35 a month in 2024. At the 2026 renewal, the new lease was generated from the base template and the pet clause did not carry over. The dog is still there. The billing is not. Four months at $35 is $140 gone, and at a 6.5% cap the ongoing omission costs $6,462 in value. Detect it by comparing the lease flag to actual collections:

=IF(AND(Leases!$F3="Yes",SUMIFS(Income_Log!$E:$E,Income_Log!$C:$C,Leases!$A3,Income_Log!$D:$D,"Pet rent",Income_Log!$A:$A,">="&EOMONTH(TODAY(),-2)+1)=0),"MISSING BILLING","")

Leak 2: the assigned space nobody charges for. Six spaces, five paying. The sixth was assigned verbally to the tenant in 1A during a snowstorm in 2024 and never made it onto a rental agreement. That is $300 a year and $4,615 of value, sitting in a parking lot you already own and already pave.

Leak 3: utility under-recovery. If you bill back water and sewer, the recovery ratio is the only number that matters:

=SUMIFS(Income_Log!$E:$E,Income_Log!$D:$D,"Utility reimbursement",Income_Log!$A:$A,">="&DATE(2026,1,1))/SUMIFS(Expenses!$D:$D,Expenses!$B:$B,"Water/Sewer",Expenses!$A:$A,">="&DATE(2026,1,1))

On a $7,200 annual water bill, a 78% recovery means $1,584 a year you paid for on behalf of tenants who agreed to pay it. Common causes: vacant units left in the allocation formula, a rate that has not moved since 2023 while the utility raised prices twice, or two units that were never enrolled. Each one is a fifteen-minute fix once you can see the ratio.

The three leaks together cost Dana $2,024 a year in cash and roughly $31,000 in value. The log that finds them costs an hour.

Package It Before You Need It

The tracker only converts into money if it survives contact with a third party. Three people will look at it, and they want different things.

• The appraiser wants a trailing-twelve schedule by category that ties to bank deposits. Give them the dashboard plus the monthly tie-out: =ROUND(SUMIFS(Income_Log!$E:$E,Income_Log!$A:$A,">="&B$4,Income_Log!$A:$A,"<="&EOMONTH(B$4,0))-Bank!B5,2) with a check cell =IF(ABS(C5)>1,"INVESTIGATE","TIED"). A ledger that ties to the bank every month for twelve months is credible. One that does not gets thrown out.
• The lender wants proof of recurrence. Attach the lease addenda, the parking agreements, and the laundry vendor commission statements. Present recurring and non-recurring as two separate subtotals so they do not have to guess and haircut.
• Your CPA wants the full gross, including the non-recurring fees, mapped to the right Schedule E lines with the RUBS income shown against the gross utility expense rather than netted invisibly.

Build the three sheets this week using the last twelve months of bank statements. Categorize backward, not forward. Twelve months of history is what an appraiser can use; a log that starts today is worth nothing until next July. Budget two hours for the reconstruction and five minutes every Friday after that.

Then run the capitalized value cell and look at the number. On a small portfolio it is usually between $60,000 and $150,000 of value that already exists, already gets deposited, and currently cannot be proven. That is the entire argument for tracking it.

If you would rather not build the category table, the validation lists, the tie-out, and the leak formulas from scratch, SheetCraft's Rental Property Analyzer ships with the other income module already wired: a controlled category list split into recurring and non-recurring, per-unit collection variance flags, RUBS recovery ratios, and an NOI rollup that feeds straight into the cap rate and valuation tabs. You enter transactions. It tells you what the income is worth and which unit stopped paying.