Build a Free Rent Ledger Excel Spreadsheet

A free rent ledger in Excel can track every bed, every resident's balance and every referral partner across your houses, and you can build one in about an hour. This guide gives you the exact columns, data types and formulas, plus a 30-minute monthly reconciliation routine that catches arrears before they become write-offs.

A rent ledger is a per-resident record of charges, payments and running balance. At month end it must prove three things: who owes what, when it was owed, and what actually landed in your account. Anything less is memory, and memory is where unreconciled rent hides.

What a ledger must prove at month end

For each bed you need the move-in date, the monthly rent, every payment with its date and method, and the balance carried forward. When a case manager asks whether a resident is paid up, the ledger should answer in under a minute. At a few houses, functions like SUMIFS and a PivotTable handle this fine.

Where spreadsheets break first

Three failure points tend to show up early: two people editing the same file, a resident who moves between houses mid-month, and referral credit nobody logged. We walk through each of them in what a spreadsheet costs a small operator. For one or two houses, though, a well-built sheet is a reasonable place to start.

Build the ledger as three tabs, not one giant sheet. Splitting residents, transactions and houses keeps every formula short and turns monthly reconciliation into a filter, not a hunt.

Tab 1: Residents and beds

One row per resident stay. Use these columns, in this order (A to J):

The Move-out date column is your bed board. A formula against it, covered in the next section, gives you occupancy per house and total open beds at a glance.

Tab 2: Transactions

One row per charge or payment, appended forever, never edited. Columns: A. Bed ID, B. Date (the due date for a charge, the deposit date for a payment), C. Type (Rent, Deposit, Fee, Credit or Payment), D. Charge, E. Payment, F. Balance, G. Method (Cash, Zelle, Check) and H. Notes. Sort the tab by date, oldest first.

Never overwrite a row to fix a mistake; add a correcting Credit row instead. That habit is what makes the ledger trustworthy when a resident disputes a balance.

Tab 3: Houses and referral partners

Two small lists: each house with its address and total bed count, and each referral partner with a contact name and phone number. Every house and partner name elsewhere in the workbook must match these lists exactly, or your counts will silently drop rows. Excel's Data Validation drop-downs, pointed at these lists, prevent most typos.

This is the same structure Royalty Group Homes is built around: beds, stays, a ledger per resident and referral sources. If you later outgrow Excel, the way you think about the data carries over unchanged.

Running balance per bed

On the transactions tab, put this in F2 and copy it down. It adds every charge and subtracts every payment for the same Bed ID, up to and including that row, so each row shows that bed's balance at that point:

=SUMIFS($D$2:D2,$A$2:A2,A2)-SUMIFS($E$2:E2,$A$2:A2,A2)

A plain running total down the whole column would mix every resident together; the SUMIFS version keeps each bed separate even when rows from different houses interleave.

Days late and aging

On a charge row, Days Late is:

=IF(AND(F2>0,TODAY()>B2),TODAY()-B2,"")

And an aging bucket in the next column is:

=IF(OR(F2<=0,TODAY()<=B2),"Current",IF(TODAY()-B2<=30,"1-30",IF(TODAY()-B2<=60,"31-60","60+")))

Build it with formulas rather than typing a balance by hand. The IRS says to keep records that support the income you report until the period of limitations for that return runs out. A formula-driven balance means every number can be rebuilt from raw charges and payments, so if a resident disputes arrears you can show the math, not just a figure you typed. It also survives a new house manager, because anyone can audit a formula.

Occupancy and open beds

On the residents tab, put this in J2 and copy it down:

=IF(AND(D2<=TODAY(),OR(E2="",E2>TODAY())),1,0)

On the houses tab, with the house name in A2 and its bed count in C2, open beds for that house is:

=C2-SUMIFS(Residents!J:J,Residents!B:B,A2)

Add conditional formatting so any house with an open bed turns red, and the board reads at a glance.

Days vacant and lost revenue

For an empty bed, days vacant is today minus the last move-out date; for a bed that has refilled, it is the next move-in minus the last move-out. Multiply by a daily rate of =MonthlyRent*12/365 to price the empty time. A $1,100 bed works out to about $36 a night, so three weeks empty is about $760. That number is what your referral pipeline has to beat.

Reconciliation is three passes: match, flag, lock. Do it the same day every month, on the 1st or 2nd, before move-ins muddy the numbers. Work from a month tab with one row per bed: Bed ID, resident, opening balance, expected rent, payment received, date paid, cleared, ending balance and notes.

Step 1: Match deposits to expected rent

Open your bank app next to the ledger. For each resident, type the amount actually received and the deposit date, and add the matching Payment row on the transactions tab. Anything that matches expected rent exactly gets a 1 in Cleared. Work house by house so each home takes a few minutes, not half an hour.

Step 2: Flag partials and arrears

Any row where the payment is less than expected rent gets a 0 in Cleared and a note: partial, late or nothing. The ending balance now shows who is behind and by how much. Text those residents the same day, and write the promised payment date in Notes, so next month starts with a date, not a memory. A resident three months behind is a collections conversation, not a note.

Step 3: Roll forward and lock the month

Copy each ending balance into next month's opening balance, then duplicate the month's tab so its history stays frozen. Update the residents tab: mark move-outs, list open beds, and get them in front of case managers the same day. An empty bed nobody knows about is the most expensive line in the ledger.

A Source column and a PivotTable

The Referral partner column on the residents tab is your attribution. On every move-in, record the discharge planner, case manager or program that sent the resident, spelled exactly as it appears on the partners list.

Then create a PivotTable with Referral partner in Rows and a count of Resident name in Values. Add House and Move-in date as filters, and you can see who filled which bed, and when, in a few clicks.

What to report back to partners

Case managers want to know their placements worked out. A short monthly note covering beds filled, residents still housed and move-outs keeps the relationship current, and shows you which partners deserve your follow-up time.

Never pay for referrals. Paying or receiving anything of value for a referral to a recovery home can be a federal crime under 18 U.S.C. 220 when services are covered by a health care benefit program, and many states have their own patient-brokering laws.

One honest gap: a spreadsheet records who filled a bed, not the referral pipeline itself. Follow-ups and intake still live in your text threads. That gap is a big part of why we built Royalty Group Homes around a bed board and referral partner tracking.

The three failure modes

First, concurrent edits. If a house manager marks a move-in while you have a local copy open, one of you can overwrite the other. Excel co-authoring lets several people edit the same workbook at once, but it needs a Microsoft 365 subscription and a file stored in OneDrive or SharePoint, and nothing stops someone typing over a payment cell.

Second, no audit trail inside the ledger. When a resident disputes a balance, the sheet shows the current number, not who entered it or why. Third, bed board drift: the sheet says occupied, the bed is empty, and nobody notices for two weeks. At one house that costs real rent; across several houses it compounds.

What dedicated tools do differently

Past two or three houses, or once two people touch the file, a dedicated tool keeps the bed board and the rent ledger in one database, so a move-out updates occupancy immediately and every entry is recorded rather than overwritten. Royalty Group Homes does this for small operators, with per-bed status, a rent ledger per resident and referral partner tracking. Your first house is free with every feature; two or three houses are $99 a month, and four or more are $199 a month. The honest tradeoff: you give up some spreadsheet flexibility, and you gain a ledger nobody can quietly hand-edit at midnight. For a fuller comparison, see the software buyer's guide.

A generic free rent ledger template gets you columns and formatting in ten minutes, but most assume one lease per unit. They usually have no bed board, no per-bed move-in and move-out dates, and nowhere to record which referral partner filled the room. You end up fighting the template at your first reconciliation instead of chasing arrears.

Building the spec above takes about an hour and gives you per-bed occupancy, a ledger that flags arrears automatically, and a referral column that shows who actually fills beds.

The honest limit: a spreadsheet cannot tell you when a bed goes empty or follow up with a case manager for you. When you hit that wall, usually around the second or third house, dedicated software starts paying for itself.