How to Calculate Returns on Rental Property in Excel: A Comprehensive Guide

CEO Khai Intela
Let me set the stage for you. Imagine receiving a call from your real estate agent, telling you about a property that has just become available. It's a home in your area that the owners...

Let me set the stage for you. Imagine receiving a call from your real estate agent, telling you about a property that has just become available. It's a home in your area that the owners are moving out of and need to sell. The owners are asking for $200,000 for the home, and it's estimated that you could rent it out for $1,400 per month. But is this really a good deal? Let's run the numbers and see if becoming a property owner is worth it.

Does Such an Investment Make Sense?

If you're new to the real estate game, it's easy to think, "Wow! $1,400 additional income would be great!" But before you jump in, let's consider the expenses. As we learned from our Basic Accounting post, income is defined as revenue minus expenses. In this case, the rental income is just the revenue part of the equation. We can't forget about the expenses. Unless you have $200,000 just sitting in the bank, you'll likely need some form of external financing.

Time to Crunch Some Numbers

To analyze the investment, we need to take a closer look at several components:

  • Investment
  • Monthly Debt Service
  • Monthly Revenue
  • Monthly Expenses
  • Monthly Cash Flow
  • Cash on Cash Return (full mortgage)
  • ROI (without principal pay down)

Let's dive into each of these categories using an Excel workbook template.

Investment

First, we'll enter the purchase price of the property, which in this case is $200,000. We also need to account for taxes and fees (0.5%), closing costs (1%), and realtor fees (1%). These categories are specific to property purchases, so if you're using this template for other purchase types, the categories will be different.

Next, we need to consider rehab costs, such as repairs and modifications to the property. In this example, we'll need to paint the interior and modernize the kitchen, which will cost $5,000. Any other costs can be recorded in the "Other" category.

The total investment is the sum of all these costs, minus any equity you plan to contribute. In our example, the equity value is $40,000. Subtracting the equity from the total investment gives us the resulting financing requirement.

Monthly Debt Service

Since we don't have $170,000 tucked away, we'll visit the bank and negotiate the terms of our loan. We'll assume an annual interest rate of 3.0% and a 25-year loan term. Using the PMT function in Excel, we can calculate the resulting monthly loan payment, which includes both interest and principal.

Monthly Revenue

The monthly revenue is the cash received each month from renting out the property. In this case, it's $1,400 per month. If you charge for additional amenities like parking or cleaning services, you can add that as "Other Income." We also need to account for potential vacancy periods when the property remains unoccupied. In our example, we'll assume an 8% vacancy rate, resulting in a loss of $112 per month.

Monthly Expenses

Now, let's deduct all the monthly costs for the investment. We'll estimate maintenance and repairs at 3.0% of the monthly revenue, property management at 8.0%, and real estate taxes at 1.5%. We'll also factor in insurance and any other expenses. The total expenses will be the sum of these costs.

Monthly Cash Flow

The monthly cash flow is the sum of the debt service, revenue, and expenses. By subtracting the expenses, we can determine the net cash flow each month.

Cash on Cash Return (full mortgage)

Although the monthly cash flow may seem low, we need to consider the investment from a long-term perspective. The cash on cash return calculates the return for the entire year. It takes into account the yearly cash flow and divides it by the investment equity. In our example, the cash on cash return is 1.5% per year.

ROI (without principal pay down)

To get a more accurate picture of our investment, we need to separate the interest portion of our payment from the principal portion. By using the CUMIPMT function in Excel, we can calculate the total interest paid over the loan term. We can also calculate the principal payment using the CUMPRINC function. The monthly cash flow, interest payment, and principal payment together provide a more realistic ROI. In our example, the ROI is 18.5%.

So, to answer our initial question, purchasing this property appears to be a good decision. Remember, these values are estimates for the sake of this tutorial. In real life, you'll want to make realistic assumptions. Investing in real estate can be a hassle, but over the long term, it can provide a solid return on investment.

Feel free to download the Excel workbook and template to calculate the returns on your own investment. Don't forget to make your calculations based on realistic assumptions and consult with professionals if needed.

Excel Workbook Download the Excel Workbook and Calculate the Returns on Investment HERE

1