How To Calculate Returns On a Rental Property (ROI with Excel Template)

How To Calculate Returns On a Rental Property (ROI with Excel Template)


Today we’re going to check if a rental property is worth buying. And we’re going to do that by calculating the cash-on-cash return and
the return on investment, or ROI of a potential investment. We’re going to use Excel
to set up a simple template that you can use to analyze all kinds of investment decisions. So let’s say this real
estate agent calls you up and offers you a deal. It’s a house in your area and the owners are moving out of town. They want to sell it. The real estate agent says the owners just want to
have $200 thousand for it, and that you can rent it out
for at least $1,400 per month. So, is this a good deal for you? Comment below and let me know. We’re going to find out in this video. (upbeat music) Before we get started, a brief thanks to Skillshare for partnering with us in today’s video. I’m going to chat more
about them later on. Check out the description for more information when you’re ready. Okay, so let’s see if such an investment that we talked about earlier makes sense. You may think, “Wow, $1,400 a month “as additional income is great.” Couple of more houses like that and you can get to quit your job. But not so fast. From our basic accounting videos, we know that income, or profit, is defined as revenue minus expenses. The rental income is just
the revenue part of that. We need to look at the expenses too. And we also need to
finance such an investment, because most people don’t happen to have $200 thousand
in cash flying around. So we’re probably looking
at some external financing. Now let’s switch to Excel
and crunch some numbers. So first let’s analyze the investment. I’m going to be using
this template I created. Now you can feel free to download this template as well and
use it as you see fit. Let’s start with the purchase
price for the property. We said that’s going to be 200 thousand. So let’s plug that in. Then let’s account for taxes and fees, closing costs and realtor fees. So these are specific costs
that are applicable to property. If you have other type of investments, let’s say you’re buying
machinery or equipment, your costs are going to be different. They could be transport and installation. So just make sure that you really identify the total cash out. So let’s say in our case our taxes and fees are going to be 0.5%. Our closing costs, as
well as realtor fees, are going to be 1% of the full amount. So that’s going to be this
number times this number. And I’m just going to
fix it using the F4 key. Press enter, and pull this down to here. Okay, so just so you know
what’s happening on this side, I’m using the FORLMULATEXT function so that you can see the formulas as I’m typing them in here. These are our costs for these. Next comes the rehab costs. You might need to fix up the property before you can put it on the market. For our case, let’s assume that we need to do a paint-job on the whole property. We might need to fix the kitchen. And that’s going to cost us $5 thousand. If you can think of any other cost, you can put it here. So now let’s calculate
the total investment. That’s basically the sum of all of this. And since I’m in Excel, I can use the shortcut key, Alt and the equal sign, to get an automatic sum. That’s the amount of money that
I need for this investment. Now these are just average numbers. Obviously, they need to be adjusted for your particular investment. Next, let’s specify how
much of our own money we’re going to put into the investment. That’s an important figure, because this is going to be the base for our return calculation. So here, let’s assume that we have $40 thousand in equity. Now as a result, the difference
to the total investment, so basically this number minus the amount that we’re going to put in, that’s going to be the amount that needs to be financed some other way. In our case, we’re going to go
and get a loan from the bank. Now, we go to the bank and we manage to negotiate an annual interest rate of 3% in the loan that we’re going to get. And we’re going to pay
back that loan in 25 years. So let’s calculate our
monthly loan payment. We can use the PMT function. First argument we need is the rate, that’s the interest rate. I want to get that monthly. Here I have the annual interest rate. So I’m going to divide this by 12. Next argument is the number of months for this loan period, that’s 25 years. I need it in months, so
that’s multiplied by 12. Then it’s the present value, so the amount that I’m
borrowing from the bank, that’s the 170 thousand. Next I have the future value. That’s the amount that’s left
at the end of the period. Now obviously the bank
wants all their money back so nothing is left. So I can either leave this
out or put a zero there. And the last argument is type. That’s whether I’m paying this back at the end of the period, or at the beginning of the period. So it’s just about timing. Let’s say I’m always paying this at the beginning of the month. So I’m going to close
bracket, and press enter. $804 is what I’m paying back
to the bank every single month. So this covers the interest, as well as the principal amount, that I’ve borrowed from the bank. Now let’s take a look at
the potential revenue. That’s the cash in by
renting out the property, which we’re going to assume
is $1,400 every month. Now you can add other
sources of income as well. So if you’re renting parking
space, you can put it here. In our case we don’t have anything, so we’re going to put zero. Tenants are going to move out, and you’re going to have periods where there is no cash coming in. So to be realistic, we’re
going to deduct this risk. The risk of our apartment being vacant. So you can pick a percentage here. I’m going to pick 8%. So I’m assuming that 8% of the time, this place could be empty, and I’m going to get no money for it. So 8% of 12 is almost one,
so it’s almost one month. So basically, I’m assuming
that almost a month this place is going to be vacant. That’s a risk that I want to account for. So that’s going to be this number, multiply it with this number
to get my monthly risk. And since it’s a risk,
it’s going to be negative. Our total revenue, if we
use Alt and the equal sign, and press enter, is 1,288 per month. Next, let’s take a look at expenses. Here we deduct all monthly
costs for the investment. So for our example, I included
maintenance and repairs, property management and real estate taxes. These two here, they’re based
on the monthly rental income. Let’s assume this one is 3%,
property management is 8%, and real estate taxes is 1.5%. Now let’s get the numbers here. So that’s 3% of the monthly income. Now I’m going to fix this,
and since it’s an expense, it’s going to be minus. That’s $42 for maintenance, and $112 for property management. Real estate, that’s
based on the full amount, so the property value, that was this much. And that’s minus as well,
and I want to get monthly so I’m going to divide it by 12. So that’s 250 every month. Now for insurance, we can
just put a number here. I’m going to put $30. Other expenses you can
think of, you can put here. In this case, we’re going to go with zero. Total expenses is the sum of this. Alt + equals, $434 per month in expenses. Now obviously these are just examples. These are just average numbers. Just be realistic on planning
the revenue in income. In reality, a lot of business cases fail because people use overly
optimistic assumptions. So now let’s calculate
the monthly cash flow. Which is the sum of debt
service, monthly revenue, and our monthly expenses. Two of these are negative, because for the first one, we’re borrowing money from the bank. That’s this much. Then we have our monthly revenue, that’s our total revenue right here. And then we have our monthly
expenses, which is this much. So what’s our cash flow each month? Let’s use the sum, $50. That’s not much. I guess we’re going to have to hold onto that job a little bit longer. Let’s just calculate
the return on investment to see if, in general, this
is a good investment or not. Even though we have to
keep our job for now. These are expressed in annual percentage. So we just need to multiply
our monthly cash flow by 12. This will give us our yearly cash flow. Our invested equity was our 40 thousand Our cash-on-cash return is
our yearly cash flow divided by our invested equity, 1.5%. Now this doesn’t sound like much, right? Could be the same as what you might get if you put your money
in a savings account. It’s okay, but it’s not that great. So this is also called
the cash-on-cash return, which is an important metric, because cash flow is usually
the most important topic when you’re investing. You don’t want an investment
that’s not adding cash to your bank account each month. But here’s the thing, we’re deducting the full mortgage payment
with the monthly debt service. This amount that we’re paying monthly to the bank pays the interest, of course. But it also reduces the outstanding principal of the loan. Usually as an investor,
you wouldn’t consider the principal payment an expense because you are building
equity, you’re buying property. So with every month that
you’re paying the bank, you own a little bit more of the property. That’s something we can account for. And we are going to account for it when doing the ROI, right here. Now to be able to do
that, we need to split the interest payment of this 804, and the principal payment. So how much of this is
going to cover interest, and how much of it is going to
cover our principal payment? Because the principal
here is a good thing, it’s actually turning into an asset. It’s turning into property. The interest part, that’s the bad thing. That’s what we need to get. Now one way of doing this
is to use an Excel function that’s called cumulative IPMT. So in Excel, we have the IPMT function. It returns the interest
payment for a specific month. But if you use the cumulative IPMT, you get the cumulative interest payment. So we’re going to get the
cumulative interest payment for the entire period. And then we’re going to get a monthly average out of that number. All of these, PMT, IPMT,
they’re very similar. So we need the rate. Rate is our interest rate
that’s sitting right here. So remember, this is an
annual interest rate. I need to divide this by 12. Next argument is the number
of periods, that’s 25. But I need to multiply this by 12 to get the number of months. Present value, that’s the amount of the loan I got from the bank. That’s sitting right here. Then here I have a start period. So from when do I want to get
this cumulative interest rate? I want to start from the first period, and I actually want to
end on the last period. The last period is 25 times 12. Last argument here is the type. And to be consistent with my PMT formula, I’m going to put a one here,
close bracket, press enter. This amount is the
cumulative interest payment for the entire period. What I want is to calculate an average monthly amount for it. So I’m actually going to divide
this by the number of months, which is 25 times 12. This amount, that’s the
average interest paymet over the entire period. Now if you’re curious if there’s a formula to calculate the monthly principal payment for the entire period, yes there is. I’m just going to show
it to you really quickly. It’s called the cumulative principal. And for that you need the
same arguments, in fact. So just to keep things simple, I’m actually going to copy this formula. I’m going to paste it right here. And let’s change the formula here. Instead of cumulative IPMT, we’re going to go with
this one, and press enter. And that’s the average monthly principal over the entire period of this loan. So check this out, the sum of these is going to be the loan that
you’re paying back each month. We can do that right here. $804 that you’re paying back to the bank, which is took out the interest part, and took out the principal payment part. Let’s take a look at
our monthly cash flow, which is actually what
we calculated right here. Let’s add the monthly principal
payment, which is this. But I’m going to put a minus because we want it as a
positive number, right? Because this amount is
actually turning into property. Now my monthly cash flow here, that’s the sum of these, is $617. So now, if you take a look
at the yearly cash flow. So let’s take this and
multiply this by 12. We take a look at our invested
equity, which is this. And now we take a percentage of our newly calculated yearly cash flow with our invested equity. What do we get? We get 18.5% return on investment. This that means that for every
dollar that we’re investing, we’re getting an additional 18 cents back. That’s actually a pretty good investment. So this is how you can use
a simple Excel template to find out if an investment makes sense. So if you’re looking for online courses, related to real estate investing, or debt financing, like
funding your small business, I can recommend David
Haber’s class on Skillshare. What I especially liked
is how he walks us through the thinking process of whether a new opportunity makes sense, or not. And of course, this includes
crunching some numbers to figure out the profitability
of the opportunity. Aside from this course, you’re going to find thousands of classes that cover a wide range of skills. Including office productivity,
entrepreneurship, public speaking, and a lot more. Now, Skillshare is really affordable too. An annual subscription
is less than $10 a month, and premium membership
gives you unlimited access. So you can join any classes on any topics you’re interested in. The great news is because Skillshare is sponsoring this video, you
can become a member right now, with the link in the description, and get a two-month free trial. If you enjoyed this video, give it a thumbs up.
(button pops) And if you want to improve your skills, consider subscribing.
(button clicks) And don’t forget to hit that bell
(bell dings) so you don’t miss out
on any new videos here. Thank you for watching, and
I’ll see you in the next video.

52 thoughts on “How To Calculate Returns On a Rental Property (ROI with Excel Template)

  1. Off topic question – how are you getting formula text automatically pop up as soon as you hit enter? VBA i assume? Great video as always !

  2. If you'd like to get 2 months of free Skillshare classes: https://skl.sh/xelplus6
    I hope you enjoyed this tutorial where we explore some of Excel's basic as well as Financial functions.

  3. Very informative
    I would like to see more accounting videos in future especially accruals , financial statements etc in detail .
    Thanks 😊

  4. Hi Leila,
    Though the accounting calculations are too confusing for me and I couldn't get proper benefit of this video however I always like to watch your videos that give me a strong backup knowledge and I really appreciate your efforts in this regard.
    The next thing is that I have put a request in comments on your last video which needs your attention to give a better and built-in solution to date calculations if possible or if you feel you may recommend the file I shared on a link there, please.

  5. Hello Leila – thank you for this clear demo! I learnt all those PMTxx functions I never used before! I cannot answer to your question as of 0:48, because an important variable is missing beyond the price and the monthly rental! I also need to know how old the house is! Imagine it's 75 years old and needs to be reconstructed in 2 years, then it's different than if it was just built and I can rent it for 75 years without rebuilding it!.. More generally, wouldn't it be wise to consider the expense of rebuilding any house every 70 years or so? My company does this for their warehouses and their offices, so why wouldn't individuals like us do the same thing for our private real estate?

  6. This was great to see as a finance graduate. I had no idea about an excel function that can separate principal and interest. That function should be widely known for everyone. Also the illustration showing that as you pay off the principal your ownership grows with the house. Really great watch, I'll be enrolling in your VBA udemy course very soon too! So thanks for everything you do.

  7. So before I go to the rest of the video a comment that it is not a good deal because the monthly income is less than 1% of the purchase price

  8. Wonderful Leila, by this you explained in a simple manner how to evaluate wide range of business opportunities.
    Many thank
    Maki S. Hussain
    Baghdad – Iraq

  9. It's important to note, as Leila does, that the monthly interest and principal payments are the averages over the entire period of the loan. So in the first few years you will be paying more in interest and less in principal than is shown and in the later years you will paying more towards the principal and less towards the interest.

  10. Hi Leila! I've paused the video at 0:50 to give you my back-of-the-envelope calculation. I'm assuming a perpetuity rate of 5% which I know is entirely crazy, but it gives a ballpark result. Annual rental income of (1400×12) $16,800 / 0.05 = $336,000 which is more than 50% over the purchase price. So I am concluding it is a good investment. Now I'm going to watch the rest of your video.

  11. Excellent Leila! You had me worried for a while with the morgage as I assumed one had the cash in the back pocket, so to speak. The next step might be a spreadsheet for a 10-year period which should take care of the issues of repairs and taxes, then one could calculate present values and IRRs. Another idea might be for business plans which have to forecast income and expenditures for small companies, as grants are usually contingent on providing such a cash flow forecast.

  12. Hey Leila,
    I would like to know is there3 a way to link multiple excels like one main excel to spread information to other 4 excel files (1 to 4) i found a way with DATA EXISTING CONNECTIONS but it wont refresh automaticly on opening excel, yes i checked option refresh data on opening file. That guide would be good. Thank you You are G8.

  13. i realize this isn't an investing course, but you didn't account for the opportunity cost of the $40,000 in equity. Using that money cost you something. By your own examples, you should at least assume it costs 1.5% per annum in a savings account. Great video on Excel though! πŸ˜‰

  14. All clips being posted! are total interested to me, I could use them in future Life. I wonder how you coming up with so interesting video clips.

  15. Hi
    While calculating below in excel
    95,000Γ·12 = 7916.66666666667(10 decimals 11th as 7)
    Using calculator i am getting 11 decimals as 6 and 12th as 7.

    For big numbers excel giving 10 decimals correctly but not 11.

    As per my requirement i need full decimals like scientific calculator or 11 decimals irrespective of small or big ???
    Thanks in advance

  16. Nice and clear – well done Leila! Quick hint: to duplicate a formula in a cell below, instead of copying and pasting from edit mode at 12:49, just press CTRL' (that's control and apostrophe) in D51 and there it'll be! Second hint: don't rent properties to college kids unless you drastically increase maintenance expenses.

  17. Wonderful explained πŸ‘ thank you. I need How to Build a budget in Excel, please. For nonprofit organization πŸ™ salaries. Fringe benefits…

  18. Can you please keep the working part of the video always in the first half of the screen, it's because we are not able to view anything in the bottom 1/3 part of the screen as it's covered by subtitles.

  19. Great as always, thank you Leila! I usually do calculation before taking an actionπŸ˜ƒ. NPV also is my favorite capital budgeting technique when it comes to invest in different projects.

Leave a Reply

Your email address will not be published. Required fields are marked *