Amortization_ module 3_ table | Summaries Mathematics

HPM | Module_3_Amortization_Table

Hello class. We're going to do an amortization table tutorial today. And I've got the problem primarily set up for us.

And we're going to work and build-- actually, we're going to build two amortization tables under the conditions that

we have stated up here. And this can be applied to many different types of business practices when we're

borrowing money and you have the opportunity to have payment schedules set up.

This is the type of worksheet that we would work through. It also applies to your home or your house loan, works

off of a similar principle. So we're actually going to put together the entire amortization table. Once we get going

with this you'll see it's pretty easy. And there's a kind of a message in the end of this too. And I'll be sure to point

that out to you. But I think you'll be able to see it pretty clearly as well when we work through this.

So here are the parameters that we're going to be working with. We have a rate for this loan over a number of

periods. In this case, it's 30. And the beginning amount, or the borrowed amount, on the loan. Which is its current

present value, which in this case it's $200,000. So in the case of the one we're going to compare it to, same

parameters, interest rate and present value, it's just over a 10 year period, as opposed to the 30 year period. So

we'll be able to compare some of the differences here.

So we get started with this, the first thing we have to calculate is the yearly payment on this loan. And like what

we've been doing so far, Excel has a function for this. It's called PMT for payment. We're going to calculate this on

a yearly payment, you could easily do it on a monthly payment. You can see there's another tab here for monthly.

The worksheet gets rather large, and as far as doing a tutorial it's hard to do that with. But you can go to the

monthly and see how that's calculated as well. It's the same, we use the same method to do that, that we do for

this yearly amount.

So first thing we want to do is calculate the yearly payment. And we're going to go to our formula bar. And under

Financials we can cruise down and you'll see the PMT for payment. We pull that up, the function arguments that

we have, and the first thing it asks for is rate. Second piece is, again, the number of periods, which is 30. And the

last piece of this is the present value. Again that needs to be put in as a negative amount. We drop a negative

Now once we have that, we can start to build our table here. We've got columns here, a beginning amount, total

payments, the interest amount, the principal, and the ending balance. And we calculate this for each period. And

any amortization table that you look at is going to have these five columns that are associated with that.

So the first thing is, it asks for the beginning amount. Which is the beginning amount of our loan. Or in this case,

Partial preview of the text

Download Amortization_ module 3_ table and more Summaries Mathematics in PDF only on Docsity!

HPM | Module_3_Amortization_Table

Hello class. We're going to do an amortization table tutorial today. And I've got the problem primarily set up for us. And we're going to work and build-- actually, we're going to build two amortization tables under the conditions that we have stated up here. And this can be applied to many different types of business practices when we're borrowing money and you have the opportunity to have payment schedules set up. This is the type of worksheet that we would work through. It also applies to your home or your house loan, works off of a similar principle. So we're actually going to put together the entire amortization table. Once we get going with this you'll see it's pretty easy. And there's a kind of a message in the end of this too. And I'll be sure to point that out to you. But I think you'll be able to see it pretty clearly as well when we work through this. So here are the parameters that we're going to be working with. We have a rate for this loan over a number of periods. In this case, it's 30. And the beginning amount, or the borrowed amount, on the loan. Which is its current present value, which in this case it's $200,000. So in the case of the one we're going to compare it to, same parameters, interest rate and present value, it's just over a 10 year period, as opposed to the 30 year period. So we'll be able to compare some of the differences here. So we get started with this, the first thing we have to calculate is the yearly payment on this loan. And like what we've been doing so far, Excel has a function for this. It's called PMT for payment. We're going to calculate this on a yearly payment, you could easily do it on a monthly payment. You can see there's another tab here for monthly. The worksheet gets rather large, and as far as doing a tutorial it's hard to do that with. But you can go to the monthly and see how that's calculated as well. It's the same, we use the same method to do that, that we do for this yearly amount. So first thing we want to do is calculate the yearly payment. And we're going to go to our formula bar. And under Financials we can cruise down and you'll see the PMT for payment. We pull that up, the function arguments that we have, and the first thing it asks for is rate. Second piece is, again, the number of periods, which is 30. And the last piece of this is the present value. Again that needs to be put in as a negative amount. We drop a negative sign in front of the $200,000 that we call up here and we find that our yearly payments for this loan are $17,765. Now once we have that, we can start to build our table here. We've got columns here, a beginning amount, total payments, the interest amount, the principal, and the ending balance. And we calculate this for each period. And any amortization table that you look at is going to have these five columns that are associated with that. So the first thing is, it asks for the beginning amount. Which is the beginning amount of our loan. Or in this case,

cell B8. We'll pull that. Our total payments on the loan are the yearly payments that we just calculated. The interest on the loan is the beginning amount of our loan, times the yearly interest rate. So it's the beginning amount times the yearly interest rate. And our principal payment on the loan is the total payments that we make less the interest on this loan. So it's $17,765 minus the $16,000 in interest, or a principal payment of $1,765. Our ending balance on this is the beginning amount of the loan, minus the principal payment. So our ending balances at the end of year one is $198,235. At this point, to figure the beginning amount for the second year, it is our ending balance from the first year. So we just referenced that ending balance amount. And then the payments are not going to change through this loan. So we can hold these steady. So if you recall, we use the dollar sign to do that. We're just going to pull that down one. Our interest rate, this column, the beginning amount we want it to float, but the interest rate we want to remain constant. So we're going to put a dollar sign in front of the B and behind it to hold that interest rate constant. And the principal payments are going to work down the sheet with us. So we're going to continue to pull that formula as we work down the sheet, so we can pull that down. And same way with the ending balance, it's going to work down the sheet. Now once you have this second row completed, you can cover all of that and pull all of this down. And if we did this right, our ending balance should be $0 at the end of year 30. And it is. And so at the end of year 30, we've amortized this entire loan. Which means that we've paid it. We've paid it down, we've made the same total payments each period. You can see what our interest has done. And there's this inverse relationship between the interest paid and the principal paid. You can see how they are moving in opposite directions. So at the beginning of the loan we're paying all of this interest and very little on principle. And by the end of the loan, we're paying virtually everything towards principal and very, very little interest. And if we do it properly, at the end of year 30, because this is a 30 year loan, our balance is $0. And you can see also, we have a tally down here at the bottom that the total payments made on this $200, loan were $532,965. In interest payments we made $332,000 and in principal payments we made $200, towards the loan. So that's a fair amount of interest that was paid on this loan over this 30 year period. So something to think about. Now we're going to take the same loan, and we're converting it to a 10 year loan. And we want to see we want to see what the differences in this are. Same variables, 8% rate and still a $200,000 loan. So let's work this for the 10 year period as well.

fetching all of this high interest, so there's many incentives to get people to upgrade their houses and trade out of their house and pay on it for three or four years, and then go to the next house. It's hard to get ahead if that's what you're doing. You know, because you're constantly just churning at the beginning of these loans. Where you're paying very little in principle, and you're paying all of this interest. They would much rather see you at this point than down here where you're paying them very little in interest and a lot towards the principle of the house. So the faster you can get to the back side of those loans, the better off you are. And again this works in business practice as well. When we're financing pieces of equipment, and we're making purchases for our businesses and stuff, and we're taking out mortgages, and that type of thing. We're taking out loans on equipment, and land, and this type of thing, you have to be cognisant of this time value of money piece of this. And what it actually cost to use those dollars. So when you get a chance, take a look at this next tab over here, the monthly payments, you can see it's rather long. I mean it goes on and on, it goes for 360 periods because we're talking about doing this on a monthly basis over 30 years. Doing this one over a 10 year period, so there's 120 periods in this one. But you can see how little the interest payments are on this worksheet. But take a look at both of those and there's an exercise like this so you'll create your own amortization table. But I think there's some real value in knowing how all of this works and how this fits together. So this concludes this tutorial, so I'll see you on the next one.

Amortization_ module 3_ table, Summaries of Mathematics

Related documents

Partial preview of the text

Download Amortization_ module 3_ table and more Summaries Mathematics in PDF only on Docsity!

HPM | Module_3_Amortization_Table