Make a fully flexible home loan calculator ~ Excel Magic

We recently took a mortgage on our house. As I am self-employed, there is a lot of variability in my income. So I wanted to see the impact of occasional top-up payments. Most online calculators couldn’t tell me how it would impact my loan term. So I went ahead and built an Excel calculator to give me the answer.

We’re trying to borrow some money from the bank to purchase a house the problem with mortgages is you have got a monthly or a weekly payment and every week you will make the same payment but as i am self-employed there is a lot of variability in my income so i wanted to know what it would be like if i make some arbitrary extra payments on my mortgage most of the

Online mortgage calculators that i try do not cater for such a thing they do have extra payment option but it is just a fixed payment every six months or something like that so i did what i do best which is i made an excel mod cage calculator so in this video let me explain how we can construct such a mod cage calculator if you don’t want to watch the whole video

But rather want to have the mod cage calculator for your situation feel free to download the link for that is in the video description here is my mortgage payments calculator so i’m borrowing 500 000 for 20 years at 5.35 annual percentage rate and my monthly payment works out to three hundred be ninety seven dollars as you can see here in this graph it tells

Me that i’m saving four years and ten months with extra payments and this extra payment column is where i can put my data so let’s see what happens if i delete all of these payments my payment term is same i’m not really saving anything and now let’s just see what happens if i make an extra payment in the sixth month for five thousand dollars i’m gonna save four

Months of extra payment and at the end of the year let’s add another ten thousand i’ll get one year’s savings with extra payment if i add another ten thousand here i’ll save one year seven months with just 25 000 of extra payment so how do you build something like this this is a very good example of how to use various excel formulas and think about challenges

Like this so let’s do all of this from scratch in a blank worksheet so i’m going to add a new worksheet we do need some input values the input values that we need are loan amount term and then interest rate so the loan amount is let’s just say this is 500 000 the term would be 20 again you can put any number of years here and then the interest percentage

Would be let’s just say 5.50 percentage so once these data points are there we just need to calculate what our monthly payment would be even though i’m calculating it at monthly level you can use the same formula logic to calculate for weekly or fortnightly or quarterly payments if you want so the formula that works for this is pmt formula pmt and then you

See also  Heres How Toyota Dealerships are Scamming You

Will specify the rate the rate is here because the rate is annual whereas we are making monthly payments we just need to divide the rate with 12 to get the effective monthly interest rate and n per refers to the number of payments you would be making so the term is 20 years and you’re making monthly payments so you will make 20 times 12 payments and then the pv

Refers to the present value or the amount that you’re borrowing so this is the amount and you will get your monthly payment here it will come up in negative because you’re borrowing 500 000 and you’re giving out three thousand dollars every month now this is how much you pay every month but to cater for extra payment scenario what we need is we need to set up a

Loan amortization table uh this is where every month we need to know how much of the balance is there and then calculate things on top you see what happens with extra payments is whenever you make an extra payment it will directly reduce your outstanding principle whereas with your monthly payments you’re not only paying the principal but you’re also paying for

The interest so this is why we need to set up a table to cater for the extra payments and recalculate outstanding principle so that we can fit all of them together to see what impact it would have so to set up the loan amortization table or that monthly schedule we’ll just have we need an area where months can be specified so here i’m just saying sequence and

I want to make up to 30 years maximum so 30 times 12. what this will do is it will give you all the values from 1 through 360 which works out to be 30 years so we can calculate the schedules for up to 30 years with this sequence formula the next one is we just need to know what is the opening balance from a principal standpoint so opening balance in the month

One will always be equal to whatever you borrowed and then the second month would be this money minus how much ever principal payment has been made so here again we will need to know what is the principal paid and then what is the extra payment the closing balance in the month would be basically 500 000 minus your principal paid minus extra payment so this

Is really how it works out um the principal paid amount in each month can be calculated using the ppmt function what this will do is it will take a rate and then a number of payment and then n per and then figure out basically how if this all works out while this formula does work because the extra payment will knock down the remaining term we need also to

See also  How to make money Online using LUNO || South Africa

Calculate how much is the balance term so before we even use that we need to have an extra column here it’s called effective term so initially we are not making any extra payments we are just borrowing so the effective term at the start would be this times 12. all of this will make sense once we set up the whole spreadsheet so let’s go back and finish the

Rest of the calculations and then see how everything falls into the place so the principal paid in the very first month would be ppmt of this rate lock this to c4 always divided by 12 and then per refers to the payment number okay so for the per it will always be one we’ll just assume that we have got the opening balance and then we’ll calculate on top of it

Again this will make sense once we set up the rest and then amper is how many payments we are going to make because the effective term is here we’ll just point to that this way when i drag this we will be able to calculate this further down and then the pv is opening balance so this will tell you that in the very first month you would have paid 1147 dollars

Of principal so even though you’re taking a payment of 3400 only 1100 is the principal rest covers your interest on the loan and then extra payment is any payment value that you input right now we’re just going to leave that blank and then the closing balance would be this now we need to subtract this but because this is already negative we can just add it

And and then take out any extra payment that you put here so so we started the month with 500 000 balance we paid 1147 and we end the month with 498 852 dollars now comes the fun part which is to calculate the effective term for the subsequent months so this is where the opening balance here will always be closing balance there so just point this formula is

Always simple it will just point to the cell about there the effective term would need to be what would be the number of months it takes to pay off this if you’re making that monthly payment okay so you might think why can’t we just use 240 minus one that wouldn’t work because if i make some extra payment my closing balance will be different so we need to use

The different logic here so this is where the n per function comes in it will tell you on a loan for a given interest rate and number of payments what would be the time it takes to complete that so the rate is here we’ll again lock this by 12 and then the pmt is how much payment you will make every month so that’s the payment that i’m gonna make we will lock

That and then the pv is equal to my opening balance and this will tell you that if you want to make that much payment at that interest rate it will take you 239 months to finish the loan this all checks out but see what happens to this number if i make an extra payment of five thousand dollars this will come down to 234 so this is how we are able to figure out

See also  THE RINGS OF POWER was so bad even the access media now throws AMAZON under the bus!

What is the effect of that on this okay so as you can see here it comes up with a decimal point you can’t really make a decimal point term so i’m just going to round this to zero decimal so it’ll round up we’ll take out this and then we’ll come back to 239 so this formula you can just drag it down and then the next month that will be there and then the closing

Balance is always the same calculation so it’ll go down like that and this row if i just fill this down i will see how my loan will change as it stands without any extra payments we are going to finish at the month number 240 so we’ll make the payment on 240 and then there’s nothing left for the month 241. so now let’s just see what happens if i add some extra

Payments i’m gonna just at the end of first year we’ll make a payment of ten thousand dollars and then at the end of eighteen months twenty five thousand dollars so based on this you can see that when i made the ten thousand the payment term from 229 went all the way down to 220 so it we saved nine months there likewise here you save it almost 10 months so uh

20 months there so this is how the calculation really works so i took this logic and then i kind of applied some extra polishing i applied conditional formatting wherever the extra payment went so if i put a number then the cell will turn green telling me that that is the month in which i’m knocking down some of the loan term and then once i had that i also

Calculated what my closing balance would be if i made no extra payments so this is without no extra payments and this is with extra payments and then i plotted that to show how the closing balance kind of went down the greenings with extra payments and then the gray color is without extra payments and the scale goes up to 30 years so i can see over a course of

30 years how things can work out and then i added a nice little title on the top using some extra calculations that says you say one year seven months with extra payments hi again if you want to learn a little bit more about financial analysis and what if modeling in excel check out this video and youtube thinks you will enjoy this video so pick one of these

Two things i’ll catch you in those places bye you

Transcribed from video
Make a "fully" flexible home loan calculator ~ Excel Magic 😎 By Chandoo