馃攳
Calculating Returns On a Rental Property (ROI with Excel Template) - YouTube
Channel: Leila Gharani
[0]
Today we're going to check
[1]
if a rental property is worth buying.
[5]
And we're going to do that by calculating
[7]
the cash-on-cash return and
the return on investment,
[12]
or ROI of a potential investment.
[15]
We're going to use Excel
to set up a simple template
[18]
that you can use to analyze
[20]
all kinds of investment decisions.
[22]
So let's say this real
estate agent calls you up
[26]
and offers you a deal.
[28]
It's a house in your area and the owners
[30]
are moving out of town.
[32]
They want to sell it.
[34]
The real estate agent says
[36]
the owners just want to
have $200 thousand for it,
[40]
and that you can rent it out
for at least $1,400 per month.
[45]
So, is this a good deal for you?
[48]
Comment below and let me know.
[49]
We're going to find out in this video.
[51]
(upbeat music)
[55]
Before we get started,
[56]
a brief thanks to Skillshare
[58]
for partnering with us in today's video.
[60]
I'm going to chat more
about them later on.
[63]
Check out the description
[64]
for more information when you're ready.
[66]
Okay, so let's see if such an investment
[69]
that we talked about earlier makes sense.
[71]
You may think, "Wow, $1,400 a month
[76]
"as additional income is great."
[80]
Couple of more houses like that
[81]
and you can get to quit your job.
[84]
But not so fast.
[86]
From our basic accounting videos,
[88]
we know that income, or profit,
[91]
is defined as revenue minus expenses.
[95]
The rental income is just
the revenue part of that.
[100]
We need to look at the expenses too.
[102]
And we also need to
finance such an investment,
[105]
because most people don't happen
[107]
to have $200 thousand
in cash flying around.
[110]
So we're probably looking
at some external financing.
[114]
Now let's switch to Excel
and crunch some numbers.
[117]
So first let's analyze the investment.
[120]
I'm going to be using
this template I created.
[122]
Now you can feel free to download
[124]
this template as well and
use it as you see fit.
[127]
Let's start with the purchase
price for the property.
[130]
We said that's going to be 200 thousand.
[132]
So let's plug that in.
[134]
Then let's account for taxes and fees,
[136]
closing costs and realtor fees.
[139]
So these are specific costs
that are applicable to property.
[143]
If you have other type of investments,
[145]
let's say you're buying
machinery or equipment,
[148]
your costs are going to be different.
[149]
They could be transport and installation.
[152]
So just make sure that you really identify
[155]
the total cash out.
[156]
So let's say in our case our taxes
[158]
and fees are going to be 0.5%.
[161]
Our closing costs, as
well as realtor fees,
[165]
are going to be 1% of the full amount.
[169]
So that's going to be this
number times this number.
[172]
And I'm just going to
fix it using the F4 key.
[174]
Press enter, and pull this down to here.
[177]
Okay, so just so you know
what's happening on this side,
[180]
I'm using the FORLMULATEXT function
[182]
so that you can see the formulas
[184]
as I'm typing them in here.
[186]
These are our costs for these.
[188]
Next comes the rehab costs.
[190]
You might need to fix up the property
[193]
before you can put it on the market.
[195]
For our case, let's assume that we need
[197]
to do a paint-job on the whole property.
[199]
We might need to fix the kitchen.
[201]
And that's going to cost us $5 thousand.
[204]
If you can think of any other cost,
[206]
you can put it here.
[207]
So now let's calculate
the total investment.
[209]
That's basically the sum of all of this.
[212]
And since I'm in Excel, I can use
[213]
the shortcut key, Alt and the equal sign,
[216]
to get an automatic sum.
[218]
That's the amount of money that
I need for this investment.
[221]
Now these are just average numbers.
[223]
Obviously, they need to be adjusted
[225]
for your particular investment.
[227]
Next, let's specify how
much of our own money
[231]
we're going to put into the investment.
[233]
That's an important figure,
[235]
because this is going to be the base
[237]
for our return calculation.
[238]
So here, let's assume
[240]
that we have $40 thousand in equity.
[243]
Now as a result, the difference
to the total investment,
[247]
so basically this number minus
[250]
the amount that we're going to put in,
[252]
that's going to be the amount that needs
[254]
to be financed some other way.
[256]
In our case, we're going to go
and get a loan from the bank.
[260]
Now, we go to the bank and we manage
[261]
to negotiate an annual interest rate of 3%
[266]
in the loan that we're going to get.
[267]
And we're going to pay
back that loan in 25 years.
[271]
So let's calculate our
monthly loan payment.
[274]
We can use the PMT function.
[277]
First argument we need is the rate,
[279]
that's the interest rate.
[281]
I want to get that monthly.
[283]
Here I have the annual interest rate.
[285]
So I'm going to divide this by 12.
[288]
Next argument is the number of months
[291]
for this loan period, that's 25 years.
[295]
I need it in months, so
that's multiplied by 12.
[298]
Then it's the present value,
[299]
so the amount that I'm
borrowing from the bank,
[302]
that's the 170 thousand.
[304]
Next I have the future value.
[306]
That's the amount that's left
at the end of the period.
[310]
Now obviously the bank
wants all their money back
[312]
so nothing is left.
[314]
So I can either leave this
out or put a zero there.
[317]
And the last argument is type.
[320]
That's whether I'm paying this back
[321]
at the end of the period,
[323]
or at the beginning of the period.
[324]
So it's just about timing.
[326]
Let's say I'm always paying this
[327]
at the beginning of the month.
[329]
So I'm going to close
bracket, and press enter.
[331]
$804 is what I'm paying back
to the bank every single month.
[337]
So this covers the interest,
[339]
as well as the principal amount,
[341]
that I've borrowed from the bank.
[342]
Now let's take a look at
the potential revenue.
[345]
That's the cash in by
renting out the property,
[349]
which we're going to assume
is $1,400 every month.
[353]
Now you can add other
sources of income as well.
[355]
So if you're renting parking
space, you can put it here.
[358]
In our case we don't have anything,
[359]
so we're going to put zero.
[361]
Tenants are going to move out,
[363]
and you're going to have periods
[364]
where there is no cash coming in.
[366]
So to be realistic, we're
going to deduct this risk.
[370]
The risk of our apartment being vacant.
[373]
So you can pick a percentage here.
[375]
I'm going to pick 8%.
[377]
So I'm assuming that 8% of the time,
[380]
this place could be empty,
[382]
and I'm going to get no money for it.
[384]
So 8% of 12 is almost one,
so it's almost one month.
[389]
So basically, I'm assuming
that almost a month
[392]
this place is going to be vacant.
[394]
That's a risk that I want to account for.
[397]
So that's going to be this number,
[399]
multiply it with this number
to get my monthly risk.
[402]
And since it's a risk,
it's going to be negative.
[406]
Our total revenue, if we
use Alt and the equal sign,
[409]
and press enter, is 1,288 per month.
[414]
Next, let's take a look at expenses.
[416]
Here we deduct all monthly
costs for the investment.
[419]
So for our example, I included
maintenance and repairs,
[423]
property management and real estate taxes.
[426]
These two here, they're based
on the monthly rental income.
[430]
Let's assume this one is 3%,
property management is 8%,
[435]
and real estate taxes is 1.5%.
[438]
Now let's get the numbers here.
[440]
So that's 3% of the monthly income.
[443]
Now I'm going to fix this,
and since it's an expense,
[447]
it's going to be minus.
[448]
That's $42 for maintenance,
[450]
and $112 for property management.
[454]
Real estate, that's
based on the full amount,
[459]
so the property value, that was this much.
[462]
And that's minus as well,
and I want to get monthly
[465]
so I'm going to divide it by 12.
[468]
So that's 250 every month.
[470]
Now for insurance, we can
just put a number here.
[473]
I'm going to put $30.
[475]
Other expenses you can
think of, you can put here.
[478]
In this case, we're going to go with zero.
[480]
Total expenses is the sum of this.
[482]
Alt + equals, $434 per month in expenses.
[486]
Now obviously these are just examples.
[488]
These are just average numbers.
[490]
Just be realistic on planning
the revenue in income.
[494]
In reality, a lot of business cases fail
[496]
because people use overly
optimistic assumptions.
[500]
So now let's calculate
the monthly cash flow.
[503]
Which is the sum of debt
service, monthly revenue,
[506]
and our monthly expenses.
[508]
Two of these are negative,
[509]
because for the first one,
[510]
we're borrowing money from the bank.
[512]
That's this much.
[514]
Then we have our monthly revenue,
[516]
that's our total revenue right here.
[518]
And then we have our monthly
expenses, which is this much.
[522]
So what's our cash flow each month?
[525]
Let's use the sum, $50.
[528]
That's not much.
[529]
I guess we're going to have to hold onto
[531]
that job a little bit longer.
[533]
Let's just calculate
the return on investment
[536]
to see if, in general, this
is a good investment or not.
[539]
Even though we have to
keep our job for now.
[541]
These are expressed in annual percentage.
[544]
So we just need to multiply
our monthly cash flow by 12.
[549]
This will give us our yearly cash flow.
[552]
Our invested equity was our 40 thousand
[556]
Our cash-on-cash return is
our yearly cash flow divided
[561]
by our invested equity, 1.5%.
[566]
Now this doesn't sound like much, right?
[568]
Could be the same as what you might get
[570]
if you put your money
in a savings account.
[574]
It's okay, but it's not that great.
[576]
So this is also called
the cash-on-cash return,
[578]
which is an important metric,
[580]
because cash flow is usually
the most important topic
[583]
when you're investing.
[585]
You don't want an investment
that's not adding cash
[587]
to your bank account each month.
[589]
But here's the thing, we're deducting
[591]
the full mortgage payment
with the monthly debt service.
[595]
This amount that we're paying monthly
[597]
to the bank pays the interest, of course.
[600]
But it also reduces
[602]
the outstanding principal of the loan.
[604]
Usually as an investor,
you wouldn't consider
[607]
the principal payment an expense
[609]
because you are building
equity, you're buying property.
[614]
So with every month that
you're paying the bank,
[616]
you own a little bit more of the property.
[620]
That's something we can account for.
[621]
And we are going to account for it
[623]
when doing the ROI, right here.
[625]
Now to be able to do
that, we need to split
[628]
the interest payment of this 804,
[631]
and the principal payment.
[632]
So how much of this is
going to cover interest,
[635]
and how much of it is going to
cover our principal payment?
[639]
Because the principal
here is a good thing,
[641]
it's actually turning into an asset.
[644]
It's turning into property.
[645]
The interest part, that's the bad thing.
[648]
That's what we need to get.
[649]
Now one way of doing this
is to use an Excel function
[653]
that's called cumulative IPMT.
[656]
So in Excel, we have the IPMT function.
[659]
It returns the interest
payment for a specific month.
[663]
But if you use the cumulative IPMT,
[666]
you get the cumulative interest payment.
[668]
So we're going to get the
cumulative interest payment
[671]
for the entire period.
[673]
And then we're going to get
[673]
a monthly average out of that number.
[676]
All of these, PMT, IPMT,
they're very similar.
[679]
So we need the rate.
[681]
Rate is our interest rate
that's sitting right here.
[684]
So remember, this is an
annual interest rate.
[686]
I need to divide this by 12.
[689]
Next argument is the number
of periods, that's 25.
[692]
But I need to multiply this by 12
[695]
to get the number of months.
[696]
Present value, that's the amount
[698]
of the loan I got from the bank.
[700]
That's sitting right here.
[701]
Then here I have a start period.
[704]
So from when do I want to get
this cumulative interest rate?
[708]
I want to start from the first period,
[710]
and I actually want to
end on the last period.
[713]
The last period is 25 times 12.
[716]
Last argument here is the type.
[718]
And to be consistent with my PMT formula,
[721]
I'm going to put a one here,
close bracket, press enter.
[725]
This amount is the
cumulative interest payment
[729]
for the entire period.
[731]
What I want is to calculate
[732]
an average monthly amount for it.
[734]
So I'm actually going to divide
this by the number of months,
[738]
which is 25 times 12.
[742]
This amount, that's the
average interest paymet
[745]
over the entire period.
[746]
Now if you're curious if there's a formula
[749]
to calculate the monthly principal payment
[751]
for the entire period, yes there is.
[753]
I'm just going to show
it to you really quickly.
[755]
It's called the cumulative principal.
[759]
And for that you need the
same arguments, in fact.
[763]
So just to keep things simple,
[764]
I'm actually going to copy this formula.
[768]
I'm going to paste it right here.
[770]
And let's change the formula here.
[772]
Instead of cumulative IPMT,
[775]
we're going to go with
this one, and press enter.
[778]
And that's the average monthly principal
[780]
over the entire period of this loan.
[783]
So check this out, the sum of these
[785]
is going to be the loan that
you're paying back each month.
[788]
We can do that right here.
[790]
$804 that you're paying back to the bank,
[793]
which is took out the interest part,
[795]
and took out the principal payment part.
[797]
Let's take a look at
our monthly cash flow,
[800]
which is actually what
we calculated right here.
[803]
Let's add the monthly principal
payment, which is this.
[807]
But I'm going to put a minus
[808]
because we want it as a
positive number, right?
[811]
Because this amount is
actually turning into property.
[816]
Now my monthly cash flow here,
[818]
that's the sum of these, is $617.
[823]
So now, if you take a look
at the yearly cash flow.
[826]
So let's take this and
multiply this by 12.
[829]
We take a look at our invested
equity, which is this.
[833]
And now we take a percentage
[834]
of our newly calculated yearly cash flow
[837]
with our invested equity.
[839]
What do we get?
[840]
We get 18.5% return on investment.
[844]
This that means that for every
dollar that we're investing,
[847]
we're getting an additional 18 cents back.
[851]
That's actually a pretty good investment.
[853]
So this is how you can use
a simple Excel template
[856]
to find out if an investment makes sense.
[858]
So if you're looking for online courses,
[860]
related to real estate investing,
[862]
or debt financing, like
funding your small business,
[866]
I can recommend David
Haber's class on Skillshare.
[870]
What I especially liked
is how he walks us through
[872]
the thinking process of whether
[874]
a new opportunity makes sense, or not.
[876]
And of course, this includes
crunching some numbers
[878]
to figure out the profitability
of the opportunity.
[882]
Aside from this course,
[883]
you're going to find thousands of classes
[885]
that cover a wide range of skills.
[887]
Including office productivity,
entrepreneurship,
[891]
public speaking, and a lot more.
[893]
Now, Skillshare is really affordable too.
[896]
An annual subscription
is less than $10 a month,
[900]
and premium membership
gives you unlimited access.
[903]
So you can join any classes
[906]
on any topics you're interested in.
[909]
The great news is because Skillshare
[911]
is sponsoring this video, you
can become a member right now,
[914]
with the link in the description,
[916]
and get a two-month free trial.
[919]
If you enjoyed this video,
[920]
give it a thumbs up.
(button pops)
[922]
And if you want to improve your skills,
[924]
consider subscribing.
(button clicks)
[925]
And don't forget
[926]
to hit that bell
(bell dings)
[927]
so you don't miss out
on any new videos here.
[930]
Thank you for watching, and
I'll see you in the next video.
Most Recent Videos:
You can go back to the homepage right here: Homepage