Download the Free Rental Property ROI calculator 👉 HERE and follow along.

Are you wondering if a rental property is worth the investment? Excel can help you find the answer!

In this guide, we’ll break down how to calculate the return on investment (ROI) for a rental property, step by step.

Whether you’re a real estate newbie or a seasoned investor, this method will help you make smarter decisions.

What is ROI in Real Estate?

ROI (Return on Investment) measures how much profit you make compared to the money you’ve invested.

For rental properties, it’s a simple way to see if your investment is paying off.

The formula for ROI is:

ROI = (Annual Profit ÷ Total Investment) × 100

Let’s use Excel to crunch the numbers and decide if a property is a good deal.

Example Scenario

You’re looking at a house selling for $200,000. If rented, it could bring in $1,400 per month. But is it worth it?

Let’s calculate.

Featured Course

Excel Essentials for the Real World

A hands-on Excel course for professionals who never had formal training. Fill in what got skipped, so you stop doing things the long way without knowing there was a shorter one.
Learn More

Calculate Average Monthly Cashflow on Rental Property

Step 1: Set Up Your Excel Sheet

  1. Open a New Workbook
    Create a new spreadsheet in Excel. Label these columns:
    • Category
    • Value
  2. Enter Basic Details
    Add these values:
    • Purchase Price: $200,000
    • Monthly Rent: $1,400
    • Down Payment (Equity): $40,000

Step 2: Calculate Your Total Investment

Your total investment includes more than just the purchase price. Add these costs:

  • Taxes & Fees (e.g., 0.5%) = =200000*0.005
  • Closing Costs (e.g., 1%) = =200000*0.01
  • Realtor Fees (e.g., 1%) = =200000*0.01
  • Rehab Costs (e.g., painting and kitchen updates): $5,000
  • Other Costs

In Excel, calculate the Total Investment:

  • Total Investment = Purchase Price + Taxes + Closing Costs + Realtor Fees + Rehab Costs + Other Costs

For this example:

  • Total Investment = $200,000 + $1,000 + $2,000 + $2,000 + $5,000 = $210,000

Step 3: Calculate the Financing Requirement

If you’re not paying the full amount upfront, you’ll need to determine how much financing you require.

Subtract your down payment (equity) from the total investment.

  • Financing Requirement = Total Investment – Down Payment

For this example:

  • Down Payment: $40,000
  • Financing Requirement = $210,000 – $40,000 = $170,000

This is the amount you’ll need to borrow from the bank.

Step 4: Estimate Monthly Loan Payments (Debt Service)

Your loan payment (or debt service) is one of the biggest monthly expenses for a rental property.

Use the PMT function in Excel to calculate this.

Here’s the formula:

=PMT(D16/12, D17*12, D12, 0, 1)

Explanation of the Formula:

  • D16/12: This divides the annual interest rate by 12 to get the monthly rate.
  • D17*12: This multiplies the loan term in years by 12 to get the total number of monthly payments.
  • D12: This is the loan amount (the financing requirement).
  • 0: This represents the future value, which is $0 because the loan will be fully repaid.
  • 1: This means payments are made at the beginning of each month.

For our example:

  • Annual Interest Rate (D16): 3.0%
  • Loan Term (D17): 25 years
  • Loan Amount (D12): $170,000

When you input this into Excel, the monthly payment is $804.

Step 5: Calculate Monthly Revenue

The monthly revenue is the money you earn from renting out the property. In Excel, include the following:

1. Base Rent (D22):
For this property, assume the monthly rent is $1,400.

2. Other Income (D23):
If you charge for amenities like parking, cleaning, or landscaping, add it here. For this example, leave it at $0.

3. Vacancy Loss (D24):
It’s common for rental properties to have vacancies. Use a conservative estimate of e.g., 8% vacancy time.

This works out to approximately 0.96 months of lost rent each year.

Calculate the monthly loss:

=C24 * -D22
  • C24: 8% (vacancy rate as a decimal)
  • D22: $1,400 (monthly rent)

The result is a loss of $112 per month.

4. Total Monthly Revenue:
Add these together:

Total Revenue = D22 + D23 + D24

For this example:
Total Revenue = $1,400 + $0 – $112 = $1,288 per month.

Step 6: Calculate Other Monthly Costs

Next, calculate the additional expenses for maintaining the property. These might include:

  • Maintenance & Repairs (D29): Estimate 3% of rent.
    =D22*0.03 = $42
  • Property Management (D30): Estimate 8% of rent.
    =D22*0.08 = $112
  • Property Taxes (D31): Divide annual taxes by 12 months.
    =(D3*0.015)/12 = $250
  • Insurance (D32): Use an estimated value, such as $30.
  • Other Expenses (D33): Leave at $0 for now.
Total Monthly Costs = D29 + D30 + D31 + D32 + D33

For this example:
Total Costs = $42 + $112 + $250 + $30 + $0 = $434.

Featured Bundle

Power Pro Bundle

10x your productivity in Excel 💪 by mastering Excel’s Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More

Step 7: Calculate Monthly Cash Flow

Finally, calculate your monthly cash flow to see how much profit you make after expenses.

Monthly Cash Flow = Total Revenue - (Loan Payment + Total Monthly Costs)

For this example:

  • Total Revenue: $1,288
  • Loan Payment: $804
  • Total Monthly Costs: $434

Monthly Cash Flow = $1,288 – ($804 + $434) = $50

Calculate Cash on Cash Return (full mortgage)

Cash on Cash Return (CoC) tells you how much return you’re making on the actual cash you’ve invested, excluding loan repayments and equity growth.

Formula:

CoC Return = (Annual Cash Flow ÷ Initial Equity Investment) × 100

Example:

  1. Annual Cash Flow: Multiply the monthly cash flow by 12:
    $50 × 12 = $600
  2. Initial Equity Investment: This is your down payment or the amount of cash you contributed upfront. In this example, it’s $40,000.
  3. CoC Return Calculation:
    CoC Return = ($600 ÷ $40,000) × 100 = 1.5%

This shows a modest annual return on your initial investment before accounting for equity growth or property appreciation.

Featured Course

Fundamentals of Financial Analysis

Read financial statements properly and learn the Excel formulas behind financial reporting. For people moving into finance, and for people who want to be sure nothing is missing.
Learn More
Financial Analysis Course Cover

Calculate ROI on Rental Property

To get a more accurate view of your overall returns, include the principal payments you make each month as part of your equity growth.

Separate the Interest and Principal Payments

Use Excel’s CUMIPMT and CUMPRINC functions to calculate the interest and principal portions of your loan payments.

1. Calculate Monthly Interest (D50):

The Excel CUMIPMT function calculates the total interest paid on a loan over a specific period. Let’s use it to find the average monthly interest payment

Formula:

=CUMIPMT(D16/12, D17*12, D12, 1, D17*12, 1) ÷ (D17*12)

Explanation of the Components:

  1. Rate (D16/12):
    This is the annual interest rate divided by 12 to find the monthly interest rate. For our example, the annual interest rate is 3.0%, so the monthly rate is 3.0% ÷ 12 = 0.25%.
  2. Nper (D17*12):
    This is the total number of payments over the loan’s lifetime. Multiply the loan term in years by 12 months. For a 25-year loan, it’s 25 × 12 = 300 payments.
  3. Pv (D12):
    This is the loan amount, also called the “present value.” In our example, the loan amount is $170,000.
  4. Start_period (1):
    The starting point for the calculation. We want to start from the first payment.
  5. End_period (D17*12):
    The ending point for the calculation. We’ll end at the final payment (300th month).
  6. Type (1):
    This tells Excel when payments are made. A value of 1 means payments are made at the beginning of the month (consistent with our earlier calculations).
  7. No of periods (D17*12):
    To find the average monthly interest payment, divide the total interest by the total number of months in the loan term.

For this example, the average monthly interest is -$237.

2. Calculate Monthly Principal (D51)

The CUMPRINC function calculates the total principal paid on a loan over a specific period. Let’s use it to find the average monthly principal payment.

Formula:

=CUMPRINC(D16/12, D17*12, D12, 1, D17*12, 1) ÷ (D17*12)

Explanation of the Components:

  1. Rate (D16/12):
    This is the annual interest rate divided by 12 to get the monthly rate. For our example, the annual interest rate is 3.0%, so the monthly rate is 3.0% ÷ 12 = 0.25%.
  2. Nper (D17*12):
    This is the total number of payments over the loan’s lifetime. Multiply the loan term in years by 12 months. For a 25-year loan, it’s 25 × 12 = 300 payments.
  3. Pv (D12):
    This is the loan amount, also called the “present value.” In our example, the loan amount is $170,000.
  4. Start_period (1):
    The starting point for the calculation. We’ll begin with the first payment.
  5. End_period (D17*12):
    The ending point for the calculation. We’ll end at the final payment (300th month).
  6. Type (1):
    This tells Excel when payments are made. A value of 1 means payments are made at the beginning of the month (consistent with our earlier calculations).
  7. No of periods (D17*12):
    To find the average monthly principal payment, divide the total principal by the total number of months.

This gives an average monthly principal payment of $567.

3. Verify Monthly Debt Service (D52)

Check that the sum of interest and principal equals the monthly loan payment:
$237 + $567 = $804 (matches the loan payment).

Calculate ROI (with Principal Paydown)

Now, add the monthly principal (which increases your equity) to your monthly cash flow.

  1. Monthly Cash Flow (Interest Only): Add the cash flow and principal:
    Monthly Cash Flow = $50 + $567 = $617
  2. Yearly Cash Flow with Equity Growth: Multiply by 12 to get the annual amount:
    $617 × 12 = $7,404
  3. ROI Formula:
    ROI = (Yearly Cash Flow ÷ Initial Equity Investment) × 100
    For this example: ROI = ($7,404 ÷ $40,000) × 100 = 18.5%

This means your investment is generating an 18.5% return annually.

In other words, for every dollar we invest, we are receiving an additional 18.5¢.

Final Thoughts

Calculating both Cash on Cash Return and ROI gives you a more complete understanding of a rental property’s profitability.

While cash flow may seem small initially, the long-term benefits of building equity and property appreciation can significantly boost your ROI.

With Excel, you can break down these calculations step by step to make informed real estate investment decisions.

Download the Excel Rental Property Calculator

Want to save time and make smarter investment decisions? Download our free Rental Property Calculator in Excel!

This pre-built workbook lets you:

  • Quickly calculate cash flow, ROI, and equity growth.
  • Customize numbers to match your property details.
  • See instant results without any complicated setup.

Click here to download the calculator now and start analyzing your next rental property today!

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses. You’ll learn high-value, in-depth Excel skills that solve real problems.
Learn More
Excel Black Belt Bundle XelPlus

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.