Pro workbook: Units, Payments, Expenses, Monthly, Setup, Formulas. Sample data, real formulas.
| Rent roll | |||||||||||||||||
| One row per unit. Status, days to lease end and the renewal flag are calculated; Pro adds what each tenant has paid and owes. | |||||||||||||||||
| As of | 14 Sep | Units | 10 | ||||||||||||||
| Occupied | 8 | ||||||||||||||||
| Occupancy | 80% | ||||||||||||||||
| Monthly rent | 10,280.00 | ||||||||||||||||
| Property | Unit | Tenant | Rent | Due day | Lease start | Lease end | Deposit | Status | Days left | Flag | Paid this month | Due to date | Paid to date | Balance | Lost to vacancy | 2,230.00 | |
| 12 Oak Street | Oak 1A | R. Alvarez | 1,250.00 | 1 | 1 Mar | 28 Feb | 1,250.00 | Occupied | 167 | 1,250.00 | 11,250.00 | 11,250.00 | Ending in 90 days | 4 | |||
| 12 Oak Street | Oak 1B | M. Chen | 1,180.00 | 1 | 1 Jan | 31 Dec | 1,180.00 | Occupied | 108 | 10,620.00 | 9,440.00 | -1,180.00 | Collected this month | 6,200.00 | |||
| 12 Oak Street | Oak 2A | 1,250.00 | 1 | 0.00 | Vacant | Vacant | Outstanding | 4,580.00 | |||||||||
| 12 Oak Street | Oak 2B | S. Okafor | 1,300.00 | 5 | 1 Nov | 31 Oct | 1,300.00 | Occupied | 47 | Renew? | 1,300.00 | 11,700.00 | 11,200.00 | -500.00 | |||
| Mill House | Mill G | J. & P. Novak | 1,650.00 | 1 | 1 Jun | 31 May | 1,650.00 | Occupied | 259 | 1,650.00 | 6,600.00 | 6,600.00 | |||||
| Mill House | Mill 1 | T. Brennan | 1,450.00 | 1 | 1 Sep | 31 Aug | 1,450.00 | Occupied | -14 | Expired | 11,600.00 | 11,600.00 | |||||
| Mill House | Mill 2 | L. Haddad | 1,450.00 | 15 | 15 Feb | 14 Feb | 1,450.00 | Occupied | 153 | 10,150.00 | 7,250.00 | -2,900.00 | |||||
| 4 Harbour Row | Harbour 1 | K. Lindqvist | 980.00 | 1 | 1 Apr | 30 Sep | 980.00 | Occupied | 16 | Renew? | 980.00 | 5,880.00 | 5,880.00 | ||||
| 4 Harbour Row | Harbour 2 | 980.00 | 1 | 0.00 | Vacant | Vacant | |||||||||||
| 4 Harbour Row | Harbour 3 | A. Moreau | 1,020.00 | 1 | 1 Dec | 30 Nov | 1,020.00 | Occupied | 77 | Renew? | 1,020.00 | 9,180.00 | 9,180.00 | ||||
| Rent payments | |||||||||||
| One row per payment received. 'For month' is the rent month it covers — the first of that month. | |||||||||||
| Received this year | 72,400.00 | ||||||||||
| Received this month | 6,200.00 | ||||||||||
| Payments | 58 | ||||||||||
| Date | Unit | Amount | For month | Method | Note | Tenant | Rent | Short by | |||
| 3 Jan | Harbour 3 | 1,020.00 | Jan 2026 | Standing order | A. Moreau | 1,020.00 | |||||
| 3 Jan | Mill 1 | 1,450.00 | Jan 2026 | Cash | T. Brennan | 1,450.00 | |||||
| 3 Jan | Oak 1A | 1,250.00 | Jan 2026 | Standing order | R. Alvarez | 1,250.00 | |||||
| 3 Jan | Oak 1B | 1,180.00 | Jan 2026 | Bank transfer | M. Chen | 1,180.00 | |||||
| 3 Jan | Oak 2B | 1,300.00 | Jan 2026 | Bank transfer | S. Okafor | 1,300.00 | |||||
| 3 Feb | Harbour 3 | 1,020.00 | Feb 2026 | Standing order | A. Moreau | 1,020.00 | |||||
| 3 Feb | Mill 1 | 1,450.00 | Feb 2026 | Cash | T. Brennan | 1,450.00 | |||||
| 3 Feb | Oak 1A | 1,250.00 | Feb 2026 | Standing order | R. Alvarez | 1,250.00 | |||||
| 3 Feb | Oak 1B | 1,180.00 | Feb 2026 | Bank transfer | M. Chen | 1,180.00 | |||||
| 3 Feb | Oak 2B | 1,300.00 | Feb 2026 | Bank transfer | S. Okafor | 1,300.00 | |||||
| 3 Mar | Harbour 3 | 1,020.00 | Mar 2026 | Standing order | A. Moreau | 1,020.00 | |||||
| 3 Mar | Mill 1 | 1,450.00 | Mar 2026 | Cash | T. Brennan | 1,450.00 | |||||
| 3 Mar | Mill 2 | 1,450.00 | Mar 2026 | Bank transfer | L. Haddad | 1,450.00 | |||||
| 3 Mar | Oak 1A | 1,250.00 | Mar 2026 | Standing order | R. Alvarez | 1,250.00 | |||||
| 3 Mar | Oak 1B | 1,180.00 | Mar 2026 | Bank transfer | M. Chen | 1,180.00 | |||||
| 3 Mar | Oak 2B | 1,300.00 | Mar 2026 | Bank transfer | S. Okafor | 1,300.00 | |||||
| 3 Apr | Harbour 1 | 980.00 | Apr 2026 | Card | K. Lindqvist | 980.00 | |||||
| 3 Apr | Harbour 3 | 1,020.00 | Apr 2026 | Standing order | A. Moreau | 1,020.00 | |||||
| 3 Apr | Mill 1 | 1,450.00 | Apr 2026 | Cash | T. Brennan | 1,450.00 | |||||
| 3 Apr | Mill 2 | 1,450.00 | Apr 2026 | Bank transfer | L. Haddad | 1,450.00 | |||||
| 3 Apr | Oak 1A | 1,250.00 | Apr 2026 | Standing order | R. Alvarez | 1,250.00 | |||||
| 3 Apr | Oak 1B | 1,180.00 | Apr 2026 | Bank transfer | M. Chen | 1,180.00 | |||||
| 3 Apr | Oak 2B | 1,300.00 | Apr 2026 | Bank transfer | S. Okafor | 1,300.00 | |||||
| 3 May | Harbour 1 | 980.00 | May 2026 | Card | K. Lindqvist | 980.00 | |||||
| 3 May | Harbour 3 | 1,020.00 | May 2026 | Standing order | A. Moreau | 1,020.00 | |||||
| 3 May | Mill 1 | 1,450.00 | May 2026 | Cash | T. Brennan | 1,450.00 | |||||
| 3 May | Mill 2 | 1,450.00 | May 2026 | Bank transfer | L. Haddad | 1,450.00 | |||||
| 3 May | Oak 1A | 1,250.00 | May 2026 | Standing order | R. Alvarez | 1,250.00 | |||||
| 3 May | Oak 1B | 1,180.00 | May 2026 | Bank transfer | M. Chen | 1,180.00 | |||||
| 3 May | Oak 2B | 1,300.00 | May 2026 | Bank transfer | S. Okafor | 1,300.00 | |||||
| 3 Jun | Harbour 1 | 980.00 | Jun 2026 | Card | K. Lindqvist | 980.00 | |||||
| 3 Jun | Harbour 3 | 1,020.00 | Jun 2026 | Standing order | A. Moreau | 1,020.00 | |||||
| 3 Jun | Mill 1 | 1,450.00 | Jun 2026 | Cash | T. Brennan | 1,450.00 | |||||
| 3 Jun | Mill 2 | 1,450.00 | Jun 2026 | Bank transfer | L. Haddad | 1,450.00 | |||||
| 3 Jun | Mill G | 1,650.00 | Jun 2026 | Standing order | J. & P. Novak | 1,650.00 | |||||
| 3 Jun | Oak 1A | 1,250.00 | Jun 2026 | Standing order | R. Alvarez | 1,250.00 | |||||
| 3 Jun | Oak 1B | 1,180.00 | Jun 2026 | Bank transfer | M. Chen | 1,180.00 | |||||
| 3 Jun | Oak 2B | 1,300.00 | Jun 2026 | Bank transfer | S. Okafor | 1,300.00 |
| Expenses | ||||||||||
| Everything the properties cost: mortgage, tax, insurance, repairs. One row each. | ||||||||||
| Date | Property | Category | Amount | Note | Category | This year | Property | This year | ||
| 12 Jan | 12 Oak Street | Mortgage | 2,100.00 | January | Repairs | 1,545.00 | 12 Oak Street | 19,665.00 | ||
| 20 Jan | Mill House | Insurance | 1,240.00 | Annual policy | Mortgage | 18,900.00 | Mill House | 2,780.00 | ||
| 3 Feb | 12 Oak Street | Repairs | 340.00 | Boiler service 1B | Insurance | 1,240.00 | 4 Harbour Row | 3,930.00 | ||
| 12 Feb | 12 Oak Street | Mortgage | 2,100.00 | Property tax | 3,720.00 | |||||
| 12 Mar | 12 Oak Street | Mortgage | 2,100.00 | Utilities | 210.00 | |||||
| 18 Mar | 4 Harbour Row | Property tax | 1,860.00 | Half-year | Management | 600.00 | ||||
| 12 Apr | 12 Oak Street | Mortgage | 2,100.00 | Cleaning | 160.00 | |||||
| 22 Apr | Mill House | Repairs | 780.00 | Roof flashing | Other | |||||
| 12 May | 12 Oak Street | Mortgage | 2,100.00 | |||||||
| 30 May | Mill House | Cleaning | 160.00 | Mill G turnover | ||||||
| 12 Jun | 12 Oak Street | Mortgage | 2,100.00 | All | 26,375.00 | |||||
| 15 Jun | 4 Harbour Row | Utilities | 210.00 | Common areas | ||||||
| 12 Jul | 12 Oak Street | Mortgage | 2,100.00 | |||||||
| 28 Jul | 12 Oak Street | Repairs | 425.00 | Washer 2A | ||||||
| 12 Aug | 12 Oak Street | Mortgage | 2,100.00 | |||||||
| 19 Aug | Mill House | Management | 600.00 | Q3 agent fee | ||||||
| 12 Sep | 12 Oak Street | Mortgage | 2,100.00 | |||||||
| 2 Sep | 4 Harbour Row | Property tax | 1,860.00 | Half-year | ||||||
| Month by month | ||||||
| Rent due from the leases, rent collected, expenses and what's left — twelve months from the first month on Setup. | ||||||
| Month | Rent due | Collected | Collection | Expenses | Net | Occupied |
| Jan 2026 | 6,200.00 | 6,200.00 | 100% | 3,340.00 | 2,860.00 | 5 |
| Feb 2026 | 6,200.00 | 6,200.00 | 100% | 2,440.00 | 3,760.00 | 5 |
| Mar 2026 | 7,650.00 | 7,650.00 | 100% | 3,960.00 | 3,690.00 | 6 |
| Apr 2026 | 8,630.00 | 8,630.00 | 100% | 2,880.00 | 5,750.00 | 7 |
| May 2026 | 8,630.00 | 8,630.00 | 100% | 2,260.00 | 6,370.00 | 7 |
| Jun 2026 | 10,280.00 | 10,280.00 | 100% | 2,310.00 | 7,970.00 | 8 |
| Jul 2026 | 10,280.00 | 8,830.00 | 86% | 2,525.00 | 6,305.00 | 8 |
| Aug 2026 | 10,280.00 | 9,780.00 | 95% | 2,700.00 | 7,080.00 | 8 |
| Sep 2026 | 8,830.00 | 6,200.00 | 70% | 3,960.00 | 2,240.00 | 7 |
| Oct 2026 | 7,850.00 | 6 | ||||
| Nov 2026 | 6,550.00 | 5 | ||||
| Dec 2026 | 5,530.00 | 4 | ||||
| Year | 96,910.00 | 72,400.00 | 75% | 26,375.00 | 46,025.00 | |
| Rent due counts every unit whose lease covers the first of the month, at its current rent — leases that start mid-month count from the next month. | ||||||
| Months that haven't happened yet show rent due and no collection; the yellow tint is only meaningful for past months. |
| Setup | |||||
| The as-of date, the first month of the Monthly view, and the lists. | |||||
| As of | 2026-09-14 | Type =TODAY() to keep it live | |||
| Monthly view starts | Jan 2026 | ||||
| Count rent from | 1 Jan | Leases older than this are counted from here — the date you started logging payments | |||
| Methods | Expense categories | Properties | |||
| Bank transfer | Repairs | 12 Oak Street | |||
| Standing order | Mortgage | Mill House | |||
| Cash | Insurance | 4 Harbour Row | |||
| Card | Property tax | ||||
| Utilities | |||||
| Management | |||||
| Cleaning | |||||
| Other | |||||
| The unit list for Payments comes straight from the Units sheet, so a new unit is available there as soon as it has a name. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Units · Status | IF(B8="","",IF(C8="","Vacant","Occupied")) | A unit with no tenant is Vacant; the row is blank until it has a unit name. |
| Units · Flag | IF(I8="Occupied",IF(J8<0,"Expired",IF(J8<=90,"Renew?","")),IF(I8="Vacant","Vacant","")) | Days left = lease end − as of. Within 90 days: Renew?; past it: Expired. |
| Units · Paid this month | SUMIFS(Payments!$C$8:$C$207,Payments!$B$8:$B$207,B8,Payments!$D$8:$D$207,DATE(YEAR($B$3),MONTH($B$3),1)) | Payments for this unit whose 'For month' is the as-of month. |
| Units · Due to date | D8*MAX(0,(YEAR(MIN($B$3,G8))-YEAR(MAX(F8,Setup!$B$5)))*12+MONTH(MIN($B$3,G8))-MONTH(MAX(F8,Setup!$B$5))+IF(DAY(MIN($B$3,G8))>=E8,1,0)) | Rent × the number of due days passed from the later of the lease start and 'Count rent from' on Setup, stopping at the lease end. Plain date arithmetic, no DATEDIF, so it works everywhere. |
| Units · Balance | N8-M8 | Paid to date minus due to date. Negative — the tenant owes; positive — paid ahead. |
| Units · Outstanding | -SUMIF(O8:O47,"<0") | The negative balances added up, shown as a positive amount. |
| Payments · Short by | H8-SUMIFS($C$8:$C$207,$B$8:$B$207,B8,$D$8:$D$207,D8) | The unit's rent minus every payment logged for the same month — red when the month is still short. |
| Monthly · Rent due | SUMIFS(Units!$D$8:$D$47,Units!$F$8:$F$47,"<="&A5,Units!$G$8:$G$47,">="&A5) | Rent of every unit whose lease covers the first of that month. |
| Monthly · Expenses | SUMIFS(Expenses!$D$8:$D$127,Expenses!$A$8:$A$127,">="&A5,Expenses!$A$8:$A$127,"<="&EDATE(A5,1)-1) | Expenses dated within the month — from the first to the day before the next first. |
| Expenses · This year | SUMIFS($D$8:$D$127,$C$8:$C$127,G8,$A$8:$A$127,">="&DATE(YEAR(Setup!$B$3),1,1),$A$8:$A$127,"<="&Setup!$B$3) | Per category, from 1 January of the as-of year to the as-of date. |