[MUSIC] Welcome back to the first mega problem, I just love this problem [LAUGH]. Okay, so state of the problem definition I've changed the previous one slightly to show you the real world twist and the fact how beautiful our thinking can lead us to solve very complex, seemingly complex problems. So, you plan to attend an in-state college, you're borrowing $100,000 your parents on your behalf, the interest rate is 6%. But here's the real world twist, the monthly payments are needed in other words, the bank wants you to pay money every month. This will expose you to the real world, to lots of twists to the beauty of finance and making your problem look as simple as before. But how do you do this? So the first thing I would do is, I would draw a timeline, and 1 to 5, right? But actually what you want to do if you're really cool about it is make your life very simple. If there are five years, how many months are there? So number of months m becomes 5 times 12, so make this one through 60. This is probably the most important step which has nothing to do with finance, but it has to do with problem solving. So, your period is up to you and dictated by the nature of the beast, so the effects monthly payment periods is actually 60. This will simplify problems very easily, okay? I mean simplify easily it's kind of redundant, but who cares, right? Okay, now here's a trick the r given to you is called a stated rate or APR, and it is stated yearly. Almost all interest rates are stated yearly, but if the payment is monthly, then you divide this by 12, so the monthly interest rate becomes not 6% but 0.005, right? Because you're dividing point 6 by 12, okay? So, you know what have you done? You've matched these, so it makes a lot of sense to have the interest rate match the period. So if this is one month. R is equal to 0.06 divided by 12, so you've figured out n is 60, r is 0.06 divided by 12. And I will write out how to do this and we won't go to excel because I'll do it with you. So let's figure out what the monthly payment is, let's do it. So I'm going to stick care and say this zero PMT. Do this, 0.06 divided by 12 is the interest rate, the second number is what? N, but in n there's 5 times 12, you gotta be consistent, right? Because if the interest rate is per month, the number of months should be in the next thing. And now, what do you want to press? BB. Question, when you're paying annual you're paying about $23,739, what will you have answer here? I think if you press Enter, your PMT will be much less because you're paying monthly and will work out to be if I have the number right, 1933.28. So what is 1933.28,1933.28 is the monthly payment you will have to pay in order to pay off the loan fully in five years, which is also equal to 60 months. So you see, I made a little boo boo up there, you got to be careful how you enter it in Excel, but you know, okay? So fundamentally what has changed here is the timeline has changed. And therefore I would encourage you to kind of think about changing the timeline first and the most important piece is to make n 60, and to make r 0.06 divided by 12. And you can do that in Excel, you don't have to do that before and Excel will understand what you're doing, okay? Now let's do one thing, let's try to do the amortization table, I will leave it up to you to do but I'll start off, okay? So I'm going to write big balance. And then PMT payment, interest, and repayment, I'm going to write quickly. But if you want to have fun and want to learn this, do this yourself. First of all, will this be five years, five rows or will it be 60 rows? 60 rows, right? So, let's start off, the beginning balance in month 1 turns out to be 100,000. How much are you paying? 1933.28, and I'm going to write a little faster, right? So, interest rate is how much now? Interest rate is much lower than six, so you have 0.06 divided by 12, right? So how much will you or as an interest component of it? If you see this, this is about half a percent, right? So this will become 500, and how much is left? 143 3.28 look, now I go to period two, which is month again, how much do I still owe? I owe 98566.72, how did I get this number? Very simple, I am writing it quickly but the idea is very simple, take this and subtract it from this guy, right? How much is the payment? I'm paying still 1933.28, what will happen to the interest? It'll drop to 492.83, and how much are you left paying? More or less? More 0.45, I've written two rows please make sure you can do the rest. And I'm not hurrying for hurrying sake, I'm just doing it simply because you need to understand that the mechanics and the thinking are both the same. Let's go to couple of interesting questions and we'll be then done, we'll make a application one. And the first question is the following, how much will you owe the bank after the 18th month's payment? Right? So instead of doing the amortization table long, if you can answer this question, you've arrived, because you basically can fill in the amortization table, I think. So think about this, what am I asking you? I'm saying now you have a 60 n and I'm asking you made the 18th payment, how much do you think you owe the bank? So many people will start here, and it is people who have done a lot of accounting and they've worked their way forward amortization table. It will take a lifetime and then you die, [LAUGH] so it's not too much fun. Here's what finance says, finance says, the value of anything after you made the 18th payments is what remains. So this is what you'll do, you'll do PV equals, what is the first number you press in PV? Remember I had told you earlier, so you do 0.06 but you wont stop there, you will divide by 12, right? Now, how much n is left? It is 60, not quite, you paid 18, so how much I left? 42, but if you write it this way, it's pretty obvious now, right? So you have 0.06 PV, how many payments for? And what was the payment? We know the payment already, because we already know what the bank is charging us every month. What is the answer of this? The beauty is, the answer is, and I will just read, figure it out and write it for you, the answer is 73,074 bucks and 70 cents. This should be our answer, the interesting thing is even though you made 18 payments, you still owe a lot. You paid back only about $27,000 of the principal and the reason is, in the initial period, what are you mostly paying? Given that you've 60 periods, you're mostly paying interest. But as you pay off more and more of your loan, what ends up happening is you gradually pay off your whole loan over time. So let's go a little bit further and ask the following question, what is the interest component of the 37th months payment? As I said, you can pause do it yourself, do it in Excel or do it with me, it's your choice. I would encourage you very strongly of every time you want get intrigued by a problem, to take a break and do it yourself. So this is two steps, step one is when you know that you have to figure out some PV. Because the interest component is very easily calculated, if you know the beginning of period balance. So, PV at what time? In after paying 36, because 37th is the next one, so this is what you do. 0.06 divided by 12, this is the amount you owe after 36 payments, so how much and left? 60 minus 36 which is 24, and you know your payment is how much? 1933.28. This amount if you press Enter, is the amount you owe after you've paid how many? 36, and it works out to be 43,620.34, this is the beginning balance of the 37th month. Now I'm asking you what is the interest component of the next payment? How much is the next payment? How much is the 37th PMT? We know, it is 1933.28 because the amount I'm paying every month is fixed. How do I figure out the interest component of this? The interest component of the payment is 43,620.34 multiplied by 0.06, divided by 12. And this answer works out to be $218.10, so what do you notice? That the interest component of this has dropped quite a bit, right? Because what has happened is, your beginning balance is going down, now you paid off 36 payments. One more step, and I promise I'll take a break but as you said, you can always take a break. And the reason I want to do that is we've been on this problem for a long time. And the thing that I want to raise here is the concept of Effective. Annual Interest. What does that mean? That means what is the actual interest rate with compounding that you're actually paying annually. Remember the stated rate. Is 6%. Turns out, if your compounding interval is one year, the 6% is also effective. But if your compounding interval is one month, the actual interest you're paying over the year is more or less than 6%. Remember compounding it's more than 6%, right? So let's write out the formula, let's do it in our example, the effective annual rate turns out to be and you'll see this in a second 1 plus 0.06, but that's anvil. The monthly rate is 12, so I'm writing out what would one buck become after 12 months. It would have become buck 6 cents if there was no compounding within the year, but this semi, I mean monthly compounding, I'll raise it to power 12. So what is this? This is the future value of one buck with monthly interest rates and compounding. But I'll subtract out the 1 to get to the place where you want to be, which is what is the interest rate that's effective. And the formula generic formula is 1 plus r, divided by k, raised to power k minus 1, where what is k? K is the compounding interval. This is very easy to figure out, right? What's the compounding interval is because it will be stated, or in the case when you're taking a loan it will be determined by how often are you paying the PMT. If it's annual, k is 1, if it's monthly k is 12, if it's semi annual k is 2.