Rent Roll Template for Microsoft Excel

When a landlord is renting out properties to tenants, he or she needs a way to keep track of the details of those rentals. The landlord often uses a document called a rent roll to record and report details like lease terms, rent due, and rent paid.

The rent roll template described in this post is an Excel workbook that tracks property layout and rental income, in addition to landlord expenses incurred as a result of property maintenance.

Get your copy of the rent roll template with the above link.

Rent Roll Template Main Menu

The rent roll template opens to a Main Menu sheet responsible for filtering the entire workbook. You can filter the workbook using the property, unit and/or lease dropdowns on the Main Menu. Once you select a filter, the Main Menu changes to reflect that filter selection.

To clear your selection from a dropdown, click the round red button to the right of the dropdown, as show above.

At the bottom of the Main Menu, one of two charts may appear based on your filter. By default, a bar chart of profit by property appears as a visual summary of overall performance. The Profit/Loss line is calculated from Income minus Expense, both of which are described in the Income and Expenses sections, respectively.

If you select a specific property from the dropdown, the chart changes to an income bar chart by unit, helping you visualize revenue within the property.

You can always navigate back to the Main Menu from any other sheet by clicking the Main link in the upper-left corner of the sheet.

Adding or Removing Properties

A property is a rentable structure, such as a house or building, that may be bought or sold by the landlord. Structurally, a property contains at least one unit, and each unit is capable of generating a rental income, as described in the Units section. A property can also be the source of landlord expenses, such as mortgage payments.

To create a property thru the template, first go to the Main Menu and ensure that there is no property selected by clicking its clear button, then click the Properties link that appears.

From the Properties sheet, click the New Property button. A new row appears at the bottom of the table where you can type the following details:

  • Property Name: The unique name of your property.
  • Type: The type of property, customizable by you.
  • Zoning / Use: How the property is municipally zoned.
  • Lot Size: The square footage of the lot.
  • Address Line 1: The mailing address line 1.
  • Address Line 2: The mailing address line 2, if applicable.
  • City: The city, town or municipality.
  • State / Province: For a US or Canadian property, select from the dropdown.
  • Zip / Postal Code: The zip, postal or equivalent code.
  • Property Manager: The person who maintains your property. See the Property Managers section.
  • Notes: A property description.

If your desired Property Manager does not appear in the Property Manager dropdown, click the Property Managers… button to create him as described in the Property Managers section.

Once you return to the Properties sheet, the new Manager will be available in the dropdown. When finished, click the Main link to return to the Main Menu.

To edit an existing property, go to the Main Menu and select the desired property, then click the Property link that appears.

When editing an existing property, notice that an Expenses… button appears, which allows you to track landlord expenses for this property, as described in the Expenses section.

Adding or Removing Property Managers

A Property Manager is a person hired by the landlord to manage the day-to-day maintenance of a property. Creating a complete list of all Property Managers is a good place to start in this template because they can then be easily selected upon property creation.

To create or edit a Property Manager, go to the Main Menu and click the Managers link.

From the Property Managers sheet, click New Property Manager or edit the desired row. The Property Manager details are as follows:

  • Property Manager: A required and unique full name.
  • Company: The company for which the Manager works.
  • Email: His email address.
  • Phone: His telephone number.
  • Notes: A description of the Manager.

When finished, notice that there is a Properties link near the top – this link takes you directly to the Properties sheet, in case you’d like to select a newly-created Manager for a property.

Adding or Removing Units

A unit is a rentable space contained within a property. A unit could be an apartment within a building, or even a kiosk within a shopping mall.

To create a unit thru the template, its property must first exist as described in the Properties section. From the Main Menu, select the desired property, then click the Units link.

From the Units sheet, click New Unit. In the new row that appears, enter the following unit details:

  • Suite/Unit #: The unique unit name within the property.
  • Unit Type: The type of unit, customizable by you.
  • Beds: The number of bedrooms.
  • Bath: The number of bathrooms.
  • Size: The square footage of the unit.
  • Amenities: A notable feature of the unit, customizable by you.
  • Notes: The unit description.

To edit an existing unit, go to the Main Menu, select the desired property and unit, then click the Unit link.

Adding or Removing Leases

A lease is an agreement of rental terms between a landlord and a tenant for a particular unit.

To create a lease thru the template, first ensure that a property and unit are selected on the Main Menu, then click the Leases link.

On the Leases sheet, click New Lease. On the new row, enter the lease details as follows:

  • Lease Start: Date on which the lease starts. This is required.
  • Mth-to-Mth: Select “Yes” for a month-to-month lease.
  • Lease End: Date on which the lease ends.
  • Free Rent Start: Date on which free rent starts.
  • Free Rent End: Date on which free rent ends.
  • Annual Increase: Percentage increase in rent due.
  • Pre-model rate: Monthly rent due before renovations.
  • Post-model rate: Monthly rent due after renovations.
  • Rent: Current monthly rent due.
  • Prepaid Rent: Prepaid rent due.
  • Additional Rent: Additional rent due.
  • Security deposit: Security deposit amount.
  • Repair & Maintenance: Amount to be paid by the tenant.
  • Tenant: The full name of the tenant. This is required.
  • Notes: A description of the lease.

To edit an existing lease , go to the Main Menu, select the desired property, unit and lease, then click the Lease link.

While editing an existing lease, notice the Income… button near the top of the sheet – clicking this button takes you to the lease’s income, as described in the Income section.

Adding or Removing Income

Income refers to any amount paid by the tenant to the landlord in accordance with a lease.

To view or record an income transaction in the template, go to the Main Menu, select the desired property, unit and lease, then click the Income link.

To record a new transaction, click New Income. Enter the income details as follows:

  • Income Type: The type of income, which is customizable.
  • Due On: Date on which the payment was due.
  • Paid On: Date on which the payment was made.
  • Paid: Amount paid by the tenant.
  • Notes: The transaction description.

Notice the Past Due amount near the top – this is the total rent due minus the total rent paid since the start date of the lease. For this calculation, the rent due and start date are taken from the Rent and Lease Start columns of the Leases sheet, respectively. The rent paid is taken from the Paid column on the Income sheet, where Income Type is “Rent”.

Adding or Removing Expenses

An expense is an amount incurred by the landlord for the maintenance of a property. An expense could be a mortgage payment, or perhaps an insurance premium.

To view or create an expense thru the template, go to the Main menu, select the desired property, then click the Expenses link that appears.

On the Expenses sheet, to create an expense, click New Expense. The expense details are as follows:

  • Expense Type: The type of expense, customizable by you.
  • Due: The expense amount due.
  • Due On: The date on which the expense was due.
  • Paid On: The date on which the expense was paid.
  • Paid: The expense amount paid.
  • Notes: A description of the expense.

Deleting Rows from the Rent Data

On most of the template sheets, a row can be deleted by clicking the delete button.

To delete a row, click your mouse anywhere within the row you’d like to delete. The delete button, displayed as a red dash (-), appears to the left of that row. If the row contains related data on another sheet, you’ll be prompted to delete that related data first.

Generating Reports from the Rent Data

The Reports sheet brings together data from all sheets into one rent roll report.

To use the Reports sheet, first go to the Main Menu and clear any selected property, then click the Reports link that appears.

From the Reports sheet, notice the Income $ and Expense $ columns. These columns draw from the Paid columns on the Income and Expenses sheets, respectively. The Profit $ column is calculated from Income $ minus Expense $.

As with any Excel pivot table, you can filter it for you own analysis, as needed.

Print Reports to PDF

The Print to PDF feature is available on most sheets of the template.

From the sheet, click Print to PDF and the sheet will be exported to a pdf file in a folder of your choosing.

General Rent Roll Template Settings

The Settings sheet facilitates customization of any dropdowns described in this post as “customizable”. Items specific to your business, such as expense types or income types, are customizable.

To go to the Settings sheet, first go to the Main Menu, then click the Settings link.

From the Settings sheet, edit any cells as desired. These customizations immediately affect the content of all corresponding dropdown lists.

Conclusions

The rent roll template is a good way to keep track of income and expense activity related to your rental portfolio.

If you would like to maintain quarterly or yearly snapshots, it might be a good idea to keep a workbook copy that excludes all the income and expense rows.

Re-use a copy of that workbook at the start of every quarter or year, entering income and expenses as they occur.

About the Author

Barry O'Brien

Barry O'Brien

Barry is a software development veteran with a degree in Computer Science and Statistics from Memorial University of Newfoundland. He specializes in Microsoft Office and Google Workspace products developing solutions for businesses all over the world.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃