Rent Roll Template: The Snapshot Lenders and Buyers Read

The document

What a rent roll template is, and what it is not

A rent roll template lists every unit in a property on a single date: who is in it, on what lease, at what rent, what deposit you hold, and what they owe. It is the page a buyer asks for before making an offer and the page a lender asks for before sizing a loan, because it answers one question quickly. What does this building earn right now, and how much of that is secure?

What it is not is a rent ledger. A ledger is a history: every charge and every payment, tenant by tenant, month after month. The roll is a photograph taken from that history on one day. Most free templates blur the two, adding a column per month of payments to the right of the unit list, and the result is a sheet that is too wide to read and still cannot say what the building looked like on the first of the month.

Keep them apart. The ledger feeds one column on the roll, the balance due, and everything else on the roll comes from the lease.

The date

Why the as-of date is the most important cell

Every figure on a rent roll is true on one day and wrong a month later. Leases end, tenants pay, a unit goes vacant. So the roll needs a single as-of date in a cell of its own, and every date calculation on the sheet should read from that cell rather than from today.

That second part is the one templates get wrong. A days-left column built on the current date changes every time the file is opened, so the roll you sent a lender on the first says something different when they open it on the ninth. Point the arithmetic at the as-of cell and the document stays a record: open it in a year and it still shows the building as it stood on that date.

The columns

The columns a rent roll template needs

One row per unit, not per tenant. A unit exists whether or not anyone lives in it, and that is the point. The columns that earn their place:

The example at the top of this page runs to eight units at a fictional building, dated 1 September 2026. Every number in the rest of this guide comes from that sheet, so you can check the arithmetic against it.

The gaps

Loss to lease and vacancy loss, the two numbers rolls leave out

A roll that shows only contract rent tells you what the building earns. It does not tell you what it could earn, and the gap is usually the most useful thing on the page. Split it in two, because the causes and the fixes are different.

Loss to lease is market rent minus contract rent, on occupied units only. In the example, the seven occupied units would let for $9,475 a month at market and are leased at $9,015, so loss to lease is $460. That is rent you are giving up to tenants already in place, often on purpose, since a long-standing tenant who pays on time is worth a discount. It is recovered slowly, at renewal.

Vacancy loss is the market rent of the empty units: $1,225 in the example, one one-bedroom. It is recovered all at once, by letting the unit. Keeping vacant units on the roll at market rent is what makes this number exist at all. Drop them off the sheet and occupancy reads 100 percent of what is listed, which is true and useless.

Drop the vacant units off the sheet and occupancy reads 100 percent of what is listed, which is true and useless.

Together they give you two occupancy figures worth putting in the summary row. Physical occupancy is units let over units owned: seven of eight, 87.5 percent. Economic occupancy is contract rent over the market rent of every unit: $9,015 over $10,700, about 84.3 percent. The second is lower because it counts both gaps, and it is the one that describes the money.

Deposits

Where security deposits go on a rent roll

In their own column, and never added to rent. A deposit is money you hold for the tenant and expect to give back, which is why the IRS says in Publication 527: “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.” If you keep part of it because the tenant breaks the lease, the same publication says to “include the amount you keep in your income in that year.”

One exception is worth knowing because it changes the column. The publication says that if an amount called a security deposit “is to be used as a final payment of rent, it is advance rent”, and advance rent is included in rental income in the year you receive it. So a last-month’s-rent payment belongs with rent, not in the deposit column, whatever the lease calls it.

The practical reason to sum the deposit column is that a buyer inherits it. In the example the building holds $9,015 in deposits, and that is a sum someone will eventually have to pay back.

Risk

Flags for expiring leases and arrears

Contract rent is only as secure as the lease behind it and the tenant paying it. Two columns show which rent is at risk, and both should be formulas so nobody has to remember to update them.

Days left is the lease end minus the as-of date, blank for a vacant unit and marked month-to-month where the lease has run on. We highlight anything at 60 days or fewer, which is when a renewal conversation should start, and set the status to expiring under 30. In the example that highlights unit 1A at exactly 60 days and marks 2B as expiring at 29.

Status then reads the row in a fixed order, first match wins: no tenant means vacant, no lease end means month-to-month, a balance above zero means in arrears, fewer than 30 days means expiring, and anything left is occupied. In a sheet with the as-of date in B2, that is one nested formula per row, and the $1,750 of balances in the example shows up as two coloured statuses rather than a number someone has to spot.

Building it

Building a rent roll in Excel or Google Sheets

Either works, and the same three habits keep the sheet honest in both. Put the unit list in a table so formulas extend to new rows: Microsoft documents that “by entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column”, which is exactly what a status column needs. Compute the summary row from the table rather than retyping totals. And save a copy of the roll each month under its as-of date instead of overwriting one file, so last quarter’s building is still on record.

The roll is also the front page of a small set of documents. Income and costs by property belong on a profit and loss statement, which our Excel profit and loss template guide walks through line by line. Owners who also run short stays will recognise the cost side from our Airbnb expense tracker guide.

A rent roll takes an hour to set up and five minutes to refresh on the first of each month, and it is the page that answers most questions anyone will ask about a building. Tell us in the comments how many units you track and what your roll gets wrong most often; we read every reply and it shapes what we build next.

FAQ

Common questions, answered briefly

People also ask

Other questions, briefly answered