In this article, I will provide you with all the steps you need to follow to create a rental properties spreadsheet in Google sheets that will manage your income and expenses. I will also provide a rental property spreadsheet template (free!) for you to utilize. Read on to learn how to make a spreadsheet for rental property in Google Sheets.
Table of Contents
Free Rental Property Tracking Spreadsheet
I created two different spreadsheet templates for rental properties. The first helps you track your income across multiple properties. The second tracks lease dates, so you know when to reach out to your tenants to renew (and how much the next year’s rent will increase).
Note that each spreadsheet works as a template. You just need to go to FILE > Make a Copy in either file. That will give you a copy you can edit with your own information.
What Is a Spreadsheet for Rental Property and Why Use One?
A rental property spreadsheet is a document that can store data, such as income and expenses directly related to your rental properties. It helps real estate investors track monthly, quarterly, or yearly income, expenses, and taxes. A rental spreadsheet provides professional clarity and makes management and tracking of cash flow easier.
A well-maintained rent spreadsheet will also automate clear communication between owners, investors, and tenants.
Such a spreadsheet may include an overview of the history of payments, late payments, all income (in a rental income spreadsheet), and expenses (rental property expenses spreadsheet), as well as the tenant’s record. In my second spreadsheet, I made a tracker for lease agreements. This tracks the start and end date for each lease in your portfolio, and it highlights those that expire within three months.
A rental property spreadsheet can act as a pedigree for information regarding payment records. A spreadsheet increases information transparency, and updating data in such a format will reduce the chance of uncomfortable conversations with tenants during their lease period.
If you are a real estate investor and making money through renting properties is your primary source of income, you need to have diligent management skills to achieve financial transparency in your investments. Making a Google spreadsheet is a perfect fit for such usage.
Summarizing data in one place will also evaluate whether your investment is reaching the goal or objective you desired when investing.
What usually goes in the spreadsheet will eventually depend on your requirements. The number and categories of heads will also depend on the number of properties you want to maintain the record for.
To create a rental property spreadsheet that will summarize all your transactions and tenant information, you need to determine what categories you will require to retrieve information from the layout efficiently. One advantage of using Google Sheets is that you can add multiple sheets in a single file, so all of your relevant data stays in one place.
Now let’s suppose you are an investor who has purchased an apartment complex to rent it out and maintain a stable income stream on an equal period basis.
You will need to formulate a spreadsheet that encompasses an Income and expense statement regarding the complex on a monthly basis and a record of your tenants, which includes all relevant information (Addresses, overdue payments, etc.).
The tracker shall maintain income and expense statements on one sheet, and the sheet will update tenant information on the other sheet.
The income and expense statement, as the name suggests, will include all income and expenses such as monthly rent, pet fare, maintenance expense, insurance charges, management fees, repair charges, etc.
You can customize the list of categories according to investors’ interests. The tenant information sheet typically includes the property address or apartment number, tenant’s name, contact information, monthly rental charge, bedroom count, expiry date, any late payments due, etc.
A Step-by-Step Guide
Let’s now dive into the step-by-step guidelines for making a rental tracking spreadsheet in Google Sheets. The spreadsheet we will build for your investment will track your income and expenses and generate an Annual Report that considers every single expense and provides an Annual Net Income.
I will use the spreadsheet below to show you how we can calculate different values can using this sheet. I have entered random rental incomes and monthly expenses for five properties in the example below.
Here’s how you can prepare such a sheet
Step 1: After entering all my data in the sheet, I first calculated the total income using the =SUM function.
In the screenshot above, I have used the function =SUM(B4:B5) to add the monthly rental and other charges and calculate the total monthly income for property 1. Once done, I dragged the function forward to all other cells.
Step 2: Next, I calculated the monthly expense using the =SUM function
In the screenshot above, I have used the function =SUM(B9:B12) to add all the monthly expenses and generate the total monthly expense for property 1. Then, I dragged the function forward to all other cells using the fill handle.
Step 3: Once I had calculated all monthly incomes and expenses, I generated the Annual Income for each property using the =MULTIPLY function.
In the screenshot above, I have used the formula =MULTIPLY(B6,12) to multiply the monthly income for property 1 by 12 to generate the Annual Income. After that, I dragged the formula forward to all other cells to generate the annual income for each property.
Step 4: Next, I calculated the annual cost for each property using the =MULTIPLY function.
In the screenshot above, I have used =MULTIPLY(B13,12) to multiply the monthly cost of property 1 by 12 to generate the annual charge and then dragged the formula forward to apply it to all other properties.
Step 5: After calculating the annual income and cost, I generated the Annual gross Income using the =MINUS function.
In the screenshot above, I have used =MINUS(B16,B17) to subtract the Annual cost from the Annual income to generate the Annual Gross Income for property 1. After that, I dragged the function forward to do the same for all other properties.
Step 6: To calculate the taxation, I used the =MULTIPLY formula and used 0.25(25%) as a placeholder value.
In the screenshot above, I have used =MULTIPLY(B18,0.25) to generate a 25% tax (this value is an example value like all others) of the Annual Gross Income for property 1. Once done, I dragged applied the function to all other cells by dragging it.
Step 7: Finally, to calculate the Annual Net Income, I used the =MINUS function.
In the screenshot above, I have used =MINUS(B18, B19) to subtract the taxation + other costs from the Annual Gross Income to generate the Annual Net Income for property 1. Then, I dragged the function to all other cells to generate the Annual Net Income for each property.
And, done! That’s how simple it is to create a very efficient rental tracker spreadsheet on Google Sheets. Here’s the Google Sheets Rental Property Template that you can download.
Tenant Record Sheet
After completing the property management spreadsheet for rentals, you need a tenant record sheet to store tenant information against relevant properties. Here’s how you’d make it:
Step 1: Add all the heads for the information you need to record about your tenant.
Step 2: Under the description column, you can add similar data validation cells as you did in the previous sheets. For this:
- Click on the cell in the rental income sheet that has the data validation setting and press Ctrl+C.
- Now go to the tenant record sheet and click on the first 5 cells under the description column and right-click. Then select Paste Special then click on Data validation only. Your validation list is now good to go for selection.
Voila! You have just created a Rental Property Spreadsheet in Google Sheets. Want to make the same spreadsheet for rental properties in Excel? Just click File > Download. Then, choose the Microsoft Excel file type. That brings your worksheet offline, too.
Things to Keep In Mind
- It is important to always keep an unedited copy of your rental property template for future transactions.
- Data heads are customizable according to your choices.
- Refrain from overwriting in your formula cells in your rental property expense spreadsheet. They can automatically calculate the data for you; overwriting in those cells will generate inaccurate results.
- Maintaining different heads of expenses in different sheets is vital, so they are easy to track.
- Always create a new sheet in the existing spreadsheet, so your data stays in one place.
- Always ensure accurate data is filled in the cells, so the generated results are error-free.
- It is also essential to ensure the right formula is written in the formula bar.
Frequently Asked Questions
Here are some common questions I hear about my spreadsheets for rental properties.
How do I keep track of my rental property expenses?
A simple way to track your rental property expenses is to create a landlord spreadsheet in Google Sheets.
An Income and expense statement for rental property will help you keep track of all your inflows and outflows for relevant properties. Moreover, maintaining a separate sheet for all your expense heads will give you a better insight into outflows concerning relevant charges. You can then analyze which charges need to be looked after and which need to be managed more effectively.
How do I create a rental income spreadsheet?
First things first, to create a rental income spreadsheet, you need to determine the heads of income and expenses you usually counter while keeping track of inflows and outflows from your investment. Categorizing the inflows and outflows in different heads will help you maintain an error-free record that can be traced back to the relevant property heads.
In contrast, by maintaining data in a software tool like Google Sheets, you do not need to calculate whether your investment is profitable or not. The tool will automate the process for you. Read the step-by-step guideline at the start of the article to manage your rentals both effectively and efficiently.
Can I use the same spreadsheet to track security deposit payments?
You can certainly modify my spreadsheet templates to organize additional data. That’s true for operating expenses, security deposits, planned maintenance, or even regular cleaning. I also recommend tracking them in a similar way to where you track your mortgage payments. If you have mortgages on your properties, a spreadsheet can help visualize payoff dates.
Will these work for short-term rentals?
While I designed my landlord spreadsheets primarily for longer-term leases, it wouldn’t take much to modify them for short-term rentals. A spreadsheet for this purpose might include vacancy rates, cleaning schedules, and revenue goals.
Can I use this spreadsheet template at work?
If you work for a property management company or just have a portfolio of your own properties, please feel free to use these. I made them for free, and I’m happy to share. Of course, you may want something a little more powerful if you’re managing properties professionally. In that case, consider adding a cash flow analysis in a new tab. You should also consider depreciation expenses, current market values, and any planned down payments you plan to make for new property investments.
Essentially, time is money for everyone. To succeed in any field, you need to be an efficient manager. Time management is one of the best skills anyone can have. Therefore, for an investor, it is important to have one confined document that will add structure to this management.
Using a Google Spreadsheet for this purpose will help the landlords track the income and expenses and store relevant information about the tenants and the expenses head. This method of maintaining data gives you transparency across your stored content. A good thing about making these types of spreadsheets for rental property expenses from scratch is that you can edit data according to your requirements.
This article walked you through a guide on how to make a simple spreadsheet for renting properties and also provided you with a rental income spreadsheet template to use. Hope this helps you seamlessly manage your rental properties.