Tenant Income & Expenditure Spreadsheet StepbyStep Instructions

This guide explains how to set up a clear, functional spreadsheet that tracks all sources of rental income and every category of expenditure for a tenancy. Using a single workbook you can monitor cash flow, calculate net profit, and generate reports for taxtime or monthly reviews.

Why a Dedicated Spreadsheet?

• Visibility: See at a glance how much rent you collect versus what you spend.
• Accuracy: Reduce errors by using formulas instead of manual calculations.
• Decisionmaking: Spot trends (e.g., rising utility costs) and plan rent increases or costsaving measures.
• Compliance: Keep a record that satisfies tax authorities and landlordassociation requirements.

1. Choose Your Tool

Any spreadsheet program will work Microsoft Excel, Google Sheets, LibreOffice Calc, or Apple Numbers. The instructions below use generic formulas that are compatible with all of them.

2. Create the Workbook Structure

Use three main sheets:

1. Dashboard Summary charts and key figures.
2. Income Record of every rent payment and other income.
3. Expenses Detailed list of all costs associated with the property.

2.1. Dashboard Layout

Place the following cells on the first sheet (A1B10 is a good start):

• Total Rent Received =SUM(Income!C:C)
• Total Other Income =SUMIF(Income!B:B,"Other",Income!C:C)
• Total Income =B2+B3
• Total Expenses =SUM(Expenses!D:D)
• Net Operating Income =B5-B6
• Expense Breakdown Insert a pie chart that references the category totals on the Expenses sheet.

2.2. Income Sheet Columns

A	B	C	D
Date	Type	Amount	Notes
20240101	Rent	1200	January rent paid on time

Column definitions:

• Date The day payment was received.
• Type Rent, Late fee, Pet fee, Parking, Other.
• Amount Numeric value (no currency symbols).
• Notes Optional details (e.g., cheque number, bank transfer reference).

2.3. Expenses Sheet Columns

A	B	C	D
Date	Category	Vendor	Amount
20240105	Mortgage	Bank XYZ	800

Typical categories (feel free to add more):

• Mortgage / Loan
• Property Tax
• Insurance
• Utilities (water, electricity, gas)
• Maintenance & Repairs
• Cleaning / Landscaping
• Management Fees
• Legal / Accounting
• Advertising
• Supplies (light bulbs, paint, etc.)
• Other

3. Enter Data Correctly

Each time you receive rent or pay a bill, add a new row to the appropriate sheet.

4. Build Key Formulas

1. Total Rent =SUMIF(Income!B:B,"Rent",Income!C:C)
2. Total Late Fees =SUMIF(Income!B:B,"Late fee",Income!C:C)
3. Category Totals (Expenses) In the Expenses sheet create a small table (e.g., G1H10) where column G lists each category and column H uses =SUMIF(B:B,G2,D:D) to total it.
4. Monthly Net Income On a new sheet called Monthly, list months in column A, then use =SUMIFS(Income!C:C,Income!A:A,">="&DATE(2024,ROW()-1,1),Income!A:A,"<"&EDATE(DATE(2024,ROW()-1,1),1)) for income and a similar SUMIFS for expenses. Subtract to get net.

5. Visualise the Data

Charts make the information easier to digest:

• Income vs. Expenses Insert a clustered column chart with months on the Xaxis, two series (Income, Expenses) on the Yaxis.
• Expense Breakdown Use the category totals table to create a pie or donut chart.
• Cashflow Trend A line chart showing cumulative net income over the year.

6. Automate Repetitive Tasks

Consider the following shortcuts:

• Data Validation In the Type column of Income, create a dropdown list (Rent, Late fee, Pet fee, Parking, Other). Do the same for Category in Expenses.
• Conditional Formatting Highlight overdue rent: apply a rule on the Income sheet where =AND(B2="Rent", TODAY()-A2>30) turns the cell red.
• Templates Save the completed workbook as a template, then copy it for each new property you manage.

7. Review & Reconcile Monthly

Set a recurring calendar reminder (e.g., the 5th of each month) to:

1. Verify that every tenant payment has been entered.
2. Match each expense entry to a receipt or invoice.
3. Check that the totals on the Dashboard match bank statements.
4. Update charts so stakeholders have uptodate visuals.

8. Export & Archive

At the end of each fiscal year:

• Export the workbook as a PDF for a quick reference.
• Save a copy of the Excel/Sheets file in a secure, backedup location (cloud storage, external drive).
• Create a YearEnd sheet summarising the years totals and any comments on unusual items (e.g., a major repair).

9. Common Pitfalls & How to Avoid Them

• Missing entries Use the Reminder note on the Dashboard to flag incomplete months.
• Mixing currencies Keep all amounts in the same currency; if you ever need to convert, add a separate column for the conversion rate and a calculated column for the basecurrency value.
• Overcomplicating categories Start with a short list; you can always split a category later using the Split function.
• Formula errors Name your ranges (e.g., IncomeAmt) to make formulas easier to read and audit.

10. Quick Checklist (PrintFriendly)

Copy the list below and keep it on your desk.

• Enter every rent receipt on the Income sheet.
• Record all bills, receipts, and fees on the Expenses sheet.
• Update the Dashboard totals after each entry.
• Verify that the Net Operating Income matches your bank balance.
• Run the conditional formatting check for overdue rent.
• Produce monthly charts for the landlord meeting.
• Back up the file before the month ends.

Conclusion

A wellstructured spreadsheet gives landlords real insight into the profitability of their rental properties. By following the steps abovesetting up separate sheets, using consistent formulas, and reviewing data each monthyoull maintain accurate records, simplify tax preparation, and make smarter decisions about rent adjustments and expense control. Start simple, then expand the workbook as your portfolio grows.