hi guys and welcome to the mortgage and rental income calculator template now if you're planning
to take on a mortgage or if you're trying to rent out an apartment or something it's super important
that you plan your monthly cash ins and cash outs and this template helps you do just that so again
let's have a brief look at the different sheets that we're featuring in this template we actually
only have one support sheet and it's called mapping now mapping is all gray nothing for you
to change in here the mapping sheet essentially translates payment frequencies into numerical
values that we need to take into consideration for the monthly calculations also we can later specify
whether we pay a loan at the beginning or at the end of the period and then just some cosmetics
are you planning in square feet or square meters so let's take a look at the first sheet which is
mortgage calculation i'm going to start up with this one so let's say you want to take on a loan
in this particular case the loan amount is coming from the sheet monthly rental income let's take a
brief look at this one essentially you're planning the real estate object how much do you need to pay
for it what's your down payment and what's going to be your total capital required you see that 24
um 247 500 is going to translate into the mortgage calculation sheet which is right here
and now you enter the interest rate that the bank is offering you you can say okay so i'm
getting 2.7 percent in interest and at the same time my duration of the loan is going to be say
30 years so based on that and you can specify whether you want to pay
at the beginning or the end of the period and whether you want to pay monthly or quarterly
you're going to get a rate that you need to pay each month each quarter or let's say
semi-annually so let's go with the monthly one okay roughly 1 000 dollars that you need to
pay each month your amortization schedule is shown right here as we progress throughout the years
you will see that this balance of course decreases because you're paying back the
loan and you're also paying back the interest but let's take a look at what's actually the split
between the interest and the loan that you're paying back so let's scroll down a little bit
here we have our payment schedule now this all in grain this will update automatically
no need for you guys to take action here and the same holds true for these over here because you
see that this is the total payment due roughly one thousand dollars each month and the first month
you're only paying back a bit of the principal you're not paying any interest yet
but then as we move on the total payment due will stay the same but as you can see here
you're paying back a little more of the principal loan amount which is the 24
7500 and your interest payments are going down now what is the logic behind that
of course the bank wants to get paid first and your interest payments are their profit
so they have a keen interest on making sure that the interest is paid first to them because that
for them that's their business right so if you scroll down all the way to the bottom
you see that the principal amount continuously increases each month while the interest payments
decrease the total will always stay the same and very interestingly here here in column i you see
the closing balance so after each month this is what's left to be paid and if we go down you see
you're ending up with a balance of zero after 360 periods being calculated by means the number of
years so depending on what i enter in here the number of periods will change and then also i
could say hmm you know what i actually want to pay end of period and then you will see that even in
the first month you have to pay interest because that means that you've already run a bit of the
loan and that's why you need to pay back interest so this is it for the mortgage calculation
that's planning your expense site let's look at the monthly rental income say that we're
purchasing this and at the same time we also want to rent it out and we're getting a cash in from
this so this is what we usually start with right so we're planning in square meters that's the size
this is going to be the purchase price we have some additional expenses that we need to pay for
like the land registry we have a realtor coming in these are all arbitrary numbers
everything in green means you can change now in the end we can specify a monthly rental price
this is going to be 800 this is just our decision but we can of course change it as we see fit
and it's also going to give us a monthly gross yield now what's the monthly gross yield
it's essentially your rent income multiplied by 12 divided by the purchase price
this is just a very very common kpi that is used in real estate investment
to sort of gauge the attractiveness of renting out an apartment
so if we look at this one right here we of course have an income schedule income schedule
meaning that each and every month we have monthly rental income coming from our rent that we charge
with the payment dates so let's say we charge rent or we receive rent every month on the 24th
now then we need to have you know something for the chart you can disregard this as
again this is green and this is gray so no need for you to change anything about this
but then it may be the case that sometimes you have additional income or additional expenses
let's say in the first month the tenant is moving in and they're placing a deposit with you
now this of course stays with you and at the end of the tenure of the tenant
you need to pay back that deposit at the same time let's say we're getting a new
kitchen sometime here in november so we need to plan for an additional cash out of 2000
for this new kitchen that means it's going to calculate our total monthly cash inflow and
also calculate our accumulated income which will then translate up here into the chart you see
these bar charts reflect our monthly cash inflow now here of course it's negative because we have
to invest in a new kitchen at the same time here in june 2021 we're planning with the tenant moving
out again so we need to pay back the deposit but as you can see here we have quite a nice
trend upwards when it comes to our accumulated income or savings each month in the end
combining monthly rental income and your mortgage payments you get a total cash flow which is
your rental cash inflow your mortgage cash outflow again no need for you to change anything
about this and here you see the net cash impact each month so you see that in on a regular month
you're actually 44 dollars short because your mortgage payments are just a slightly
slight bit higher than your cash in each month and with that you can essentially
plan a full real estate investment and see whether it makes sense for you