FIN 26 Loan Amortization Schedule

Let's take a look at problem 324A. We're running through a loan amortization schedule, so let's read the question. We'll see what we're up against, and I think these ones aren't too hard, and in fact, if you've ever had a loan, you may have seen one of these before. You borrow $100,000 on a 10-year loan, the loan has 9% annual interest rate and requires you to make equal annual payments. I know that they don't tell you how much those equal annual payments are, so for us to do a schedule, we've got to figure out how much the payment is, and in fact, before we can do a schedule, we've got to figure out what that payment is, so we're going to solve for the payment. If we look at our PV of an annuity formula, we're solving for this, the regular cash flow. I'll solve it first in the financial calculator, then we'll do it by hand, and then we'll prepare our loan amortization table. Let's go through it. What am I going to input into my calculator? Well, let's start with the N, number of periods is 10, there's 10 years, it's annual interest, so 10 periods, 10 payments. What's next? I slash Y, the discount rate, the discount rate here is 9%. PV, well, what is the value today? We're borrowing, we're getting $100,000, let's put in a positive $100,000 as the PV. The next one is PMT, that's what we're solving for, and the last one is FV, and that's going to be zero because we're paying off this loan over the 10-year period. Okay, so let's solve. 10 goes in as N, 9 goes in as IY, 100,000 to positive goes in as PV, zero goes in as FV, and I'm going to compute PMT, I get PMT of 15,582.00, I guess 0.01, I'm just going to leave it 15,582. Okay, so let's do that in the PV of an annuity. If you want, you can skip ahead like a minute if you are satisfied with 15,582, but we'll do the math here. So PV is 100,000, we know this is 100,000 equals, we're solving for C, times 1 minus 1 over 1.09, 1 plus R, to the T to the power of 10, all divided by R, which is 0.09. Okay, let's solve. 1.09 to the 10th power, I go 1 over that number, so 1 over X, I go 1 minus that number, so I do minus 1, hit the plus minus, 0.5775, divided by 0.09, and now my brackets, the total amount of what's in brackets is 6.42, so it's 100,000 equals C times 6.42, solving for C, C equals 100,000 over 6.42, and I'll just take this number, which is, you know, no rounding here, I hit 1 over X, times 100,000, and that gets me the answer of 15,582, C equals 15,582, and look at that, that was our payment from before. Okay, so we've solved for the payment, but that isn't answering the question, that question said give me an amortization schedule for the loan. Here's how we do it. We start with a beginning amount, beginning value, I suppose, would be one way of putting it, and the beginning value of our loan is $100,000, this is year one. How much are we paying? Well, we make a payment of 15,582. How much is interest of our payment? And the interest is 9% times the beginning value, so the interest here in year one, we have a loan that's 100 grand times 0.09, 9,000, so if I make a $15,000 payment, 9,000 goes to interest, how much goes to actually reduce the loan? We call this the principal, you could call it principal reduction, I suppose, but it's $6,500, 65,82, and it's just 15 minus nine is six. So what's the ending value of the loan then, or the ending balance? After we've made the payment, the value or the balance left on the loan, 100,000 minus what we paid down, 65,82, remember the 15 grand, a bunch of it went to interest, only six grand went to pay down the loan, we're at 93,418, okay, that's year one. We're asked to do three years, now there are 10 years, but we're just going to do years two and three. We start year two with what we ended year one with, 93,418, we make another payment, the payment's always the same, 15,582, our interest is 9% of the beginning value, 9% of 93 grand this time, so times 0.09, it's 840, I'll call it 8408, 93,418 minus 8408, or sorry, sorry, no, no, no, I'm saying the wrong thing here, $15,000 payment, 8,000 was interest, how much was principal, 15,582 minus 8408 is 7174, I had a loan that started at 93 grand, I paid down, I paid 15 grand, but 8,000 was interest, the other 7,000 paid down the loan, what is my new loan balance, I'll take 7174 minus 93,418, hit the plus minus, it's 86,000, so it's just 93 minus seven is 86, 86,244, okay, last one, start with 86,244, I make a $15,582 payment, how much of that went to interest, well, 86,244 times 9% is 7762, and the principal then, if it's $15,000 repayment, 7,700 is interest, almost half, it's basically almost right on the nose, half, 15,582 payment minus 7762 means 7820 went to pay down the principal, so I had an $86,000 loan, I paid off $7,800 of principal, 86,244 means if I take 86 minus seven, I end up at 78,424, 78,424, and we could keep going, and in fact, I did this in Excel previously as I was setting it up, and you can see Excel, I went to the penny, but let's just sort of compare notes here, so a couple of things happen as you look at this, the first is, we matched, right, 93,418, 93,418, 86,244, 86,243, 60, 78,424, yes, right, we got it right, so congratulations to us, we've solved the problem, thumbs up, the only other thing I wanted to call your attention to is when you look at one of these long ones, like if you have a 30-year mortgage or something like this, you will, I look at these things and it kind of makes me want to puke, and what makes me want to puke is when I look at the total payments, 15 times 10 is $155,820 in 10 cents, so $155,000, remember, we borrowed $100,000, 9% interest is not insane interest, we paid, we make $155,000 in payments to pay back $100,000, we paid $55,000 of interest, that's like a year's salary for, and like a pretty good salary, and that's not even a ton of money that we borrowed, $100,000, so you borrow $100,000 for 10 years, you're giving up a year's salary to do it, whenever I see those numbers, I always total up the interest, and I always think, it's good not to get into debt if you can avoid it, right, these very long-term debts just sap away at people's cash flow, so I know there's a finance class, there's a lot of lending and borrowing going on, but on a personal level, whenever I look at one of these tables, I always get uncomfortable, I think that's what makes me an accountant and not a great corporate finance professor, so I know I'm teaching finance material, but know this, I'm an accountant, and I'm very financially conservative in my own life, because I look at tables like this, and they make me very uncomfortable, anyway, hopefully, this problem didn't make you uncomfortable, and hopefully, you're comfortable hitting one of those buttons, I'm just pointing at the air right now, but I think on the screen, I'm pointing at the buttons, thanks for watching, I'll see you in the next video, bye-bye.