Spreadsheet formulas for a landlord rent roll (that handles early and late rent)

Most landlord spreadsheets I've seen are a list of payments and a SUM at the bottom. That works until a tenant pays November's rent on October 29th, or pays half, or you need Schedule E numbers per property in April. I built a rental property spreadsheet (Excel + Google Sheets, no macros) and these are the formulas that made it work.

Looking at a property before you buy? The free rental cash flow calculator gives cash flow, cash-on-cash, cap rate and break-even rent from a listing in a minute.

Rent belongs to a month, not a date

The Payments tab has a date column and a separate "For month" dropdown (Jan to Dec). Two hidden helper columns turn that into a year and month number:

Month:  =IF(B6="","",IFERROR(MATCH(F6,MonthNames,0),MONTH(B6)))
Year:   =IF(B6="","",YEAR(B6)+IF(AND(MONTH(B6)=12,F6="Jan"),1,
                      IF(AND(MONTH(B6)=1,F6="Dec"),-1,0)))

If "For month" is blank, the payment date's month is used. The year fix matters for the one case that breaks naive sheets: January rent paid on December 30th belongs to next year, and a late December payment made in January belongs to last year.

Named ranges (PayTen, PayAmt, PayYear, PayMon, PayType) keep everything after this readable.

The rent roll grid

The Rent Roll tab is one row per lease and one column per month. Each cell is:

=IF($B6="","",
  IF(SUMIFS(PayAmt,PayTen,$B6,PayYear,Yr,PayMon,3,PayType,"Rent")=0,
     IF(AND(DATE(Yr,3,1)>=DATE(YEAR(Start),MONTH(Start),1),
            DATE(Yr,3,1)<=End, DATE(Yr,3,1)<=TODAY()), 0, ""),
     SUMIFS(PayAmt,PayTen,$B6,PayYear,Yr,PayMon,3,PayType,"Rent")))

So the cell shows the rent paid for that month. If nothing was paid, it shows 0, but only when the lease covered that month and the month has started. Otherwise it stays blank, so months before move-in or in the future don't look like missed rent.

Colors from conditional formatting

Three formula rules on the grid, each compared to the lease's monthly rent:

Paid:     =AND(D6<>"", D6>=INDEX(Leases!$I$6:$I$65,ROW()-5))
Partial:  =AND(D6<>"", D6>0, D6<INDEX(Leases!$I$6:$I$65,ROW()-5))
Late:     =AND(D6<>"", D6=0, DATE(Yr,3,1)+Grace<TODAY())

Green, amber, red. The "Late" rule waits for the grace period from Setup, so rent due on the 1st doesn't turn red on the 2nd.

Lease status and "ending soon" on the dashboard

The Leases tab status column:

=IF(H6<TODAY(),"Ended",IF(G6>TODAY(),"Future",
  IF(H6-TODAY()<=60,"Ends in "&(H6-TODAY())&" days","Active")))

To list the leases ending soonest on the dashboard without FILTER or SORT (older Excel doesn't have them), a hidden helper column gives each ending lease a unique number:

=IF(LEFT(K6,4)="Ends", H6+ROW()/10000, "")

The end date is a whole number, so adding ROW()/10000 breaks ties between two leases ending the same day. Then the dashboard rows pull the 1st, 2nd, 3rd smallest:

=IFERROR(INDEX(LeaseTen, MATCH(SMALL(LeaseKey,1), LeaseKey, 0)), "")

SMALL ignores the blank text cells, and IFERROR hides rows when fewer leases are ending.

Schedule E per property

Each Schedule E line is one SUMIFS per property column:

Repairs:  =SUMIFS(ExAmt, ExProp, D$5, ExCat, "Repairs", ExYear, Yr)
Rents:    =SUMIFS(PayAmt, PayProp, D$5, PayYear, Yr)

The last line, "Other", catches any custom category you add on Setup, by subtracting the known categories from the property's total:

=SUMIFS(ExAmt,ExProp,D$5,ExYear,Yr)
 - SUMPRODUCT(SUMIFS(ExAmt,ExProp,D$5,ExYear,Yr,ExCat,KnownCats))

SUMIFS with a range as the criteria returns one total per category, and SUMPRODUCT adds them up, so nothing gets counted twice or dropped.

The finished sheet

It also has a dashboard (rent collected, NOI, cash flow after mortgage, occupancy, past-due), cap rate and cash-on-cash per property, and a monthly cash-flow chart. It's on Payhip and Gumroad for $9 if you'd rather not build it yourself. It's a bookkeeping tool, not tax advice.