Income and work

A month-wise rent ledger for several tenants

A month-by-month rent ledger for landlords with several tenants: layout, formulas for balances and monthly totals, and a worked three-tenant example.

Editorial illustration for A month-wise rent ledger for several tenants

The easiest rent ledger for several tenants has one row per tenant per month, with columns for rent due, rent received, the date and mode of payment, and the balance. Totals by month and by tenant then come from a few SUMIFS formulas. You can see in seconds who has paid and who is behind.

What columns should the ledger have?

ColumnHeadingNotes
AMonthFirst date of the month, such as 1 Apr 2026
BTenant or flatUse a fixed label such as Flat 2A
CRent due in ₹As per the agreement
DReceived in ₹Amount actually credited
EDate receivedDate of the credit
FModeUPI, transfer or cash
GBalance in ₹Formula
HNotePart payment, late reason, reference

In G2 enter =C2-D2 and copy down. A positive balance means money is still owed to you.

What formulas total things up?

  • Rent received in a given month, with the month's first date in K1: =SUMIFS($D$2:$D$500, $A$2:$A$500, $K$1)
  • Rent due in the same month: =SUMIFS($C$2:$C$500, $A$2:$A$500, $K$1)
  • Outstanding for one tenant across all months: =SUMIFS($G$2:$G$500, $B$2:$B$500, "Flat 2A")
  • Rows still showing a balance: =COUNTIFS($G$2:$G$500, ">0")

These work in Excel and Google Sheets. Format column A as dates so that equal dates match.

What does a worked month look like?

Hypothetical April for three tenants:

TenantDue in ₹Received in ₹DateModeBalance in ₹
Flat 1A18,00018,0003 AprUPI0
Flat 2A22,00015,00012 AprTransfer7,000
Flat 3B14,50014,5001 AprUPI0

Due: 18,000 + 22,000 + 14,500 = ₹54,500. Received: 18,000 + 15,000 + 14,500 = ₹47,500. Outstanding: 54,500 − 47,500 = ₹7,000, which equals Flat 2A's balance. The totals agree, so the ledger is consistent.

Where does a partial or late payment go?

Record it on the row for the month it pays for, and use the Note column for the date and reason. If the tenant pays the balance later, add it to the same month's row by editing the received amount and noting the second date, or add a second row for the same tenant and month. Choose one method and use it every time. Tracking arrears across several months is a separate job from the layout, and it helps to keep the oldest months in sight.

What else belongs in the ledger?

  • Rent revisions, noted with the date from which the new rent applies
  • Maintenance or other amounts the tenant pays you, kept in separate columns so rent stays clean
  • A reference to the agreement, and its renewal date

Keep security deposits out of the rent columns, since a refundable deposit is not rent. Use a separate sheet for deposits.

Where does an expense tracker help?

A rent ledger records what comes in. Your property costs and household spending are in the same bank statement, and SpendixAI can take the statement upload, let you review the rows and categorise them, so you see rent credits next to the maintenance and repairs you pay. Whether and how rental income is taxed is for your CA, so keep the ledger complete for them.

Monthly routine

  1. Add the new month's rows for every tenant on the first.
  2. Fill received amounts and dates as credits arrive.
  3. On the last day, check balances and note anyone behind.
  4. Compare the total with the bank statement.
  5. Save a copy of the file with the month in its name.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports