Application I: Fundamentals of a Loan Part 3

Welcome back to the first mega problem. I just love this problem [LAUGH]. Okay, so stare at 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 seemingly complex problem. So you plan to attend an in-state collage, you're borrowing $100,000, your parents on your behalf. The interest rate is 6% but here's a 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 a lot of twists, to the beauty of finance and making a 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 one to five, right. But actually what you wanna 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, n becomes 5x12. So make this 1 through 60. This is the probably the most important step. Which has nothing to do with finance, but has to do with problem solving. So your period is up to you and dictated by the nature of the beast. If it's monthly payments, period is actually 60, this will simplify problem very easily. Okay, simplify easily is 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 would divide this by 12. So the monthly interest rate becomes not 6% but 0.005, right? Because you're dividing 0.06 by 12, okay. So what have you done? You matched these. So it makes a lot of sense to have the interest rate match the period. So 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 right 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 gonna stick here and say this, 0 PMT.

Do this. Do 5, multiplied by 12.

Comma. So you've covered, I mean sorry. Let's just, I made a boo boo, so let's, keep the boo boo. Because boo boo's important. 0.06 divided by 12, is the interest rate. The second number is what? N, but in n is 5x12. You've gotta be consistent, right? Because of the interest rate is per month, the number of months should be in the next thing. And now what do you wanna press?

Question, when you are 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. It'll work out to be if have the number right. 1933.28. So what is 1933.28? 1933.28 is the monthly payment you'll have to pay in order to pay off the loan fully in 5 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 it before hand, 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 gonna write BEG 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 for yourself. First of all, will this be five year, five rows, or will it be 60 rows, right. So let's start off, the beginning balance in month one 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 6, so you have 0.06 divided by 12, right? So how much will you owe as an interest component of it?

If you see this, this is about half a percent, right? So this'll become 500. And how much is left? 1433.28. Look, now we go to period 2, which is month again. How much do I still owe? I owe 98566.72. How did I get this number? Very simple. I'm 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. 45. I've written two rows. Please make sure you can do the rest. And I'm not hurrying for hurrying's 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 with the real mega 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, make the 18th payment. How much do you think you owe the bank?

So many people will start here, and I tease people who are done a lot of accounting, and they work their way forward, the amortization table it will take a life time and then you die. It's not too much fun, yes, but finances. Finance's the value of anything after you made the 18 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 did a little boo boo earlier? So you do 0.06, but you won't stop there. You'll divide by 12, right? Now how much n is left?

It is 60, not quite. You've 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 42. 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'll just figure it out and write it for you. The answer is 73,074.70 bucks. This should be answer, the interesting thing is even though you made 18 payments you still owe a lot.

You still paid back only about $27,000 of the principal, and the reason is in the initial PV what are you mostly paying? Given that you've 60 PV, you are 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 to 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 PV balance. So PV at fourth time. And 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 end 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 pay how many? 36, and it works out to be 43,620.34. This is the beginning balance of the 37th month. Now I am asking you what is the interest component of the next payment? How much is the next payment?

How much is the 37th PMT? You know it is 1933.28 because the amount I'm paying every year, every month is fixed. Although 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 also 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're paid off 36 payments. One more step and we'll, 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, 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 are 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 + 0.06. But that's annual. The monthly rate is 12. So I'm writing out, what would 1 buck become after 12 months?

It will become buck $0.06 if there was no compounding within the year. But this monthly compounding, I'll raise it to power 12. So what is this? This is the future value of 1 buck with monthly interest rates and compounding. But I'll subtract out the 1. To get you to the place where you want to be, which is what is the interest rate that is effective. And the formula, genetic formula, is 1 + r divide by k raised to power of k minus 1. There what is k? K is the compounding interval.

This is very easy to figure out right what 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 semiannual k is 2.