Generating recurring rental income, profiting from property appreciation over the long term, and receiving tax benefits are 3 of the many reasons for investing in rental property.
A good landlord rental property Excel spreadsheet is a valuable tool for analyzing potential acquisitions, keeping track of tenant rent payments, and monitoring rental property financial performance.
We’ll explain why landlords use rental property spreadsheets, then discuss creating a rental property spreadsheet and an easy and free way to keep track of all aspects of a rental property automatically.
Key takeaways
- Many landlords create rental property spreadsheets in Excel to analyze potential investments, compare ownership costs, and monitor rental property financial performance.
- Creating a rental property spreadsheet requires time and knowledge of how Excel works, but there are good options for downloading a free Excel spreadsheet.
- Free online rental property software that automatically tracks income and expenses is a good alternative to manually entering data on a spreadsheet.
Benefits of using a spreadsheet for rental property
Let’s begin by looking at some of the reasons why landlords use spreadsheets for rental property.
Track potential investments
Remote real estate investors can pick and choose rental property to purchase in some of the top real estate markets in the country. A spreadsheet can track potential acquisitions and locations.
Compare potential investments
Each piece of real estate is different—even homes located within the same neighborhood. Logging data such as list price, price per square foot, neighborhood rating, and property taxes help you make an apples-to-apples comparison before writing up an offer.
Analyze ownership costs
A rental property requires ongoing maintenance to keep the home safe and habitable for tenants. When analyzing the costs of owning a rental, some factors to consider include the cost of repairs required at closing (if any); future capital expenses, such as replacing the heating, ventilation, and air conditioning (HVAC); and landlord insurance rates, which may vary among similar properties.
Consolidate information
A tenant rent roll in a single spreadsheet can consolidate information for each tenant and property, including tenant information like contact details, lease beginning and ending dates, monthly rent amount, and security deposit.
Track maintenance expenses
Stay on top of routine repairs and maintenance, such as the type of work done (unclogging a toilet, servicing an HVAC, etc.), the date the work was completed, and the name of the handyperson or contractor who did the job.
Generate income and expense data
Collect data needed to generate a profit and loss (P&L) statement, including monthly rental income collected, tenant application and late fees, property management and leasing fees, operating expenses, and mortgage interest deductions.
How to create a rental property spreadsheet
A rental property spreadsheet is a comprehensive report that monitors the financial performance of a rental property. There are 6 steps for creating a landlord rental property spreadsheet:
1. Rental property information
This section of the spreadsheet contains information about the rental property:
- Real estate market in which the property is located
- Name of property (often used with multifamily buildings)
- Type of property (single-family rental (SFR), multifamily, short-term rental, etc.)
- Property address
- Property tax parcel number
- Building size and lot size (square feet)
- Number of bedrooms and bathrooms
- Other notes (including garage or carport, outdoor deck, tool shed, etc.)
2. Mortgage data
Entering mortgage data helps you monitor owner’s equity and decide when and if to refinance and pull cash out to use as the down payment for an additional real estate investment:
- Lender name and contact info
- Mortgage account number
- Type of loan (conventional, Federal Housing Administration or Veterans Affairs, private lender, etc.)
- Loan amount
- Down payment
- Interest rate
- Interest rate type (fixed or adjustable)
- Loan term
- Amortization period (normally the same as the loan term for residential mortgages)
- Outstanding loan balance (used to determine owner’s equity by subtracting from the current market value of the property)
- Other notes (such as prepayment penalty and whether the loan can be assumed by a buyer)
3. Acquisition cost
Data in this section is used to calculate financial metrics, such as cash-on-cash return, cap rate, and depreciation expense:
- Total home purchase price
- Purchase price per unit (for multifamily buildings)
- Value of lot or land included in the purchase price
- Down payment amount (if the property was financed)
- Closing costs that are tax deductible up front (including prepaid mortgage interest and mortgage points)
- Closing costs that are added to cost basis (such as legal and recording fees and owner’s title insurance)
- Renovations, repairs, and capital improvements made at the time of purchase
- Total initial investment
- Total cost basis (used to calculate depreciation)
4. Income
A rental property spreadsheet can be designed to track rental income on a monthly and annual basis. Income data in this section of the spreadsheet is used to calculate return on investment (ROI) metrics, including net operating income (NOI) and cash-on-cash return:
- Potential rental income (assumes property is rented 100% of the time)
- Vacancy factor percentage (to allow for periods when the home is vacant due to tenant turnover)
- Vacancy loss (determined by multiplying potential rental income by vacancy factor)
- Gross rental income (subtract lost rental income due to vacancy from potential rental income)
- Additional rental income (from sources such as pets, roommates, or appliance rent)
- One-time income (such as tenant application fees)
- Gross operating income (gross rental income plus additional income sources)
Multifamily landlords may include data for each unit, including potential rental income and average rent per unit.
5. Expenses
Operating expense items may differ from one rental property type to another. For example, tenants in SFRs generally pay their own utilities, while multifamily buildings sometimes have a master meter for water and sewer that is shared for the entire building.
Some common rental property operating expenses include:
- Advertising and marketing
- Leasing fees
- Property management fees
- Routine repairs and maintenance
- Supplies
- Pest control
- Landscaping
- Landlord insurance
- Homeowner association (HOA) fees
- Property taxes
- Professional service fees (such as for an attorney or certified public accountant)
- Mortgage interest
- Contributions to a capital expense (CapEx) account
6. Financial return metrics
Data entered on a rental property spreadsheet ties into calculations used to monitor the financial performance of a rental property. Common financial return metrics include:
- Cash flow calculated by subtracting operating expenses and mortgage payments (if applicable) from the total amount of rental income collected
- Cash-on-cash return as the percentage return of annual pretax cash flow compared to the amount of cash invested
- NOI determined by subtracting operating expenses (excluding mortgage payments and CapEx contributions) from the total rental income collected
- Cap rate as a percentage return calculated by dividing NOI by property purchase price or value
Where to find an Excel spreadsheet for your rental properties
Creating a landlord rental property Excel spreadsheet can be time-consuming and complicated, even for people who have a good grasp of how to set up a worksheet. Fortunately, there are several online sources for downloading various rental property spreadsheets for free:
Rental property analysis
Roofstock Academy, a premier online resource for learning the fundamentals of real estate investing, has a free Excel spreadsheet for program attendees analyzing rental property:
Rent roll
This article on Stessa contains a free rent roll template.
Income and expenses worksheet
This resource page on Stessa contains links to various income and expense worksheets around the internet.
Alternatives to using an Excel spreadsheet for rental property
A rental property spreadsheet is a must-have tool for landlords to keep track of financial performance. However, one of the biggest drawbacks is that data must be manually entered.
Stessa makes rental property finances simple by providing free software to automate income and expense tracking, create tenant rent rolls and real estate balance sheets, and quickly and easily generate critical financial reports, such as income and cash flow statements and CapEx reports.
Investors can track an unlimited number of portfolios and individual SFRs, residential multifamily buildings, and short-term vacation rentals. After signing up for a free account, simply enter some basic information for each rental property and tenant, link bank and mortgage accounts, and monitor financial performance online via the comprehensive owner’s dashboard.
Final thoughts
Staying laser-focused on the numbers is key to successfully owning and operating rental property. A landlord rental property spreadsheet is a good tool to use to monitor performance. Investors looking for an automated alternative to manually entering data on a spreadsheet can sign up for a free account with Stessa.
