Mortgage Calculator With Extra Payment - YouTube

Hi! Welcome to moneyness.biz, your home for personal finance.

Today, I'm going to show you how to make a mortgage calculator with extra payments.

This is something that I've been using myself for years,

I've always found it useful, and I want to share it with you.

You might think that building a mortgage calculator in a spreadsheet sounds really complicated.

Trust me, this is not rocket science. If you know how to use a spreadsheet just a little bit,

we can build this together.

You might be thinking, "there are a ton of mortgage calculators out there on the internet."

Well, why is this one special? This one is special, first,

because some of those mortgage calculators out there don't get things right.

For example, when I search YouTube for mortgage calculator, the first thing that comes up,

or one of the first things, is a video from 2008, and it gets the interest payments wrong.

I have been using mortgage calculators like this for almost 20 years,

both personally and professionally. I'm going to show you

how to do it right. I'm going to show you at the end

of this video the trick that i use to take this mortgage calculator

and match the amortization schedule in my personal mortgage

to the penny, and I challenge you, once you've built this, to match it up

against the amortization schedule in your mortgage docs

and leave me a comment. Let me know whether you can match to the penny

or not. Another interesting feature of this calculator

is that it handles extra payments. So, for example, if you're six months into the

mortgage and you make an extra thousand dollar

payment, you might wonder, "how much will my total interest shrink?"

or "what's my new mortgage payoff date?" Now you'll have a tool to be able to

answer that. One word of warning first, this is for

fixed rate mortgages only. If you have an adjustable rate mortgage,

they look a little different. Now, having said all that, let's build

this! All righty, here's the beginning of our

mortgage calculator. Cells that are in yellow are things that

can change from loan to loan, so you put your individual information

in there. The other cells are formulas I'm going

to show you how to do those. Let's say we start out with a three

hundred thousand dollar loan, which has a rate of three and a half

percent I put that in in decimals and the term,

the term means how long does the mortgage last, a term

of 30 years. Let's just go through to a little formatting to make it pretty

three hundred thousand dollars in decimals, the rate

in percent, and show two decimal places. Here's our first calculation which is

the term, which is equal to the term in years

above times 12. Now we have our monthly

payment and we're going to use the pmt function.

The first thing the pmt function wants is the rate. Now that rate is an annual

rate but we want a monthly payment so we're

going to divide that rate by 12. Be very very careful, this works for U.S.

mortgages. I don't know if it works for mortgages

outside the U.S. and I know there are other types of

loans where the compounding works differently and you can't just divide by

12. So again, be careful. This works for U.S. mortgages,

I can't guarantee it for anything else. The next thing we need is the number of

periods, which is the number of months. Finally,

we need the loan amount. There is our monthly payment. Now you can check

that monthly payment against other mortgage calculators,

make sure they agree. The monthly payment here is

negative, because we have money, three hundred thousand dollars coming

in, so our monthly payment goes out and it's negative, but for our purposes

that needs to be a positive number, so I'm just going to put a minus

sign in there. Now that we have our monthly payment, let's hit the dollar

sign, format that so it looks pretty. Now we

are going to fill in the next two rows by hand.

After that everything is the same so we will select and drag

down and that will finish off our mortgage amortization schedule.

For this first cell we need the first mortgage

payment due date, so let's call that October 1st,

2020. The beginning balance is the same as the loan amount. The

payment is equal to the monthly payment, but,

we're going to use the dollar signs to make that an absolute reference, so when

we drag things it still points to that B6

monthly payment cell. The interest is the beginning balance

times the interest rate. Again that's an absolute interest rate. And again,

our interest rate, up on B3, is annual and we want monthly so we

divide by 12. Now, we have a payment, it's composed of

principal and interest. We know the payment, we know the interest,

so the principal must be the payment minus

the interest. Here we have a spot for extra payments, let's not worry about

that yet. Once we fill in the whole amortization

schedule then we will come back and play with the extra payments.

Our ending balance is our beginning balance minus the principal that we paid

minus the extra payment. We have three more columns on the right

here. This is the first month of the mortgage,

so we'll put in a one. We've only made one payment so far, so

total interest is equal to that interest payment that

we made. Similarly total principal is equal to

the principal payment that we made plus any extra

payments. We're in good shape so far! For the next

row, payment due date, we're going to use the

edate function to say the next payment due date

is the previous payment due date, and go up one month. Now that is a very funny

looking set of numbers, so i'm going to right

click and say format cells and say date

and say okay. That's a more reasonable looking date.

Our beginning balance is last month's ending balance.

Our payment here, we're going to use the min function.

So it is the minimum of the beginning balance plus this month's

interest, or, the regular monthly payment. The regular monthly payment gets the

dollar signs. The reason we do this is so that if we

make an extra payment, it's possible that our

beginning balance in the last month could get very small,

and if we don't use the min function, we could make too big of a

payment. Our monthly payment might be bigger than what's due.

So if we use the min function it helps account for those

extra payments. The interest and the principal

are calculated the same way as above, so I'll select and drag those down,

and I'll do the same thing with the end balance.

The month is just the month above plus one.

The total interest is equal to our total interest from last month

plus the interest we paid this month, and our total principal is equal to the

total principal from last month plus the principal we paid this month

plus any extra payments. Before I drag everything down, I'm going to do a little

freeze panes here so that we don't lose track of our column headings.

Now, let me select row 10 and I will just drag it down to row 368.

Boom! Okay, you can see our total principal paid over here,

300,000, matches the loan amount. Now, let us make some cells to keep track

of things. Total principal. And total principal

is equal to the maximum of the total principal column.

We're going to use that to check just to make sure each time we make a change

our total principal paid is the same as loan amount. Let's also keep track of

total interest, and again we will make that the maximum

of whatever we find in the total interest

column. And one more thing, if we mess around with

this loan, if we're going to change some

numbers, let's keep track of what our interest payment is with the

original setup. So we will make a row here called

original interest. And I'm going to

copy and paste a value, and format that value.

Okay, this is a pretty reasonable mortgage calculator,

it should match up well with the good mortgage calculators on the web.

And we can use it to do things like say if my note rate was three and a quarter

instead of 3.5, so 0.0325, my new total interest payments would be

around 170,000 dollars. So I would save almost 15,000

dollars, over the life of the loan, by having a lower interest rate.

Let's go back to three and a half. What if I got a 20-year loan instead of

a 30-year loan? Now you can see at the end of our

spreadsheet, down here, everything's at zero because

it's not a 30-year loan anymore so we don't have to go this far

out and it should end right around month 240.

If we have a 20-year loan, our total interest payments

are the original minus the total. Our total interest payments are almost

67,000 dollars lower. Let's go back, let's reset things. We can

see that our mortgage calculator is working right now. Now, I promised you

a mortgage calculator with extra payments, so let's go back up to

the very beginning. If we were to make an extra payment,

let's say after a year we pay an 1,000 dollars.

That will save us 1,751 dollars.

Now, the principal hasn't changed, right so we didn't make any extra

principal payments over the life of the loan. We just paid some principal

early, and it saved us 1,750. If, instead, we made that extra principal

payment, let's say right in the middle of the

loan, around month 180.

Now we only save 688 dollars. When you make the principal payment

early, like we did before, you can see how that

reduces the ending balance, which means the beginning

balance next period is lower, which means that the amount of interest

that you pay that period is lower, which means more goes to principal, and

that's why you save so much money. Here is a mortgage

calculator with extra payments, and this is pretty good, but I promised

you a trick that let me match up this mortgage

calculator to my actual mortgage documents. So

here's the trick. Take a look at this monthly payment. If

we look out a couple more digits, you can see

that we are paying fractions of a penny. But in the real world you can't pay a

fraction of a penny. So, what we need to do,

is round this. Use the round function.

And I'm going to say round that payment to two decimal places. Now you can see

that the monthly payment is only to the penny. We need to do this

for the interest payment as well. So let us take that interest payment,

round it, again to two decimal places, and we will

just drag that on down. Now

this, with the rounding, matches my mortgage documents to the penny.

One interesting thing that happened though,

is that our total principal is now not equal to our loan amount. This means that

our last payment didn't fully pay off our mortgage. We

have to pay an extra $2.71. Now, we could make a complicated

Excel formula to do that, but, just to be simple, let's add it in

manually and say plus 2.71. Now our principal

matches. You could say, with some justification,

I thought this was a fixed rate mortgage, I thought my payment was going to be

constant over the life of the loan. In my mortgage docs the last payment is

just a little different from all the other payments, and it's

because of this rounding. Let's try one more example. What if our

mortgage was for 100,000 dollars?

Now again, you can see that the total principal is

off a little bit, so we need to get rid of

that extra amount we put in before, and change it manually.

There we go, everything works out. Let's wrap things up.

I hope you learned three things today: first, how to make a mortgage calculator,

second, how to use it to look at different loans,

and third, how to measure the impact of making an earlier and

extra payment. As I said before, if you have a mortgage, please take your

mortgage docs and match it up against the calculator.

Leave a comment saying whether they match to the penny or not.

Thank you so much for watching! Please hit like

and subscribe and the bell, and I'll see you next time!