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 – ExpensesThe 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.
Featured Course
Excel Essentials for the Real World
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 * -D22Because 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 * -D22Property Management (cell D30)
=C30 * -D22Real Estate Taxes (cell D31)
=C31 * -D3/12For 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.
Featured Course
Power Excel Bundle
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)
=D18Monthly Revenue (cell D39)
=D25Monthly Expenses (cell D40)
=D34The 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 * 12Our 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).
=D41Monthly 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.
=-D51Monthly 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 * 12This yields a value of $7,398.
Invested Equity
Investment Equity has been calculated (cell D46), so we will reference it (in cell D59).
=D46ROI 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 / D59This 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.
Featured Course
Fundamentals of Financial Analysis
Download Excel Workbook & Template
Feel free to Download the Workbook and Calculate the Returns on Investment HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
