How to Manage Rental Property in Excel

If you rent out one apartment, you can keep the details in your head. By the third, or when a garage and a small office join the portfolio, questions begin to pile up. Did the tenant in unit 2 pay for October? When does the office lease end? Which unit has been empty for two months?

A small, well-connected spreadsheet answers all of this. The trick is to separate properties, tenants, leases and payments, and let formulas work out the balance.

Four sheets

1. Properties: name or address, type, standard rent. Status (rented or vacant) is calculated.
2. Tenants: name and contact details.
3. Leases: property, tenant, start date, end date, monthly rent, payment day, deposit.
4. Payments: date, lease number, amount.

A lease ties a property to a tenant for a period. Payments attach to leases, not to tenants, because one tenant may have two leases.

Step 1: How much has been charged?

Charges depend on how many months of the lease have started. Using DATEDIF with the start date and today (capped by the end date):

=(DATEDIF(StartDate,MIN(TODAY(),EndDate),"m")+1)*MonthlyRent

The +1 counts the current month as started, which means rent is charged from the first month on. If your leases prorate partial months, adapt the formula or enter a custom charge.

Step 2: How much has been paid, and the debt

=SUMIF(Payments!B:B,A2,Payments!C:C)

Debt is charged minus paid. A negative number is an overpayment, which is useful to see and not hide. Example: a lease started on July 1 at 900 USD a month. In October the charged amount is 4 x 900 = 3,600 USD. If 2,700 USD has been paid, the debt is 900 USD, one month's rent.

Step 3: Lease status and expiry

=IF(TODAY()>EndDate,"Ended",IF(EndDate-TODAY()<=60,"Ending soon","Active"))

Sixty days gives you time to talk to the tenant, advertise the unit or send a renewal. Set the threshold to suit local notice rules and your own routine.

Step 4: Vacant or rented?

A property is rented if an active lease exists for it today:

=IF(COUNTIFS(Leases!A:A,A2,Leases!D:D,"<="&TODAY(),Leases!E:E,">="&TODAY())>0,"Rented","Vacant")

Step 5: Summary for decisions

Occupancy is rented units divided by total units. Monthly income is the sum of the rent for active leases. Arrears is the sum of positive debts. With three of four units rented, occupancy is 75%. If the rent on the three occupied units is 900, 650 and 400 USD, monthly income is 1,950 USD, and the vacant unit with a standard rent of 700 USD means 700 USD of potential income that month is missing.

Common mistakes

• Mixing deposits with rent. Keep the deposit in its own column; it is not income.
• Recording payments by tenant name. Names change and spellings differ; use the lease number.
• No end dates. Without one you will never get an expiry warning.
• Overwriting rent after an increase. Add a new lease or addendum to preserve history.

Ready-made template: rental management

The template is built on this model. It is paid, works in Excel 2007 and later and in LibreOffice, and has no macros. You get a blank file and a filled-in sample with fictitious data.

• Properties: up to 100 properties with the status Rented or Vacant, tenant and rate.
• Tenants: contact details.
• Leases: up to 300 leases with dates, rent amount, payment day, deposit, amount charged, paid, debt and status.
• Payments: a log of receipts by lease number.
• Summary: occupancy, monthly income, arrears, upcoming expiries and a chart.

The debt is the amount charged for started months from the lease start minus the amount paid, and an overpayment shows with a minus sign. A lease is Ending soon when no more than the set number of days remain; the default is 60. The deposit is entered in the lease and stored. Calculations are approximate. It suits owners of apartments, offices, garages and warehouses, and small property management companies.

FAQ

Should I charge late fees in the spreadsheet?

You can add a column for them, but check local rules and your lease before charging. Keep fees separate from rent so balances stay clear.

How do I handle a rent increase?

Close the old lease line at the change date and start a new one at the new rate, so the history stays accurate.

Is a spreadsheet enough for taxes?

It gives you tidy records of income. Tax treatment depends on where you live, so check with a tax professional.

How should I keep lease documents?

Store scans in a folder and put the file name in the lease row. The workbook is the index.