Free Excel template · Excel for real estate

Free rent roll template for Excel

Q: How do I calculate occupancy rate in Excel?

Count occupied units with =COUNTIF(F2:F13,"Occupied") and divide by the number of units, =COUNTA(A2:A13). Format the result as a percentage.

For landlords and small property managers who need one page that answers the monthly questions: which units are empty, who still owes rent, how much the vacancies cost, and whose lease ends soon. List each unit once; the summary on the right updates as you change status and payments. Set the As of date and it counts the leases ending in the next 90 days.

cellwise-rent-roll-template.xlsxExcel workbook · 4.3 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	J	K
1	Unit	Tenant	Lease start	Lease end	Monthly rent	Status	Paid this month	Balance due	As of date	Nov 1, 2026
2	101	Maya	Dec 1, 2025	Nov 30, 2026	1325	Occupied	1325	0	Units	8
3	102	Luis	Mar 1, 2026	Feb 28, 2027	1295	Occupied	1295	0	Occupied	6
4	103				1350	Vacant	0	0	Occupancy	0.75
5	104	Aisha	Jun 15, 2026	Jun 14, 2027	1340	Occupied	1000	340	Rent if fully occupied	10755
6	201	Tom	Sep 1, 2025	Dec 31, 2026	1310	Occupied	1310	0	Rent from occupied units	8025
7	202	Nina	Jan 1, 2026	Dec 31, 2026	1360	Occupied	1360	0	Vacancy loss this month	2730
8	203	Omar	Aug 1, 2026	Jul 31, 2027	1395	Occupied	0	1395	Collected	6290
9	204				1380	Vacant	0	0	Outstanding	1735
10								0	Leases ending within 90 days	3
11								0
12								0
13								0

How the formulas work

Balance due per unit

H2=IF(F2="Occupied",E2-G2,0)

For an occupied unit it is the rent minus what has been paid this month; vacant units owe nothing. In the sample unit 104 has a partial payment and owes $340, and unit 203 has paid nothing yet.

Occupancy rate

K4=IF(K2>0,K3/K2,0)

Occupied units divided by all units. Status must be typed exactly "Occupied" or "Vacant"; to stop typos, select F2:F13 and use Data > Data Validation > List with Occupied,Vacant.

Vacancy loss this month

K7=K5-K6

Rent if every unit were let (K5 adds the rent column, including the asking rent on vacant units) minus the rent from occupied units. Two empty units in the sample cost $2,730 this month.

Leases ending within 90 days

K10=COUNTIFS(D2:D13,">="&K1,D2:D13,"<="&(K1+90))

COUNTIFS counts lease end dates on or after the As of date and no more than 90 days after it. Change K1 at the start of each month. With November 1 as the date, three leases end by January 30, which is time to plan renewals.

Make it yours

Keep one row per unit for good, even when it is empty. Vacant rows with an asking rent are what make the vacancy loss figure work.
Start each month by copying the sheet and clearing Paid this month. Last month's copy becomes your payment history.
Highlight balances: select H2:H13 and add Home > Conditional Formatting > Highlight Cells Rules > Greater Than, 0.
To see the leases themselves rather than a count, sort by Lease end (Data > Sort) or filter the column for dates in the next three months.

FAQ

What is a rent roll?

A list of every unit in a property with its tenant, lease dates, rent and payment status. Lenders and buyers often ask for one, and landlords use it to see occupancy and arrears at a glance.

How do I calculate occupancy rate in Excel?

Count occupied units with =COUNTIF(F2:F13,"Occupied") and divide by the number of units, =COUNTA(A2:A13). Format the result as a percentage.

How do I count leases expiring in the next 90 days?

Use COUNTIFS with two date conditions on the lease end column: =COUNTIFS(D2:D13,">="&K1,D2:D13,"<="&(K1+90)), where K1 holds the date you are counting from.

Functions used

Related guides

More free templates

Updated November 2, 2026