Create Your Excel Bill Sheet for Mortgage Effortlessly

Excel bill sheet for Mortgage

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