What spreadsheet formulas can ChatGPT write for a rental rent roll?
Describe your columns and the answer you want, and your assistant can write the formula: a late-rent flag, days vacant, rent collected by property or leases ending in the next 90 days. Test each formula on three rows you can check by hand before you trust it across the sheet. If the sheet is becoming a system, consider moving to software that keeps the rent roll for you.
Give the assistant your columns
Formulas depend on layout, so start by describing it. Paste the header row and two sample rows, and say which sheet the data is on. For example: "Sheet Rent Roll, columns A Unit, B Tenant, C Rent, D Lease end, E Paid this month. Row 1 is headers." Without that the assistant has to guess the column letters, and a guess is the usual source of a wrong formula.
Say whether you use Excel or Google Sheets. Most formulas below work in both, but a few names differ.
Formulas worth asking for
| What you want | Formula to try (data in rows 2 to 50) |
|---|---|
| Total rent charged this month | =SUM(C2:C50) |
| Rent still unpaid | =SUM(C2:C50)-SUM(E2:E50) |
| Flag a tenant as late after the 5th | =IF(AND(DAY(TODAY())>5,E2<C2),"Late","") |
| Days until the lease ends | =D2-TODAY() |
| Leases ending within 90 days | =COUNTIFS(D2:D50,">="&TODAY(),D2:D50,"<="&TODAY()+90) |
| Rent charged for one property (property name in column F) | =SUMIFS(C2:C50,F2:F50,"Maple Street") |
Ask for the formula and for a one-sentence explanation of each part. If you cannot read a formula, you cannot spot when it is wrong.
Days vacant and what it cost
For a vacant unit, put the date the last tenant left in a column and ask for days vacant: today's date minus that cell. To show the lost rent, multiply days vacant by rent per day, which is monthly rent times 12 divided by 365.
Example a made-up unit rents for $1,200. Rent per day is $1,200 times 12 divided by 365, about $39.45. After 21 vacant days the lost rent is about $828.
Treat this as an estimate. Your books decide what counts as vacancy loss. See vacancy loss in your books.
Test before you trust
- Pick three rows with known answers: one paid on time, one late and one with a lease ending soon.
- Check each formula result against what you expect by hand.
- Copy the formula down the column and look at the first and last row. A formula that points at the wrong cell on row 2 will point at the wrong cell on every row.
- Save a copy of the sheet before you paste a new formula over existing work.
Common errors are a missing dollar sign that shifts a range when you copy it, dates stored as text so the math returns an error, and blank rows that skew a count.
Where a spreadsheet stops working
A rent roll in a sheet is accurate only if someone updates it. Payments arrive in the bank, not in the sheet. Once you have several properties, each with its own bank account and books, the sheet becomes a second job. Signs it is time to move: you re-type the same payments twice, two people edit one file, or you cannot say how a number was calculated.
The IRS guide to keeping records says records must support the income and expenses on your return. A sheet can do that if it is kept up, but software that records the transaction once is easier to defend.
How DoorKeeping handles it
If you already have a rent roll in a spreadsheet, you can ask your assistant to import it: you give it the file (CSV or Excel), it returns a preview with totals and any issues and changes nothing, and nothing is saved until you confirm. Unknown column names are mapped by AI. After that the rent roll, late rent and vacancy come from your books, so you ask a question instead of writing a formula. Rent stays in your own bank account, and anything involving money waits for your approval.
Sources
- IRS Publication 583: Starting a Business and Keeping Records, read Oct 30, 2026
- DoorKeeping: Connect Claude or ChatGPT (steps and tools list), read Oct 30, 2026
Questions
- Can ChatGPT build a whole rent roll spreadsheet?
- It can lay out columns and write the formulas, but you still have to enter and maintain the data. Test each formula on rows you can check by hand.
- Why does my formula return an error?
- Common causes are dates stored as text, a range that shifted when copied, or a blank cell. Paste the formula and the error into the assistant and ask for the cause.
- Excel or Google Sheets for a rent roll?
- Either works. Google Sheets makes sharing easier and Excel handles larger files. The formulas above work in both.
- Can the assistant read my spreadsheet directly?
- Some assistants accept a file upload. Remove tenant IDs and bank details first, and check results the same way you would any formula.