Calculating Returns for a Rental Property

Have you ever thought you could use more money?  My guess is, “yes”.

A common way to increase income is to purchase real estate, which you can rent out.  The first question that’s asked when participating in such an endeavor is:

“Is this property worth buying?”

Two ways to answer this question are by calculating:

  • Cash-On-Cash Return, and
  • Return on Investment (ROI)

This article will show you how you can use Excel to set up a simple template that you can use to help analyze all sorts of investment decisions.

A completed version of the template is available from the workbook link at the end of the post, but if you take the time to go through the process yourself, you will come away with a greater understanding of what the numbers mean and how they impact the overall decision.

Let’s set the stage.  You receive a call from your real estate agent and informs you of a property that has become available.  It’s a home in your area that the owners are moving out of and need to sell.

The owners have an asking price of $200,000 for the home.

It is expected that you could safely ask $1,400 as monthly rent.

Is this a good deal?

We will run the numbers and see if becoming a property plutocrat is in your future.

Does Such an Investment Make Sense?

If you’re new to the real estate game, it would be easy to think, “WOW! $1,400 additional income would be great!”  With a few more purchases like these, you could quit your job and pursue your dream of becoming a full-time Excel YouTuber.

Hold tight, Mr./Mrs. Budding Tycoon.  If you remember from our Basic Accounting post, we know that income (i.e. profit) is defined as revenue minus expenses.

Income = Revenue – Expenses

The rental income is just the revenue part of the equation.  We can’t forget the expenses.  Unless you happen to have $200,000 just sitting in the bank, you will likely need to explore some form of external financing.

Time to Crunch Some Numbers

We need to analyze the following components:

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

Follow along using the workbook template.

Investment

We will begin by entering the purchase price of the property (cell D3) set at $200,000.

Next, we will account for taxes and fees (0.5%), closing costs (1%), and realtor fees (1%) (cells C4, C5, and C6).

NOTE: These categories are more in line with property purchases.  If you use this template for other purchase types, like heavy equipment, your categories will be different (ex: transportation, installation, etc.)

To calculate these categories, we multiply each of the categories by the purchase price.

Newly acquired properties are rarely ready for rental (say THAT 3-times fast).  We need to account for rehab costs with account for repairs and modifications to the property.  For our example, we need to paint the interior and modernize the kitchen.  For our property, this costs $5,000 (cell D7).

Any other costs can be recorded in the Other category (cell D8).

The Total Investment is the sum of all cells from D3 to D8.  This will be the total cash outflow for the investment.

If you plan on deferring any of these costs with your own money (equity), we need to record this to be deferred from the Total Investment.  Our example will have an equity value of $40,000 (cell D11).

Our final answer for the Investment category is achieved by deducting the Equity (D11) from the Total Investment (D9).  This calculation gives us the Resulting Financing Requirement (cell D12).

Monthly Debt Service

Because we don’t have $170,000 tucked in our bed cushions, we visit our bank and negotiate the following terms:

  • Annual interest rate of 3.0%
  • Term of Loan as a 25-year loan

We enter the Annual Interest Rate of 3.0% (cell D16) and the Term of Loan in Years as 25 (cell D17).

The Resulting Monthly Loan Payment is calculated using the PMT (payment) function.

The PMT function has the following structure:

=PMT(rate, nper, pv, [fv], [type])
  • Rate – is the interest rate. For us, we want to calculate the interest rate for a single, monthly payment (D16/12).
  • Nper – is the number of payments. This is the number of years times the number of months per year (D17*12).
  • Pv – is the “present value”. This is the amount of money we are requesting for our loan, $170,000 (D12).
  • [fv] – is the “Future Value”. This is an optional argument that denotes the amount of money left at the end of the period.  We don’t expect to have any money left at the end of the payment, so we will calculate this at 0 (zero).  You can also leave this blank, just don’t forget to enter the comma separator in the formula as a placeholder.
  • [Type] – defines when a payment is made. This is an optional argument that has two choices: 0 = end of period payment, and 1 = beginning of period payment.  We will make our payments at the beginning of the month.

The formula for our PMT function appears as follows:

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

We will be obligated to pay to the bank $804 at the beginning of each month.  This value includes interest and principal.

Monthly Revenue

The monthly revenue will be the cash received each month by renting out the property.  We will assume a value of $1,400 per month on this property (cell D22).

If you are charging for any other amenities, like parking, cleaning services, landscaping fees, etc., we will catalog that value as “Other Income” (cell D23).  We will leave this at 0 (zero) for our example.

There will likely be months where the property remains vacant due to tenants moving out and new tenants not taking possession.  This risk will be listed as “Less revenue for vacancy” (cell C24).

We will work with a percent of 8% as a safe guess on the amount of vacant time for each year.  This would work out to .96 months per year of vacancy.  That is just under a single month of $1,400.

This calculation for loss (cell D24) is as follows:

=C24 * -D22

Because the value in cell D22 is a risk, it needs to be declared as a negative value.

The result is $112 for each month ($1,344 per year).

The Monthly Revenue is the sum of all cells from D22 to D24.

We are presented with a value of $1,288 per month for revenue.

Monthly Expenses

This section will deduct all the monthly costs for the investment.  We will make the following estimates:

  • Maintenance & Repairs (cell C29) – 3.0%
  • Property Management (cell C30) – 8.0%
  • Real Estate Taxes (cell C31) – 1.5%

The formulas to calculate these costs are as follows:

Maintenance & Repairs (cell D29)

=C29 * -D22

Property Management (cell D30)

=C30 * -D22

Real Estate Taxes (cell D31)

=C31 * -D3/12

For Insurance (cell D32), we will estimate $30 per month.  Other Expenses (cell D33) will be estimated at 0 (zero) for this example.

The Total Expenses is the sum of all cells from D29 to D33.

We are presented with a value of -$434 per month for Total Expenses.

REMEMBER: All these values are estimates purely for the sake of this tutorial.  In real life, you want to make realistic assumptions for all these values.  Many investment plans fail due to people using overly optimistic assumptions.

Monthly Cash Flow

Monthly Cash Flow is the sum of our debt service, revenue, and expenses.  Debt service and expenses will be negative values.

Monthly Debt Service (cell D38)

=D18

Monthly Revenue (cell D39)

=D25

Monthly Expenses (cell D40)

=D34

The Monthly Cash Flow calculation (cell D41) is the sum of all cells from D38 to D40.

We are presented with a value of $50 per month for Monthly Cash Flow.  Looks like our dreams of Excel YouTuber stardom will have to wait a bit longer.

Cash on Cash Return (full mortgage)

Although earnings appear bleak, we should look at our investment form a more long-term perspective.

To see the Cash on Cash Return for the full year, we will calculate our Yearly Cash Flow (cell D45) as 12-months’ worth of Monthly Cash Flow.

=D41 * 12

Our Investment Equity (cell D46) is our original Equity (cell D11).

The Cash on Cash Return (cell D47) is expressed as a percentage by dividing the Yearly Cash Flow by Investment Equity.

=D45 / D46

We are presented with a value of 1.5% return per year (pre-tax).  This doesn’t appear to be much.  It’s about the same as investing your money in a simple savings account.

This sounds like a lot of extra hassle for very little return.  But remember, each time you make a payment to the bank, you are gaining a larger and large percentage of the investment.  In other words, you own more and more of the property.  You are building equity (an asset).

We want to factor this behavior into our calculations.  This is where ROI comes into play.

ROI (without principal pay down)

To see a more accurate picture of what is happening, we need to separate the Interest portion of our payment from the Principal portion.

Monthly Interest

To calculate the Interest portion of the payment (cell D50), we will use the Excel financial function called CUMIPMT.  This returns the interest portion of a payment period.  Once we have that, we will calculate it into a single payment’s worth of Interest.

The CUMIPMT function has the following structure:

=CUMIPMT(rate, nper, pv, start_period, end_period, type)
  • Rate – is the interest rate. For us, we want to calculate the interest rate for a single, monthly payment (D16/12).
  • Nper – is the number of payments. This is the number of years times the number of months per year (D17*12).
  • Pv – is the “present value”. This is the amount of money we are requesting for our loan, $170,000 (D12).
  • Start_period – is the point in time where we want to begin the calculation. We will begin with the first payment; period 1.
  • End_period – is the point in time where we want to end the calculation. We will end with the final payment; the number of years multiplied by the number of months per period (D17*12).
  • [Type] – defines when a payment is made. This has two choices: 0 = end of period payment, and 1 = beginning of period payment.  We will make our payments at the beginning of the month to be consistent with our previous logic

The formula for our CUMIPMT function appears as follows:

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

This yields a value of -71,245 for the entire period.  We want an average monthly amount, so we will divide this result by the number of years for the term of the loan (25) times the number of months per year (12).

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

This yields a value of -$237.

Monthly Principal

To calculate the Monthly Principal (cell D51), we will use the Excel Financial function called CUMPRINC.

The good news for us is this function works exactly like the CUMIPMT function.  It even has all the same arguments.

The CUMPRINC function has the following structure:

=CUMPRINC(rate, nper, pv, start_period, end_period, type)
  • Rate – is the interest rate. For us, we want to calculate the interest rate for a single, monthly payment (D16/12).
  • Nper – is the number of payments. This is the number of years times the number of months per year (D17*12).
  • Pv – is the “present value”. This is the amount of money we are requesting for our loan, $170,000 (D12).
  • Start_period – is the point in time where we want to begin the calculation. We will begin with the first payment; period 1.
  • End_period – is the point in time where we want to end the calculation. We will end with the final payment; the number of years multiplied by the number of months per period (D17*12).
  • [Type] – defines when a payment is made. This has two choices: 0 = end of period payment, and 1 = beginning of period payment.  We will make our payments at the beginning of the month to be consistent with our previous logic

The formula for our CUMIPMT function appears as follows:

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

This yields a value of -$567.

Monthly Debt Service

As a double-check, the sum of these two results (cells D50 & D51) is the total of the loan being paid each month ($804) to the bank.  We can calculate this (cell D52) with the following formula:

=SUM(D5:D51)

Monthly Cash Flow

Our full mortgage Monthly Cash Flow has been calculated (cell D41), so we will reference it (in cell D54).

=D41

Monthly Principal Payment

Our full Monthly Principal Payment has been calculated (cell D51), so we will reference it (in cell D55).  Because the referenced value is a negative value, we will preface our reference with a negative symbol to “flip” the value into a positive state.

=-D51

Monthly Cash Flow

The Monthly Cash Flow (interest only) calculation (cell D56) is the sum of cells D54 to D55.

=SUM(D54:D55)

This yields a value of $617.

Yearly Cash Flow

The Yearly Cash Flow (cell D58) takes the Monthly Cash Flow and multiplies it by the number of months in the period (12).

=D56 * 12

This yields a value of $7,398.

Invested Equity

Investment Equity has been calculated (cell D46), so we will reference it (in cell D59).

=D46

ROI Return

Our newly calculated percentage for ROI Return (cell D60) will divide the Yearly Cash Flow (cell D58) by the Invested Equity (cell D59).

=D58 / D59

This yields a value of 18.5%

This means that for every dollar we invest, we are receiving an additional 18.5¢.  That’s pretty good over the long term.

So, the answer to our question is… “yes”.  Purchasing this property appears to be a good decision.

Download Excel Workbook & Template

Feel free to Download the Workbook and Calculate the Returns on Investment HERE.

Excel Download Practice file

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials