2024 Rental Property Analysis Spreadsheet [Free Template]

An effective rental property analysis spreadsheet consolidates all income and expenditure data for a property into a centralized platform, allowing your to create projections of potential profitability for each real estate venture.

Within this article, we guide you through the process of creating your personalized rental property analysis spreadsheet. And explore the important role of cash flow in the realm of real estate investment.

Plus, download our complimentary rental property analysis spreadsheet which you can tailor to suit your specific needs within your real estate enterprise.

How to Create a Rental Property Analysis Spreadsheet

Employing a spreadsheet simplifies updates when assessing multiple investment options, aiding in your decision-making process.

The first step is to choose the platform you want to build the rental property analysis spreadsheet on. For example, Google Sheets, Microsoft Excel, LibreOffice Calc, or Open Office Calc. 

To create your own property analysis spreadsheet follow these four key steps:

1. Estimate fair market value

The first bit of data you’ll need when building your rental property analysis spreadsheet is the fair market value of the property. To determine the fair market value of a rental property you can employ various estimation techniques. 

Create a Comparative Market Analysis (CMA). This is similar to the process appraisers do. By utilizing recent sales data, active listings and unsold properties you can create a comparison value of similar properties in the area. Real estate agents and online platforms like Zillow and Realtor.com can assist in this regard.

Once market value is ascertained, you need to determine how much rent you would need to charge to achieve your financial targets. 

Utilize financial metrics such as the Capitalization Rate (Cap Rate) and Gross Rent Multiplier (GRM) to evaluate potential returns. Cap Rate derives the potential rate of return from Net Operating Income (NOI), while GRM relates the property's fair market value to its gross rental income. If the numbers don’t work then the property is too expensive.

If you can’t find any current rental data, employ the 1% Rule to estimate monthly rent. Multiply the property's asking price or market value by 1% (e.g., $150,000 market value corresponds to a minimum monthly rent of $1,500).

2. Forecast operating expenses

After gauging the property acquisition cost, you’ll want to estimate the overall ownership and operating expenses. 

Typical operating costs for single-family rentals and smaller multifamily units may include:

• Leasing fees, 
• Property management, 
• Maintenance, 
• Landscaping, 
• Utilities, 
• Capital expense (CapEx) reserve contributions, 
• Property taxes, 
• Insurance, 
• HOA fees, 
• Mortgage payments (principal and interest).

If the property is currently tenanted, request the seller's income and expense statements from recent years, but validate this information through discussions with local property managers or fellow investors. Sellers may occasionally omit key expenses like management fees or CapEx contributions to present lower operating costs.

If the property is vacant, you can estimate operating expenses using the 50% Rule, assuming that approximately half of the monthly rental income, excluding the mortgage, covers expenses. For instance, a $1,200 monthly rent suggests around $600 in monthly operating expenses based on the 50% Rule.

3. Determine market rents

While it's tempting to project high rental income due to robust demand, err on the side of caution when determining market rents during initial property analysis. Conservative rent estimates are prudent.

If conservative rental income covers operating expenses, explore scenarios with rents closer to market rates. Conversely, if profitability is marginal at peak market rents, reconsider the investment's risk level.

A practical approach to assessing market rents is to contact landlords or leasing agents as if you were a prospective tenant. Utilize online resources such as Zillow Rent Estimates and Rentometer to gather rental information.

4. Project repair, rehab, and updating costs

For properties necessitating immediate repairs or upgrades, seek quotes from local contractors via your property manager to compare bids. Verify if local building codes mandate licensed contractors or permit more cost-effective handyman services.

During your ownership, consider selective updates to enhance property value and rental income. Prioritize projects that promise the best returns on your investment in improvements.

Download Free Rental Property Analysis Spreadsheet

Landlord Studio has put together a simple rental property analysis spreadsheet using Excel/Google Sheets

Why It’s Important to Focus on Some Cash Flow

Buy-and-hold investors in real estate generate their profits in two main ways: through consistent rental income throughout the entire holding period and the potential of long-term property appreciation.

Historical data from the Federal Reserve illustrates that since the 1960s, the median sales prices of homes have generally trended upward. For instance, between the last recession in 2008/2009 and Q1 2021, median sales prices have surged by approximately 66%. However, during the onset of the recession, from Q1 2007 to Q1 2009, home prices dipped by roughly 20%.

Given the cyclical nature of real estate markets, investors should prioritize analyzing the cash flow potential of real estate investments instead of attempting to time the market. Properties with similar attributes typically exhibit similar future appreciation potential. Additionally, the possibility of equity gains is most significant toward the end of the loan term due to a higher percentage of mortgage payments being allocated to the principal during those final years.

In cases where the potential equity gain between two properties is relatively comparable, the one with the greater cash flow generally emerges as the superior investment choice.

How to Calculate Cash Flow

Cash flow is the difference between the overall income and expenses, excluding depreciation expenses (a non-cash deduction) and personal income tax. When evaluating the potential cash flow of a rental property, it's crucial to avoid overestimating income while underestimating expenses. Once you've prepared your cash flow or profit and loss statement, it's advisable to conduct various 'what-if' scenarios to gauge how cash flow fluctuates under different circumstances.

For instance, consider whether the property remains cash flow positive if rental income decreases. Assess how varying vacancy rates impact property cash flow. Additionally, try to determine the time required to regain a positive cash flow position if major capital repairs, such as replacing the heating and cooling systems or the roof, become necessary.

Here are the typical components included in a cash flow analysis:

1. Gross Annual Rental Income: This is the potential income achievable with full occupancy.
2. Vacancy/Credit Loss: Represents the potential income loss during property vacancies, often estimated at 5% of gross annual rental income.
3. Effective Gross Rental Income: Calculated as the difference between gross annual rent and vacancy/credit loss.
4. Leasing Fees: Charges incurred each time the property is rented to a new tenant, typically equivalent to one month's rent.
5. Property Management Fees: Generally range from 8% to 12% of the gross monthly rent collected.
6. Repair and Maintenance Costs: Cover expenses like electrical outlet repairs or plumbing leak fixes, typically around 10% of monthly rent collected.
7. Utilities: For small multifamily buildings, landlords may include water, sewer, and gas costs in the rent charged to tenants.
8. Property Taxes: Vary by state, typically ranging from approximately 0.5% to over 2% of the property value, contingent on the rental property's location.
9. Insurance Premiums: Encompass homeowners insurance along with additional landlord insurance coverage for rental use.
10. HOA Fees: Include monthly dues and special assessments if the homeowners association lacks sufficient reserves for major repairs like street repaving or pool resurfacing.
11. Other Expenses: These cover landscaping, pest control, snow removal, and business and travel expenses.

Net annual cash flow is derived by subtracting all operating expenses from the effective gross rental income.

You might like: 11 Major Tax Deductible Expenses For Rental Properties

Other Financial Metrics to Analyze

Astute real estate investors consistently enhance their decision-making by juxtaposing the outcomes of their rental property analysis with other key financial metrics, thereby facilitating a comprehensive evaluation of different investment choices:

Cap Rate

Formula: Cap Rate = NOI (Net Operating Income before mortgage debt service) / Property Value

Purpose: Evaluate the property's potential return on investment based on its operating income relative to its value.

Cash-on-Cash Return

Formula: Cash-on-Cash Return = Net Annual Cash Flow / Cash Invested

Purpose: Measures the return on the actual cash invested in the property, accounting for financing and operational factors.

Gross Rent Multiplier

Formula: Gross Rent Multiplier = Market Value / Gross Annual Rents

Purpose: Assesses the relationship between the property's market value and its total annual rental income.

Gross Rental Yield

Formula: Gross Rental Yield = Gross Annual Rent / Market Value

Purpose: Calculates the percentage return generated by the property's annual rent in comparison to its market value.

Try our free rental yield calculator.

Rental Property Analysis: Final Words

We've delved into the intricacies of rental property analysis, cash flow management, and the main financial metrics that underpin sound investment decisions. However, the work doesn’t stop there. Once you’ve found the perfect property, and finally exchanged you then need to find tenants and then accurately track all of your income and expenses to ensure your property stays on track with your estimates.

Landlord Studio offers a lifeline in this regard, making it as easy as possible to keep accurate financial records for your rental property investments. Its user-friendly interface simplifies the task of tracking income, expenses, and tax-related matters. 

Landlord Studio's ability to sync with bank accounts and generate detailed financial reports not only saves time but also minimizes the risk of costly errors. It ensures that your records are always current, allowing you to adapt and strategize as market conditions evolve.

Achieve your financial goals with powerful accounting and property management tools designed for you.

You Might Also Like

• The Complete Guide: How To Categorize Rental Property Expenses
• How to Run a Successful Vacation Rental Investment
• 10 Best Rental Property Management Software for 2023
• 11 Real Estate Investing Metrics that Investors Need to Know
• 11 Creative Financing Strategies For Real Estate Investing