Security deposit ledger template for Excel: columns and a reconciliation check
A deposit ledger should tell you how much of each tenant's money you hold and when a refund is due, and its total should match the account the deposits sit in. Here are the columns and formulas, with three tenants and $5,400 held.
Free: Rent Due & Late Fee Calculator, one Excel tab. Confirm your email and the file arrives right away, plus an occasional note (at most one a week) on running a small rental. Unsubscribe in one click. Privacy.
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.
What the ledger has to answer
A deposit ledger answers two questions: how much of each tenant's money are you holding today, and by when does a refund have to go out. The test of a good one is simple. Its total matches the balance of the account where the deposits sit.
The columns
- Unit and, if you want it, tenant.
- Received date and Amount.
- Deductions: the total you are keeping at move-out. Keep the itemised list and the receipts beside it, one line per deduction.
- Refund due: a formula, amount minus deductions.
- Move-out date, Return-by date and Settled on (the date the refund went out).
- Held now: a formula, so a settled deposit drops out of the total.
A worked example: three tenants, $5,400 held
| Unit | Received | Amount |
|---|---|---|
| Unit 1 | 2025-02-01 | $1,800 |
| Unit 2 | 2025-06-15 | $1,500 |
| Unit 3 | 2026-01-10 | $2,100 |
| Total held | $5,400 |
Headers sit in row 1 and the units in rows 2 to 4, in columns A to J: Unit, Received, Amount, Deductions, Refund due, Move-out, Return-by, Settled on, Held now, Flag. Three settings sit to the right in column L, labelled in column K: the number of return-by days (L1), the deposit account balance from your bank statement (L2) and the difference (L3).
Refund due E2: =IF(F2="","",C2-D2)
Return-by G2: =IF(F2="","",F2+$L$1)
Held now I2: =IF(H2="",C2,0)
Flag J2: =IF(AND(G2<>"",H2="",TODAY()>G2),"Past return-by","")
Difference L3: =SUM(I2:I4)-L2
Start: 1,800 + 1,500 + 2,100 = 5,400 held; bank balance 5,400; difference 0
Unit 2 out: deductions 180 + 140 = 320; refund due 1,500 - 320 = 1,180
Settled: send 1,180, move the 320 you kept out of the deposit account,
type the date in Settled on
ledger 1,800 + 2,100 = 3,900; bank 5,400 - 1,180 - 320 = 3,900; difference 0If the difference is not zero, look for a deposit that arrived but was never logged, a refund sent without a Settled on date, kept money still sitting in the account (until you move the $320, the two figures are $320 apart), or bank fees or interest posted to the account.
The return-by date is yours to look up
The formula above adds a number of days that you type into one cell. This guide does not give you that number. Deposit return deadlines, what may be deducted, whether deposits must be held in a separate account and whether interest is owed all differ from state to state, and sometimes from city to city. Rules vary by state and city; check your state's landlord-tenant page, type the number of days it gives, and note beside the cell where you found it and the date. If the rule counts from a different event, or in business days, change the formula to match: =WORKDAY(F2,$L$1) adds business days.
What the IRS says about a deposit
Publication 527 (2025), under rental income, says: "Don't include a security deposit in your income when you receive it if you plan to return it to your tenant at the end of the lease. But if you keep part or all of the security deposit during any year because your tenant doesn't live up to the terms of the lease, include the amount you keep in your income in that year." It adds: "If an amount called a security deposit is to be used as a final payment of rent, it is advance rent. Include it in your income when you receive it." Source: IRS Publication 527.
In ledger terms: a deposit you return in full never becomes income. An amount you keep is income in the year you keep it, so the Deductions column, grouped by the year of the Settled on date, is the figure to give your preparer. A deposit that is really the last month's rent belongs in your rent records, not in this ledger. How to treat the repair cost the deduction relates to is a separate question for a qualified preparer.
What this does not cover
The ledger records what you decided; it does not tell you what you are allowed to deduct, and it does not write the itemised statement or the notice to the tenant. It has no interest calculation. If you pool deposits in an account that also holds other money, the reconciliation will not close, so the check only works for an account used for deposits alone.
Sources
More landlord guides
- Schedule E expense categories spreadsheet: sorting receipts into lines 5-19
- Rental property vacancy rate spreadsheet: physical vs economic vacancy
- Rental property income and expense spreadsheet: a 12-month duplex log
- 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
Already built
The Deposits tab of the Landlord Rent & Expense Tracker records the deposit held, deductions, refund due and a return-by date that you enter yourself, and reconciles total held to a bank-balance input. No state's deadline is built in. It is one Excel file, $29 one time, with no bank link and no account.