Hi this is vic tantri at kandra.com in this video i’m going to show you how to use a home mortgage calculator to analyze a bunch of different scenarios and payment plans for your home loan this spreadsheet was created by dr vipper at vertex vertex42.com vertex 42 is a wonderful online resource featuring dozens of different spreadsheets many of which are available

For free and some of which are available for a paid download i highly recommend you check them out one of the great things about the vertex 42 spreadsheets is that they are rich in information for example if you scroll over any of these little red arrows they’ll give you a lot of information about that particular cell so for the loan amount it says that this is

The amount you have borrowed not the sale price of the home that’s an important point of clarification if you enter the current balance of your mortgage make sure you adjust the term length to be the amount of years that you have left on your mortgage so there are a lot of arrows like this that will help guide you through the spreadsheet and help you really make

Sense of a lot of the complexity let’s get started with some of the inputs the first one is the loan amount which we just talked about we’re going to assume a 250 000 mortgage right below that we have the annual interest rate we’re going to assume six percent per year next up we have the term length in years how long do we expect this loan to last we’re going to

Assume a 20-year loan next up we have the first payment date i’m creating this video in august of 2009 so we’re going to assume that my first payment date is september of 2009. this is important because it will customize the entire spreadsheet based on this input next up we have the compound period how often is the interest compounded usually in the united states

It’s compounded on a monthly basis while in canada it’s semi-annual but again you can customize this based on the specifics of the terms of your loan this isn’t something that you normally get to decide next up we have the payment frequency how often are you expecting to make payments once again you have some choice here determined by the terms of your loan and

We’re going to assume monthly payments right below that you have a very important output which is the monthly payment as you can see our monthly payment is one thousand seven hundred and ninety one dollars and eight cents let’s now scroll down a little further to this little box that says home value or price this box represents how much we expect the home to

Actually be worth right now we’re assuming it’s the same as the low loan amount but it can actually be changed based on the expected value of the home the reason this is important is because property taxes homeowners insurance and monthly property management insurance are all determined by the home value not by the loan loan amount these these tabs right below

That are just assumptions but they can vary on a state by state basis however based on all of these outputs we can get a total a total monthly payment which includes the the taxes and the insurance costs and that’s going to be two thousand three hundred and twenty nine dollars and 41 cents we’re going to skip this little extra payments tab for now i’m going

To come back to that a little bit later on for now let’s jump up here to this balance at a specified year tab this allows you to just simply understand how much you’re going to owe at the end of a certain year how much is the outstanding balance left so for example let’s put balance at the end of year 10. so at the end of or excuse me at the end of year five

Let’s uh let’s see that we have a interest paid of 71 000 about principal paid about thirty seven thousand dollars and our outstanding balance is going to be two hundred and twelve thousand dollars left so those are all relevant things and it’s something that you can uh play around with a little bit if you want to understand that a little bit better let’s now

Move over here to the fixed rate or adjustable rate box you have the choice to choose between the fixed rate or the variable rate interest now if it’s fixed rate all of these boxes freeze up because you can’t make any more assumptions but we’re going to assume that we have a very variable rate mortgage so let’s walk through each of these boxes remember all of

These boxes are simply assumptions there’s no way to really predict 100 certainty for any of these things but they are good to help give you a better sense of the financial picture so the first major one is the num the years that the rate remains fixed the rate will remain fixed usually for a certain number of years we’re going to zoom three right below that we

Have the interest rate cap what is the maximum interest rate we’re going to assume 12 percent below that we have the interest rate minimum of four percent the periods between adjustments simply represents a period in this particular situation by the way is a month remember and we’re going to assume that the adjustments are going to occur every 12 months again

Customizable based on on the assumptions of changes in the economy and right below that we have the estimated adjustment how much do we expect interest rates to increase in that 12-month period based on all of these inputs we can get the output of the highest expected monthly payment now remember this doesn’t include all the property taxes and homeowners insurance

And other things that we talked about earlier this is strictly the mortgage part of the payment so now that we understand that let’s scroll down a little bit here to the tax deduction section this allows you to understand a little bit about how much you might expect to save on taxes a lot of loans will allow you to deduct taxes on the interest that you pay so if

We input our tax bracket we can get an effective interest rate and uh and and probably most importantly we can understand how much tax we expect to be returned rebated to us over the lifetime of the loan granted these are all assumptions again and are very much dependent on your situation but once again it can help you understand your financial picture let’s now

Move to the left here to the summary tab which shows you the total payments that you are that you are outlaying your cash outlay over the lifetime of the loan and of that how much is interest so we are going to have an interest payments of 223 000 over the lifetime of the loan let’s scroll here to the payment scale the premium the payment schedule has several

Different columns the first one column b is simply the payment date when are we making the payments the interest rate is in column c the interest due is in column d this this shows how much are we paying an interest in uh in each period and as you can see the the interest continues to decrease the interest exposure over uh over over the months as they go by

Which is to be expected the payment due is our monthly payment this extra payments column i’m going to come back to this a little later i talked about i talked about how we’re going to do a recurring extra payment option and we’re gonna illustrate this later on in the discussion for now let’s move on to the additional payments this allows you to make a one-time

Additional payment let’s say i get a bonus of five thousand dollars in in month eight so i input five thousand dollars here pay off my loan a little bit quicker and all the outputs are going to change my uh my my total exposure is going to decrease my total interest is going to fall as you can see in the summary sheet up here my total payments has decreased my

Total interest is decreased so making payments early on in in the payment schedule really any time an additional payment can help you reduce your overall debt and your overall interest exposure as well let’s delete that for now we’ll come back to that a little bit later in column h we have the principal paid how much principal are we going to be paying off each

Each period and our total balance their total outstanding loan we have the tax return how much are we expecting to receive in in tax rebates based on our interest payments and based on our tax bracket and finally the cumulative tax return how much tax have we saved over the lifetime of the loan so this payment schedule helps talk about all those major issues for

All of the periods let’s scroll back up here moving to this extra payments section that i was talking about earlier this allows you to set up a recurring extra payment let’s say for example that i receive a uh an an annual bonus of uh let’s say ten thousand dollars per year and i want to take that annual bonus and i want to put it towards paying off my mortgage

A little bit faster and save some money save some money on interest um so i i moved down here and our payment interval as you can see if we look at this red arrow it says that it specifies the extra payment amount will be made every n payments so if we’re going to be making monthly payments of ten thousand dollars we can put one here but again we’re only going

To be making yearly payments so we’re going to do this we’re going to input 12. we’re going to be making this payment every 12 months next up we have our extra annual payment this represents what we’re planning to make this this allows you to make an extra payment um every year in addition to the extra payment that we were talking about but since we’re already

Making an annual payment this is going to be a little redundant for now next up we have the starting payment this shows you when do we want to start making this annual payment is it going to be after the 5th month is it going to be after the 10th month so right now we’re going to assume that it’s going to be after the fifth month so those are the input fields

And the extra payments and right below that we have a little uh output that says the total amount of extra payments and um it shows also that we’re going to save 91 000 in interest as a result of all these extra payments so that’s a sizable amount of money just because we’re making these interest these extra these extra payments over the lifetime of the loan

And if we scroll back down over here do the uh to the extra payments column we can see that in in month 12 we’re receiving a 10 000 extra payment um and we’re inputting that in there and the spreadsheet has now taken that into account so this spreadsheet i hope has helped you understand how many different aspects of analyzing home mortgages and how you can do

That very effectively in one single space i just want to show you once again that this spreadsheet like all of the other vertex 42 spreadsheets are quite printer friendly if we click on the print preview button here you can see that you have a very nice printable schedule scroll down a bit and the whole summary sheet and and all the loan schedules are very

Printable and and you can share it with anyone you want so um thank you very much for watching i hope this video has helped you understand the home mortgage calculator and to purchase this calculator i recommend you click on the link to the side or embedded in this video thanks again for watching and for similar videos i recommend you check out my website www.canjo.com you

Transcribed from video

Home Mortgage Calculator – Vertex42 By kanjohvideo