You own several properties, each with several units, and you want to know without flipping through notebooks: which unit is vacant, who is behind on rent and what their phone number is, which contract is about to end, and how much each property earned this month and cost you. The free rental portfolio sheet answers these from six records you type: properties, units, tenants, contracts, payments and expenses.
The problem it solves
A one-row-per-tenant rent sheet is fine for one building, but it breaks with several properties: a unit changes tenants, one tenant rents two units, a contract ends and is renewed at a new rent. Here the pieces are separate: a property has units, a unit has contracts, a contract links a unit to a tenant, and a payment is logged against a contract. Each unit keeps its history and every number is calculated from its source.
From opening the file to the report
- Settings: today's date (or =TODAY()), how many days before a contract ends to warn you, and the month to report.
- Properties: one row per property with a code such as P01, a name and an address.
- Units: a code such as P01-01, its property picked from a list, type, area and listed rent.
- Tenants: one row per tenant with a code such as T001, a name and a phone.
- Contracts: pick the unit and tenant, then start, end, monthly rent and deposit.
- Payments: date, contract number, amount and the month it covers.
- Open Overdue, Renewals, Monthly report and Summary; they calculate themselves.
Worked example from the sample data
The names and numbers below are sample data inside the sheet, not real people, and today's date in it is 7 November 2026. It has three properties, eight units and six contracts:
- Contract C004 (a trading company, unit P02-02): ended on 31 October after 12 instalments worth 78,000, of which 58,500 was paid, so 19,500 is overdue. It is the first name in the Overdue list.
- Contract C001 (rent 5,000): 11 instalments due = 55,000, paid 45,000, so 10,000 overdue, two months.
- Contract C006 (rent 8,000): three instalments = 24,000, paid 16,000, so 8,000 overdue.
- Contract C003: paid 60,000 against 54,000 due, a balance of -6,000, meaning paid in advance; it is not counted as overdue.
- Total overdue in the example is 37,500, and 5 of 8 units are rented, an occupancy of 62.5%.
The Renewals list shows three contracts ending within 60 days: C002 in 37 days, then C001 and C006 in 54 days each. In the November 2026 monthly report the expected rent is 10,500 for P01, 6,000 for P02 and 17,000 for P03, while the 12,000 collected that month all came from P02, which shows that a month's collections can include an advance payment and need not equal the expected rent.
How the numbers are worked out
- Instalments due: one on the contract's start day each month, up to today or the contract end, whichever is earlier. A contract starting 15 January has its first instalment on 15 January and its second on 15 February.
- Overdue = instalments due x monthly rent - the contract's payments.
- Contract status: active, ending soon (within the warning days you set), expired, or not started.
- Unit status: rented while it has an active or ending-soon contract, otherwise vacant.
Using the online version
The sheet page opens on this example. Type in the white cells and the grey ones update at once; you can import your own xlsx, export your work, or download the original Excel file. Your data stays in your browser and is not sent to the server. More templates are in the templates list.
Common mistakes, and when the sheet is not enough
Frequent mistakes: a unit code in a contract that does not match the unit's own code (shown red as "unit not found"), renewing by editing the old contract's dates instead of adding a new contract so the old payments are lost, and starting a contract on the 29th to 31st. When collectors and staff need to work on the data together, a single file only lets one person write at a time; that is where a program with per-user permissions fits. If you own a single building, the simpler rent collection sheet is enough. For the follow-up routine itself read how to chase overdue rent and renew leases across several properties.
FAQ
How many properties and units fit? 50 properties, 500 units, 500 tenants, 500 contracts, 2,000 payments and 1,000 expenses.
How do I renew a contract? Add a new contract row for the same unit with its dates and rent; the old one stays expired with its payments.
Does it calculate late fees or yearly increases? No; type the increase in the new contract.