Rent Ledger Template for Excel

Generate a formatted Excel (.xlsx) rent ledger with live running-balance formulas, frozen headers, and a Schedule E summary tab, or download a blank 12-month workbook for Excel and Google Sheets.

Prefer a blank template to fill by hand or in Excel?
Tenant Rent Statement Balance clear
Starting period
Period Status Rent due Other Late fee Amount paid Date paid Method Balance Memo / Ref
Schedule totals
0.00
0.00
0.00
0.00
0.00
Opening balance $0.00
Total billed $0.00
Total collected $0.00
Ending balance due $0.00
Tab memory only (never uploaded)
.xlsx Workbook

Pre-built Excel formulas

Every monthly row calculates cumulative balances, dynamic Paid/Partial/Unpaid status rules, and SUM column totals automatically.

Blank Sheet

Blank 12-month template

Download an empty 12-month .xlsx workbook wrapped in COUNTA checks so blank rows stay clean without #VALUE! or zero clutter.

Sheets & CSV

Google Sheets & CSV import

Upload the .xlsx file to Google Sheets with all formulas and method breakdowns intact, or export UTF-8 CSV anytime.

Inside the two-tab Excel (.xlsx) workbook architecture

Most spreadsheet rent logs fail for structural reasons: inserting a mid-month partial payment shifts relative cell references out of alignment, or typing charges and credits into one mixed column makes annual tax totals tedious to extract. Every workbook exported from our rent ledger template for Excel uses a fixed ten-column accounting structure paired with a linked tax summary worksheet.

Column Header Type / Formula logic
A Period Month and year text label (for example Nov 2026).
B Status IF(...) status badge formula resolving to Open, Paid, Partial, Unpaid, or Credit.
C:E Rent Due, Other, Late Fee Separate positive charge columns formatted as $#,##0.00.
F Amount Paid Positive gross payment column summed into Schedule E cash receipts.
G:H Date Paid & Method ISO or calendar date plus standardized payment channel (ACH, Zelle, Check, etc.).
I Running Balance Cumulative formula adding prior balance plus C:E charges minus F payments.
J Memo / Ref Wrapped multi-line note column for check numbers, utility invoice IDs, or adjustments.

On the blank 12-month workbook downloaded from Download Blank Excel (.xlsx) above, each monthly row wraps the running-balance calculation inside =IF(COUNTA(A9,C9:F9)=0,"",SUM(E$5,C$9:E9)-SUM(F$9:F9)). Using SUM across range blocks rather than chaining + arithmetic prevents #VALUE! or cascading errors if you skip a row or leave optional columns blank. Sheet 2 (Summary & Tax Prep) links directly to Sheet 1 via cross-sheet references ('Rent Ledger'!C21) and computes collections by payment method with =SUMIF(...), giving bookkeepers an immediate breakdown across ACH, Zelle, Venmo, Check, Money Order, Cash, Card, and Portal deposits.

Importing into Google Sheets or extending CSV exports

Both the pre-filled Excel (.xlsx) export and the blank 12-month .xlsx file open cleanly in Microsoft Excel, LibreOffice Calc, Apple Numbers, and Google Sheets. To collaborate online with a co-owner, partner, or CPA in Google Sheets:

  1. Open Google Drive or a blank Google Sheet and select File > Import > Upload.
  2. Drop your downloaded .xlsx file into the uploader and choose Replace spreadsheet (or Insert new sheet(s) when adding a unit tab to an existing portfolio workbook).
  3. Confirm Row 7 remains frozen under View > Freeze so column headers stay anchored while scrolling longer multi-year tenancies.

If you prefer working from the lightweight CSV download inside desktop Excel, select cell A3, press Ctrl+T (or Cmd+T on Mac) to convert the ten-column block into a named Excel Table, and enable Total Row under Table Design so new monthly rows inherit formatting and balance formulas automatically. If you need a browser-ready printable schedule without opening spreadsheet software, return to our free rent ledger template to print directly or customize layouts in PDF Studio.

Multi-unit tab structure and prorated rent formulas

Portfolio owners with two to twenty doors often try to save time by placing every tenant on one chronological cashflow sheet. That layout works as a building rent roll, not as a tenant-level ledger, because an overpayment or unpaid late fee in Unit 2B immediately corrupts the cumulative figure shown for Unit 2A.

Duplicate one worksheet tab per active lease and label each tab by unit (418-2B-Rivera, 712-3C-Patel). When a tenant moves in or vacates mid-month, avoid guessing rounded fractions in your head: check whether your lease requires actual calendar days (=BaseRent/DAY(EOMONTH(StartDate,0))*OccupiedDays) or a 30-day uniform month (=BaseRent/30*OccupiedDays), paste the resulting dollar figure into column C (Rent Due), and record the formula basis in Memo / Ref. For detailed guidance on separating prorated charges, utility rebills, and security deposits across a full tenancy, review our tenant ledger guide.

Reconciling Zelle, Venmo, and bank CSV exports

Digital payment apps make collecting rent fast, yet a filtered Zelle or bank statement CSV cannot replace a master Excel rent ledger. Bank downloads list cash inflows chronologically; they never record charges billed, lease grace windows, or unpaid balances when a resident skips a pass-through water bill.

At the end of each month, open your Summary & Tax Prep worksheet tab alongside your checking account CSV export and verify each channel total (ACH, Zelle, Venmo, Check) row by row. When a bank total matches a method row on Sheet 2, you have verified both sides of your books: every dollar deposited is credited to a specific tenancy, and every uncollected charge remains visible in column I.

Frequently asked questions

Specific guidance on Excel workbook formulas, blank-row handling, and Google Sheets imports.

Do the Excel (.xlsx) exports include live formulas or static numbers?

Both the pre-filled and blank .xlsx exports contain live Excel formulas. Running balances reference prior carryover figures and period charges/payments, status cells recalculate Paid, Partial, or Unpaid states automatically, and the Summary & Tax Prep tab links totals with =SUM and =SUMIF formulas.

Why do blank rows in the downloadable Excel template stay empty instead of showing repeated balances?

The 12-row blank .xlsx workbook wraps each status and running-balance cell in an IF(COUNTA(A9,C9:F9)=0,"",...) check. Until you type a billing period or dollar figure on a row, Excel and Google Sheets keep the cell visually blank while updating totals immediately once you enter data.

What is the cleanest way to open this rent ledger template in Google Sheets?

Download either the blank or filled .xlsx file, open Google Sheets, choose File > Import > Upload, and select the workbook. Google Sheets converts both worksheets, retains currency formatting, and keeps every SUM and SUMIF formula live for sharing with a co-owner or bookkeeper.

How do I add a 13th month or extra rows inside Excel without breaking the running balance?

Insert a new row directly above the Schedule totals row rather than below it, then drag the Status and Running Balance formulas down one cell. Verify that the Schedule totals SUM ranges still include the newly inserted last data row before saving.

How should I organize Excel tabs when managing more than one rental unit?

Keep one workbook file per property or one worksheet tab per tenancy rather than stacking two tenants in the same vertical running balance. Dedicated sheets let you close a departing resident's balance cleanly while opening the next tenant's tab at $0.00.

How do I enter a prorated first or last month in Excel?

Calculate the daily rate according to your lease (for example =1850/30*12 or =1850/DAY(EOMONTH(date,0))*12), enter the result in Rent due, and document the day count in Memo / Ref so year-end tax reviewers understand why that row differs from base rent.

When should I switch from an Excel rent ledger template to property management software?

A free Excel or Google Sheets workbook handles small portfolios well when you collect rent via ACH, Zelle, or check and want local file ownership with zero subscription fees. Consider dedicated rental software such as Stessa, Apartments.com Rental Manager, Avail, or Baselane once you need automated online rent collection, automatic bank feeds, or tenant portal reminders across a larger portfolio.