I call these mega problems. So mega-problem number 1. It's an application that you've seen before, but I'm going to show you the beauty of finance and beauty of the complex world. It's a loan and a loan I will pick up is for college. You've done it before, and then I will expand on it. If you look at the screen, you'll see a very familiar problem. You're planning to go to college and your parents and you get together and figure out you need $100,000 and at time 0, and you're going to take that loan and it's required to be paid over five years, r is six percent, n is five years, and PV is a $100,000. I put a comma here but don't put it in Excel so just keep reminding you. What I'm trying to solve for here is how much will you every period starting one five times. The thing I will retain is the yearly. Let's redo this in some senses very quickly so you'll do equals sign PMT and then you'll enter, sorry, 0.06, n is five and PV is 100,000 and the answer you will get is I'm just looking over to make sure 23,739.64 so 23,739.64, we did this problem before. What I'm going to do is retain the ease of calculating this. By that, I mean yearly payments even though we'll make it more complex. Let's see, one of my most nifty ways of teaching finance. Stare at the screen and you'll find something called an amortization table. Now this will sound really complex, but it's not and I will walk through this with you, with this problem to teach you how a loan works. What is beginning balance? Just let me explain, so year is 1, 2, 3, 4, 5. Following convention, everything is happening at the end of the period. When we say yearly payment, it's not happening at time zero, it's happening at time 1, 2, 3, 4, 5. But it's very important to remember that this is the beginning of the period to the beginning balance that you notice on top. What will it be in the first year? That's the amount you start off with, let's write it out, 100,000. What I'm going to do now here is work through how the loan works and you if you ever have the desire, start doing this in Excel because you can set it up very well in Excel. Question is 100,000 beginning. Now, what is the yearly payment? We already know it. Another word for this is PMT and remember we solve that number to be 23,739.64. Again, if I make some errors, I'm going to try to catch them. If not just say gotten with a buzzer, I know how to do this so just write it out 23,739.64, what I like about the amortization table is not the table in itself, but how it works gives you a sense of how things are working in real life. Now, virtually every payment has two components and sometimes one of the components is zero, we'll talk about that later. What are the two components? The first component is interest. The second component of this yearly payment is principal repayment. Because that's how it works. You pay interest for the use of money, but then you also pay some of the money you borrowed 100,000 because if you don't do that, you'll be in what is called an interest-only loan and the loan will stay forever. There are things like that, but let's stick with the most normal stuff, which is how much is interest? The way to work this out is, you know, the annual interest is 0.06, interest rate, but how much is the interest you're paying in terms of dollars? This times the beginning balance. Interest is how much is the percentage rate determined by the marketplace given to you by the bank and how much did you borrow at the beginning of the period. Because the interest is for the use of the money, how much is this, 0.06 times 100,000 is 6,000. Very simple. But please remember that interest is a function that you pay at the end of the year. The cash flow is a function of what you borrow at the beginning. Then, well, how much is principal repayment? Very simple. Take this yearly amount and subtract 6,000 from it, so how much is left? 17,739.64. Now, what's the next step? You have done one payment at the end of the first year. You know you've paid an interest of 6,000 but 17,739 is what you've paid on the beginning balance, so what's the beginning balance at the beginning of Year 2, beginning balance? Sounds pretty mouthful. It is this number, subtract repayment from it. How much are you left with? 82,260.36. I'm going to be a little painful. As I said, I'm going to do these problems with you, so editor, how much are you paying at the end of the second year? Same amount. Think of this amount as a contract. Now a question is, how much interest is the component of this 23,000? Has it gone up or down? It's gone down because six percent of 100,000 is more than six percent of 82,260. How will you figure out the interest component? Again, simple. Take six percent interest and multiply by the beginning period balance. I'm going to write it out because it's a little bit more difficult than multiplying six percent with the 1935.62. I know I'm in the right direction because the number is going down. How much am I repaying now? Think about it. I'm repaying more or less. I'm repaying more because the payment I'm making is fixed and the interest has gone down, so the amount I'm repaying is 18,804.02. As I said, you can do this by yourself. You can now set up, I've done enough. You can set up a spreadsheet which actually models all of this. It's very simple. You start off with a payment that's fixed, so let me just write these all down. You've got all the payments that you're paying fixed amounts every year, what's changing is everything else in the table, but in a very systematic way. That is the interest is going down and the principal is going, repayment is going up till what's happened at the end of Year 5, you paid off everything. Let me quickly enter these numbers. 63,456.34. How did I get that? I took 82,000 and subtracted 18,800 from it. What will this be? This will be a number less. It's about 3,807.38, and I'll have a number here that's higher than 18 and it's 19,932.26. I'm talking so that you can compensate for my bad handwriting. Sorry, I'm laughing. But it's funny to me. This is 43,524.08. This is 2,611.44, and this is 21,128.20. This number is this number minus the repayment, which is 22,395.89. You paid this interest is 1,343.75 and 22,395.89. What is the cool thing about it? This amount and this amount or equal, which means what? The beginning of balance at the beginning of Year 6, which is end of Year 5, how much do you owe the bank? Nothing, so the thing to remember here is please use Excel and create this. Take the time to do that. You will be, you'll feel very cool. This is one application where I will now take a break, because I want you to work on this, create the Excel spreadsheet. We'll come back and then have some fun with this, and I'll show you how beautiful finance can be.
