## 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.

## Kannan P

Feb 2, 2020, 10:01 amThanks

## Pushpaj Chougule

Feb 2, 2020, 10:11 amOff topic question – how are you getting formula text automatically pop up as soon as you hit enter? VBA i assume? Great video as always !

## Leila Gharani

Feb 2, 2020, 10:14 amIf 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.

## Devendra Bairagi

Feb 2, 2020, 10:19 amHow i want to hide the formula

## Nikhil

Feb 2, 2020, 10:20 amVery informative

I would like to see more accounting videos in future especially accruals , financial statements etc in detail .

Thanks 😊

## 123rockstar2010

Feb 2, 2020, 10:27 amHow did you know I need this Leila? WOW, what a coincidence! Thank you so much.

## Kadatua Creator

Feb 2, 2020, 10:36 amThank's

## Russell Potgieter

Feb 2, 2020, 10:50 amThank you for a very informative clip. Where is the download link for the spreadsheet?

## George Tosounidis

Feb 2, 2020, 11:02 amAh! There we are! ROI ! :)))

Otherwise known (or not known) as dR/dS 😉

(R for Revenue, S for Spend)

## Kapil Chandnani

Feb 2, 2020, 11:12 amThe invested Equity also keeps on increasing (Row 59). Hence, as a result ROI falls on YOY basis.

## سامح سيد

Feb 2, 2020, 11:30 amWonderful as usual

## pieter karunia deo

Feb 2, 2020, 11:41 amGreat

## Koraxis S

Feb 2, 2020, 12:11 pmin Australian capital cities 200k would only be a deposit….

## R Garlin

Feb 2, 2020, 12:25 pmThank you – very clearly explained (as ever) and very helpful.👍

## Naman Arya

Feb 2, 2020, 12:28 pmI eagerly wait for your videos Leila. I guess no one explains such fancy excel workarounds like you do. Kudos!

## Betterifitsfree

Feb 2, 2020, 12:42 pmI'm going to the top, and I am taking Leila with me. 🤴💰

## Kam Patterson

Feb 2, 2020, 12:45 pmFantastic, thank you very much.

## Katerina

Feb 2, 2020, 12:59 pmAusgezeichnet, Liebe Leila, Danke Viele Grüße 💞

## jdi2110

Feb 2, 2020, 1:02 pmGreat job explaining. Q – are you now only working w/skill share ior is this in addition to udemy?

## Romana Ahmad

Feb 2, 2020, 1:56 pmHi 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.

## Stefano Gatto

Feb 2, 2020, 2:23 pmHello 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?

## Iron Will

Feb 2, 2020, 2:28 pmtwo of my fav things in the world investing and excel, insta like you don't even have to say it 😉

## Matthew Woldt

Feb 2, 2020, 2:36 pmThis 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.

## gaith sabah

Feb 2, 2020, 2:53 pmYou look great as always

Great video

## ExcelIsFun

Feb 2, 2020, 3:04 pmThanks for the CUMIPMT fun, Leila!!

## e Rob

Feb 2, 2020, 3:07 pmSo 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

## ACCOUNTING JOB WANTED ANY WHERE IN THE EARTH

Feb 2, 2020, 3:10 pmMost essential but no scope to use by me

## Wessam Mohamed

Feb 2, 2020, 3:23 pmAmazing

## Maki Salim Hussain

Feb 2, 2020, 4:07 pmWonderful Leila, by this you explained in a simple manner how to evaluate wide range of business opportunities.

Many thank

Maki S. Hussain

Baghdad – Iraq

## mtroanoke

Feb 2, 2020, 4:44 pmIt's important to note, as Leila does, that the monthly interest and principal payments are the

averagesover 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.## George Reynolds

Feb 2, 2020, 4:50 pmHi 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.

## George Reynolds

Feb 2, 2020, 5:13 pmExcellent 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.

## Excel Visual

Feb 2, 2020, 5:15 pmExcellent

## Nikola Josimovic

Feb 2, 2020, 5:34 pmHey 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.

## Steve Moore

Feb 2, 2020, 5:46 pmi 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! 😉

## 0ExG0

Feb 2, 2020, 5:58 pmI will use this to not only calculate Rental of Poperty but also Cars.

## Mazen Al-Sakkaf

Feb 2, 2020, 6:06 pmAnother great video..many thanks

## ryan.m

Feb 2, 2020, 6:20 pmMy tipical decision making process usually involves a coin toss

## John Rice

Feb 2, 2020, 6:54 pmDont want spoil that beautiful likes 444 with my 445, but i did it. 🙂

## squirtmph

Feb 2, 2020, 7:10 pmAll 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.

## Yugandhar G

Feb 2, 2020, 8:16 pmHi

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

## DRSteele

Feb 2, 2020, 8:59 pmNice 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.

## Bebri Ga

Feb 2, 2020, 11:22 pmawesome videos.

## Remars Agustin

Feb 2, 2020, 11:55 pmis it me or voice getting deeper.. catching a cold? I hope I'm wrong but very informative as always my dear sensei

## Januaryo Lubong

Feb 2, 2020, 12:32 amthank for this…

## Ina FM

Feb 2, 2020, 12:57 amWonderful explained 👏 thank you. I need How to Build a budget in Excel, please. For nonprofit organization 🙏 salaries. Fringe benefits…

## Marcello1b

Feb 2, 2020, 1:02 amExcellent!

## Frederic Biondi

Feb 2, 2020, 2:12 amAgain an excellent video. Congratulations Leila 👏👏👏

## Prashant Digge

Feb 2, 2020, 4:39 amCan 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.

## Soul of Chogokin

Feb 2, 2020, 5:31 amHi Leila, i wonder if you have a video tutorial for small business like distribution company. Appreciate it!

## Dennis D

Feb 2, 2020, 7:50 amVery concise explanation of the evaluation process. Thank you.

## Farzaneh Mohammadi

Feb 2, 2020, 7:54 amGreat 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.

## roberto R

Feb 2, 2020, 9:05 amYou aren’t getting a 3% loan on an investment property. Also, while you pay mortgages on the first of the month, the payment is for the prior month, last day. You can check with any mortgage loans you have lying around. Your maintenance is also wayyyyy too low, on my little real estate empire, $2000 a year per property is close, you’ll lose a roof or water heater every so often, plus new carpet and paint at a minimum between tenants. Other than these, pretty good spreadsheet though!

## Noor Ashik Ahamed Maraikar

Feb 2, 2020, 11:34 amHow do we get the formula typing automatically in Mac? Example if I want to use sum product, I have to type completely by myself, it isn’t give suggestion. Can you please help to set automatically?

## Agent. Xero

Feb 2, 2020, 12:33 pmVery interesting, thank you!

## Rahul Jauhari

Feb 2, 2020, 1:15 pmThanks mam for sharing knowledge . I am living small town of India we never calculate this.

## Bart Titulaer

Feb 2, 2020, 1:48 pmLeila, very good! one suggestion: please use the default colours in Excel for assumptions: Input cells in Cell Styles. I (also?) don't like the colours but at least it is consistent…. One other thing : the monthly % is actual the following: (1+%yr)^(1/12)-1. (since it is coumpound interest) The difference is very, very minor, maybe that's why Microsoft ingnores this even in het help of the formula assistent and also this is not mentioned in all the Excel books I have read. But there are other things in the world we should make our concerns of…;) btw no Amsterdam in 2020?

## darryl morgan

Feb 2, 2020, 2:57 pmHi Leila!Awesome Tutorial I Have Just Learned Some New Functions Thank You :):):)

## Gabriella Prievara

Feb 2, 2020, 3:23 pmThank you Leila! Very, very helpful and so easy to understand. I saved the template and I will definitely use it. 🙂

## Ashraf Hegazy

Feb 2, 2020, 5:05 pmA Like before viewing 😍😍

## G Williams

Feb 2, 2020, 5:07 pmGdayam.

## Daniel Bravo

Feb 2, 2020, 9:04 pmI love your channel. Thanks for making such a great content available for free!

## Doug H

Feb 2, 2020, 10:07 pmExcellent walk-through! Now I'll have to go find some rental property 🙂

## Robert DeMilo

Feb 2, 2020, 10:15 pmCool worksheet! Let's make a super version that includes looking at this over a longer period of time: You have property increases, tax increases, rent increases, potentially property refurbishments, fencing, roofing, water heaters, hvac systems, plumbing or electrical for cost but I think there is also the added benefit of tax deductions.

## JE Hayes

Feb 2, 2020, 11:13 pmJust so you know, I tried downloading the workbook from the blog and it throws a "file is corrupt error".

## victor ubong

Feb 2, 2020, 11:49 pmThanks Leila. Very educating and helpful video. Please can you do one that compares renting vs buying a property?

## John Borg

Feb 2, 2020, 4:11 amGreat video Leila. Thanks 🙂 🙂

## donot giveup

Feb 2, 2020, 5:00 amExcuse Ma'am. How are you?

Hope you're doing great. Ma'am your information excel is too much usefull for me… Thank you so much.. Ma'am also I have a very humble request to you. Please share a one toturial about accounting e.g. (Income Statement,,, Financial Statement). Thanks.

## sulaiman sait

Feb 2, 2020, 6:31 amSuper mam about video update more

Than you can explain to us PowerPoint

## EB Bek

Feb 2, 2020, 8:09 amI invest for living. This was a great tutorial and thanks for making it interesting and also having a downloadable file. Cheers from Ethiopia