Tenant Delinquency Aging Report in Excel: Catch the Loss at 30 Days, Not 120

Your rent ledger says unit 7 owes $4,200. That balance has been climbing since June, the tenant sends something every few weeks, and it has never once looked alarming enough to act on. A tenant delinquency aging report in Excel breaks that same $4,200 into the months it came from and tells you what the ledger cannot: the oldest unpaid charge is 71 days old, the tenant has collected at 16 percent over the last quarter, and you are about four weeks from a loss you will never claw back. This article builds that report from a charge-level ledger, gives you the formulas, and runs a 14-unit building through it.

A Ledger Tells You Who Paid, Not Who Is About to Stop

A rent ledger is a transaction list. It answers one question well: did this tenant pay in July. An aging report is a position report. It answers a different question: how old is the money I am owed, and is the hole getting deeper or shallower. Only the second question predicts anything, because rent losses do not arrive as a surprise. They arrive as a balance that ages quietly for four months while you tell yourself the tenant is catching up.

The balance column hides the shape

Two tenants in the same building owe you exactly $2,175 today. The ledger shows one number for each and gives you no reason to treat them differently.

Boateng paid every month for two years and just got hit with a $650 utility rebill on top of August rent. Whitcomb stopped answering the phone in May. One of these needs a text message. The other needed a filing six weeks ago. The balance column treats them as identical, and that is the entire reason this report exists.

What 60 days of hesitation actually costs

Price the delay before you decide it is harmless. Unit 7 rents for $1,450, which is $47.67 a day. In this county the sequence runs 5 days on the notice, 21 days from filing to hearing, 7 days to the writ, and 10 days to the lockout, roughly 45 days from serving the notice to holding the keys. The only variable below is the day you start.

The gap is $2,860, which is exactly 60 days of rent. Every other line is identical, because the filing costs the same and the turn costs the same whether you start in July or in September. Waiting does not buy you a better outcome or more information. It buys the tenant two more months of housing at your expense. On a 14-unit building running a 6 percent margin, $2,860 is roughly what four units clear in a month.

Build the Report From Charges, Not From Balances

Most landlord spreadsheets store one running balance per tenant. You cannot age a running balance, because it has no date attached. Aging requires that every charge keep its own birthday, and that payments be applied to specific charges rather than dropped into a pool.

Three tabs, and the four columns that do the work

Tab one is Charges: date, unit, tenant, type, amount. Type matters more than it looks, and the section on notices explains why. Tab two is Payments: date, unit, tenant, amount, method. Tab three is the aging report itself, one row per tenant, all formulas.

On the Charges tab, columns F through I turn a list of invoices into a position. Here is unit 7, sorted oldest first.

The tenant paid $1,450 in March, $900 in April, $1,450 in May, and $700 in July. Four payments, $4,500 total, against $8,700 charged. The ledger reports $4,200 owed. The table above reports where the $4,200 lives.

Apply the cash oldest first

Column F counts every charge that came before this one for the same tenant, which is the running total the payment waterfall has to fill before it reaches this row.

=SUMIFS($E$2:$E2,$C$2:$C2,$C2)-$E2

Column G pours all cash that tenant has ever paid into the charges from the top down, and stops when this row is full.

=MIN($E2,MAX(0,SUMIFS(Payments!$D:$D,Payments!$C:$C,$C2)-$F2))

Read it as a business rule rather than a formula. Total paid minus everything owed before this row equals the cash still available when the waterfall arrives here. If that is negative, nothing is left, so MAX floors it at zero. If it exceeds the charge, the charge is fully covered, so MIN caps it. The June row gets $150 because that is all the money left after March, April, and May were satisfied.

Column H is the open balance, =$E2-$G2, and column I is its age, =IF($H2=0,"",TODAY()-$A2). Two requirements make this work: the Charges tab must be sorted ascending by date, and tenant names must be identical strings, which means a dropdown validated against a tenant list, not free typing.

Age each charge, not the pile

On the aging tab, with the tenant name in A5, each bucket is a date-bounded sum of open balances.

• 0 to 30 days: =SUMIFS(Charges!$H:$H,Charges!$C:$C,$A5,Charges!$A:$A,">"&TODAY()-31)
• 31 to 60: =SUMIFS(Charges!$H:$H,Charges!$C:$C,$A5,Charges!$A:$A,"<="&TODAY()-31,Charges!$A:$A,">"&TODAY()-61)
• 61 to 90: =SUMIFS(Charges!$H:$H,Charges!$C:$C,$A5,Charges!$A:$A,"<="&TODAY()-61,Charges!$A:$A,">"&TODAY()-91)
• Over 90: =SUMIFS(Charges!$H:$H,Charges!$C:$C,$A5,Charges!$A:$A,"<="&TODAY()-91)

Because the bounds key off TODAY(), the report re-ages itself every morning without anyone touching it. That is the point. A static aging report is just a screenshot of a problem.

The Clock, the Trigger, and the Number You Put on the Notice

Days since the oldest open charge

The single most useful cell on the sheet is not a dollar figure. It is the age of the oldest charge that still has money on it.

=IFERROR(TODAY()-MINIFS(Charges!$A:$A,Charges!$C:$C,$A5,Charges!$H:$H,">0"),0)

Sort the report by this column descending and the building organizes itself by urgency instead of by size. The biggest balance is rarely the most dangerous one.

The trigger ladder

A number without an instruction gets read and ignored. Put the instruction in a column, with the clock in H5 and the total owed in G5.

=IFS(G5=0,"Current",H5<=10,"Reminder",H5<=30,"Late fee, call",H5<=45,"Serve notice",H5<=75,"File",TRUE,"Attorney, write-off review")

Calibrate those rungs to your county, not to a national default. The rung that matters is "Serve notice," and there is a non-arbitrary way to place it: it belongs before the day the unpaid rent passes the security deposit, because that is the day the tenant stops having anything at stake. With a $1,450 deposit and $1,450 rent, that day arrives 30 days into the first missed month. Add a flag for it.

=IF(RentOnly>Deposit,"Deposit exhausted","Covered")

The number that goes on the notice

Total owed and rent owed are not the same number, and confusing them is how a filing gets tossed. Many states allow a pay-or-quit demand for rent only, so a $650 utility rebill and a $75 late fee sitting in the same balance can invalidate the demand. Keep the Type column honest and pull the demand figure separately.

=SUMIFS(Charges!$H:$H,Charges!$C:$C,$A5,Charges!$D:$D,"Rent")

Boateng owes $2,175 in total and $1,450 in rent. Serving for $2,175 in a rent-only state means starting over, which is roughly 30 days and $1,430 of occupancy given away over a column you did not build. Confirm the rule where your property sits, then build the column either way, because a lender or a buyer will eventually ask you to split fee income from rent anyway.

Payment plans that hold the trigger, but only while they hold

A promise to pay should suppress the action column, and only while the tenant is actually current on the promise. With the installment in J5, the plan start date in K5, and the number of installments due to date in L5:

=IF(AND($J5>0,SUMIFS(Payments!$D:$D,Payments!$C:$C,$A5,Payments!$A:$A,">="&$K5)>=$J5*$L5),"Plan current, hold",I5)

The instant a payment is missed, the original trigger returns on its own. No meeting, no judgment call, no memory required. That is the difference between a plan and a delay.

One Building, Four Balances, Four Different Decisions

Fourteen units, $1,450 average rent, $20,300 of monthly billing. Ten tenants are current. Here is the rest of the report on August 11.

The collection rate is one more SUMIFS pair, payments over charges for the last quarter.

=IFERROR(SUMIFS(Payments!$D:$D,Payments!$C:$C,$A5,Payments!$A:$A,">"&TODAY()-91)/SUMIFS(Charges!$E:$E,Charges!$C:$C,$A5,Charges!$A:$A,">"&TODAY()-91),"")

Now read the rows. Alvarez owes $145 and collects at 97 percent, which is a late fee on a good tenant. Send the reminder and forget it. Boateng shows the second largest balance on the sheet and is the second least urgent, because all of it was billed 10 days ago and $725 of it is not even rent. Cordell is the one that costs money: 71 days on the oldest charge, a 16 percent collection rate over three months, and $4,200 outstanding with the deposit already exhausted twice over. Whitcomb is the row most landlords delete, and deleting it is how the $2,175 becomes zero. You cannot evict someone who already left, so that balance is a small claims or collections decision, and the clock on it is your state's statute of limitations, not the eviction calendar.

One portfolio number belongs at the bottom: everything past 30 days divided by monthly billing.

=SUM(D20:F20)/GPR returns 24.3 percent here, against $4,925 aged past 30 days on $20,300 of rent. Institutional multifamily runs this at 1 to 3 percent. Flag it at 5 with =IF(ratio>0.05,"FLAG","OK") and treat anything above that as a process failure rather than a tenant problem, because four bad rows out of fourteen is not bad luck.

Four ways an aging report lies to you

• Aging the balance instead of the charges. A single $4,200 figure has no date, so it gets classified by feel. Cordell's balance is not 71 days old or 10 days old. It is $1,300 at 71 days, $1,450 at 41, and $1,450 at 10, and only the charge-level view says that.
• Applying payments to the newest charge. Post Cordell's July $700 against July rent and the same $4,200 shows $550 sitting in the over-90 bucket and an oldest item of 132 days instead of 71. Same total, different age, different trigger, and a ledger that contradicts the lease. Most leases apply payments to the oldest balance first. Build what yours says, or amend it.
• Dropping former tenants. Money does not stop aging when the keys come back. A move-out row changes which collection path applies, not whether the balance is real.
• Running it when someone remembers. An aging report reviewed in November catches what should have been caught in August. Cadence is the feature.

Run It on the Sixth of Every Month

Grace periods usually expire on the fifth. Open the file on the sixth, before anything else, and work five steps in order.

1. Confirm the Charges tab is sorted ascending by date, then fill the F through I formulas down.
2. Sort the aging tab by oldest open charge, descending. Read that column first, not the total.
3. For every row past 30 days, compare rent only against the deposit and note who has nothing left at stake.
4. Execute every instruction in the trigger column the same day. A trigger you override twice is a trigger you should delete.
5. Stamp the action and the date in a Notes column, because that column becomes the exhibit if the file ever reaches a courtroom.

Fifteen minutes a month against a $2,860 swing on a single unit is the best hourly rate in this business.

If you would rather not wire the payment waterfall, the four buckets, the trigger ladder, and the deposit-exhaustion check by hand, SheetCraft's Rental Property Analyzer ships with the delinquency module already built: a charge-level ledger that applies every payment oldest first, buckets that re-age off TODAY() each morning, a rent-only demand column kept separate from fees and rebills, a per-tenant 90-day collection rate, and a portfolio delinquency ratio that flags the month you cross 5 percent. You enter charges and payments. It tells you which door to knock on this week, and exactly what number goes on the notice.