Rental Property Income and Expenses Worksheet
- 15'616 Downloads
- 480 KB File Size
- June 26, 2017 Updated
- 1 Number of comments
All business owners hope their enterprises will be profitable. You invest in your business to watch that investment grow. But to know whether or not your business is performing to your expectations, you need to maintain rental accounting records to track its progress.
For owners who rent real estate to others, the free Rental Property Excel spreadsheet is an excellent rental property accounting tool for tracking and understanding the business’s rental income, its expenses, and the net income that results from its business activities.
To make our template easily accessible and customizable, we’ve built it in Excel. To up your financial game and properly track your rental property income and expenses, you only need to download our template, open it, and enter your data.
First, note that the template is designed to be used by businesses renting either commercial or residential properties to others (hence the name, Rental Property Expense Worksheet). The business may rent out its own real estate as well as manage real properties for others. But, the worksheet does not account for real estate purchases or sales.
The spreadsheet is easily adaptable to other types of rental businesses. However, for the purposes of making examples and explanations, we’ll constrain our descriptions in this article to its use for tracking activities associated with rental real estate.
The template is easily completed. It can be used by the business’s owner or its property manager to quickly estimate income and/or profitability of either a single property, or of all properties owned or managed by the enterprise.
Note that this template does not track or report the business’s cash flow. That is another type of information and is available via other templates you can download here.
The spreadsheet is flexible, however. Although not designed to serve as the sole source of accounting information for the business, it can be modified to include any information you feel you need for your rental property bookkeeping.
By diligent use of this spreadsheet, the owner or their manager can follow the financial progress of the business as it moves through a twelve-month fiscal year. For our purposes, we’ve assumed that the business’s fiscal and tax year begins on January 1st and ends on December 31st. But the template can be adjusted for any twelve-month period desired.
How to Use Rental Property Income and Expenses Spreadsheet
There are fourteen tabs in this model. A “Summary” tab starts the sequence. A “Copyright” tab completes it.
Here is a blank Summary tab:
This tab provides columns for “Budget,” “Actual,” and then each month for the year, “January-December.”
The monthly (January-December) numbers will not need to be filled in on this tab. They will be pulled automatically from the monthly tabs we’ll discuss in a moment.
However, you will need to fill in your Company Name and the Year for which you are using the model at the top. If you prepare an annual budget, you may also enter that information in the appropriate column. But this column is entirely discretionary. Its use is up to you.
Also, you will need to enter the names of your Income and Expense sources in column C. See the image below:
These entries are the source for the names that are used in the monthly tabs, and assure continuity of naming across ensuing months.
The template is built for up to five sources of income and up to twenty types of expenses. The names of the most common Expenses have already been provided, and you can add the names of the Income items on the Summary tab. Note that there are several “Optional” rows available, which can be re-named for expenses such as Real Estate Property Tax or Sales Tax.
As already mentioned, our algorithms automatically pull the business’s monthly business activities into the Summary. There, you can see the results of the most recent month’s activities, as well as YTD (year-to-date) results. If you elect to use the Budget column, you can also review your YTD results against it to gauge your actual versus desired progress.
The next twelve tabs are used to record your monthly business activities. The blank tab below is for January:
To save on space, our example shows only the top part of the tab. However, the Income portion is formatted for up to fifty (50) different income categories. And the Expenses portion is formatted for two hundred (200) discrete expense categories. Each monthly tab is formatted identically to all the others.
Use the drop-down tabs available in the “Category” column to name your Income and Expense categories. Remember that these names are pulled from your Summary tab, so if you desire to adjust them, those changes will need to be done in the Summary. Then they’ll be available in the monthly Category tabs.
It’s best to maintain the same format, layout, and descriptive information on all twelve monthly tabs so that when information is pulled into the Summary tab, it’ll be correct.
Here are examples of the drop-down tabs in use. These are sections from the January 2018 Income/Expense Tab shown in complete profile below.
In each of these examples, we’ve used the drop-down tabs from the Category column in the January Income/Expense tab (full tab shown below) to select the appropriate name for the Income or Expense.
An important feature of the monthly tabs is the “Rental Period” section. Here you need to enter only the dates of the beginning and ending rental period, along with the per diem rental rate; the model will then calculate the rent due for you.
Other information specific to the individual property being rented is also available.
Finally, note that the Totals for Income and Expenses are displayed at the top of the tab. Check this total against your income and expense receipts to make sure that all transactions have been recorded.
Now let’s look at our example of the complete monthly tab for January 2018.
To enhance the image, we’re omitting columns and rows that are unused in our example. There are dozens of additional rows available for both Income and Expenses.
Our example has assumed that the company owns or manages five pieces of rental property. For convenience, we’ve also assumed that the daily rental rates for each are identical.
You can see that we’ve assumed that only three of the five units were rented during January and that those three units generated $2,850.45 in receipts or rental income from tenants.
Conversely, there were $2,825 in expenses. Some of these may fluctuate with the season, while others may remain stable throughout the year. In any case, the difference between Income and Expenses represents the Profit, provided on the Summary tab.
For expediency, we’ve only filled in the January tab. When used in real life, the other tabs would be populated as each month’s business activity occurred. And, by the end of the year, all twelve monthly tabs filed would be filled in.
In our example of a Summary tab below, we’ve assumed that the other monthly tabs have been filled in and that they’ve been pulled into the Summary as follows:
To make the example easier to view, we’ve “hidden” the Budget column and some of the Expense rows. All monthly tabs are detailed and then accumulated in the “Actual” column.
Also for clarity, we’ve broken the full-year example in two, and provide those larger views below:
First part of Example Summary
Second Part of Example Summary
From this Summary example, we can see that the business had between three and four units rented at different times during the year and that it’s Income reflected this variable activity.
Also, we see that while some expenses vacillated according to rental activity, other expenses remained constant.
The result of all these activities was that the business made a profit in some months while generating losses in others. The Net Income generated over the year was $3,850.64.
From these examples, you can see how this template will help the owner to better understand their business’s progress and profitability.
As mentioned above, this template is very useful for expense tracking, rental income tracking, as well as tracking of the rental property profits or rental property losses that results from its business activities.
However, keep in mind that it wouldn’t be appropriate to use an Expense Worksheet in every situation. Profit and Loss Statements, Cash Flow summaries, and Loan and Debt calculators all have their place in managing a successful business.
Calculations for income tax, taxable income, tax deductions, the business’s tax rate, and all other tax-related matters should be sourced from the appropriate financial statements or accounting software to assure accurate tax preparation of the business’s tax return and its supporting tax forms.
The Rental Property Income and Expense template provides you, the business owner, with valuable and timely information. Using it diligently will help you keep aware of your business’s financial health and progress, and allow you to better plan for your future.
We are sorry 🙁
Help us improve!
How we can improve this template?