In the previous screencast, you learned what an amortized loan was. In this screencast, I'm going to show you how we can use the principles that you learned previously in that screencast to create an amortization schedule in Excel. We've got a principle or a current loan amount, and we're going to make loan payments over time. So for a particular interval of time, we're going to earn interest as the blue circle here, the blue region here. The interest for any compounding period is just simply the principal times the current rate for that compounding period times the compounding period. Then if we make a monthly payment to this over time, we're going to buy down or pay off the principal on the loan. A cash flow diagram is oftentimes useful for visualizing cash flows for a particular savings account or loan as a function of time. Here, we're going to have an initial loan amount because that's coming to us. So that's our initial loan amount and then over time, we're going to be making payments at constant intervals. By the way, this is the borrower's perspective. If this is the lenders perspective, it's going to be opposite. In this screencast, we're going to look through a real life example. You buy a house for $400,000 at an annual interest rate of five percent. Your down payment is $50,000, bringing the loan amount to $350,000. So that's going to be the principal. Interest is compounded monthly and the loan term is 30 years. We're going to answer the following questions. What will be your monthly payment? We're going to create an amortization schedule. Over the lifetime of the loan, how much will you actually pay the bank? Finally, how much has the bank made off of you? Note here that we are neglecting bank fees and closing costs, etc. So I've got this in a starter file called Amortization schedule.xlsx. It looks pretty complicated, but we're just going to do one thing at a time. I've got the basic information here, I've got our annual interest rate, i. Again, in this course you'll see me using i and r to mean the same thing. It's an interest rate, this is an annual interest rate. The principle on the loan is 350,000. That's positive because we have received that from the bank. The number of years is going to be 30. The first thing we're going to do is we're going to calculate the monthly payment. To calculate the monthly payment, we're going to use the payment function and this is going to allow us to determine the periodic payment for a loan. Let's go ahead and put in a function there. Before we do this, I'm just going to go ahead and highlight those cells. I'm going to go up here to the Formulas tab, Create from Selection, click Okay. We've just created names. So we have names i, p, and n, which will make it a little bit easier to use in the rest of the spreadsheet. So I'm going to put a function here for the payment. The rate is going to be i. Now remember, if we're compounding monthly, then we have to divide that annual interest rate by 12. The number of periods is going to be the number of years. That's 30 times 12 months per year. So we have a total of 360 payments that we're going to be making or compounding periods. The present value, that's our principle or the loan amount, that's going to be P and then the future value, we want it to be zero. So we can either leave that off or I can just type in zero. As always, the type is going to be zero, so we can leave that off of the end of this function. When I press Enter, what this means is we're going to be making monthly payments of $1,880 every month. That's a negative value because we are giving to the bank that amount each month. Now what we're going to do is we're going to set up an amortization schedule. The amortization schedule just goes from the starting month all the way to the ending month. If I start here and I do Control+Down, we see that it goes all the way to 360. So I've already set this up for you. Control+Up and get back up to the top. The beginning balance of this loan during the first month, I can just type in equals P. Recall, that the interest in any single compounding period is just going to be the principal times the rate times t, and t is just going to be one month for each of these. Before I put in the interest though, let's put in the payment. I'm going to make the payments all be positive numbers here. I've already named that A, so in the payment cell, I'm going to have to do negative A, because I want to represent everything in terms of positive quantities here. This is typically what is presented in an amortization schedule. So we're going to make a payment each month of $1,879. The interest rate of every month is just the beginning balance, that's the current principle times our interest rate, i. But we have to divide by 12 because it's a monthly rate. That means that in the first month we have to pay the bank $1,458 of interest. Now we're making payments of $1,879 every month, the interests is only about 1,460. We've covered the interests and the remaining amount left over from our payment goes towards the principal. So what I can do is I can subtract, I can take my payment and I can subtract the interests for that particular month. That means that $420 goes towards the principal or our balance. The ending balance then for that month is just the beginning balance. Then we subtract the amount that goes towards the principle. We do not subtract the interest because that's what the bank gets. The bank gets that to take home and do fun things with. We just take the principal and we subtract from our beginning balance to get the ending balance for that month. You notice that in the first month of our $1,880 payment, most of that is going towards the interest. As we pay down our principle, what you'll see is the interest becomes less and more of that can go towards the principal. For the next month, the beginning balance, I can just put a reference a pointer formula to the ending balance of the previous month, the payment, this is going to be the same for all of our payments. So I'm actually just going to take this and drag it all the way down to Month 360. Then I'm going to scroll back up. We can do the same thing because I made this formula as a relative reference. I can just copy this down, and that means because the beginning balance is a little bit less than Month 1, the interest is going to be a little bit less. That means the amount that goes towards the principle on the second month should be a little bit more because we're paying the same amount every month, and then I can drop down this formula and it looks like everything is working properly. So I'm just going to take the formula here in cell B11, double-click to bring that all the way down. Now some of these are blank because they depend upon the ending balance, which we haven't put in there yet. The equations are there, the formulas are there. I can do the same thing here. I can double-click all the way down and it fills to the 360th month down here. If I scroll way down, and let me actually split the view here. So I'm going to split the view and I've got my ending balance and my month and so on here. You can see that after the 360th month, the ending balance is identically zero. So this is the payment schedule for this loan, it is known as an amortization schedule. Let's answer a couple more questions before we go here to this payment lookup. How much will you pay the bank? The amount that you're going to pay the bank is going to be n number of months times 12 months per year times our payment. I'll just put in a negative A, so that's our payment. When I press Enter, this means that we have paid the bank in those 30 years 676,000. We only borrowed 350,000. The difference then between the amount that we pay the bank and our loan amount, which was our original principal is 326,000. So over 30 years, the bank makes 326,000 off of you. You've almost paid double for your house, but it's spread out over 30 years. You're paying a little bit extra every year for the convenience of the loan, but this is how banks make money. Let's now complete this last part. I have it here as a payment lookup, maybe you just want to be able to put in the payment number. So this is the month and you want to know of that payment, so the fifth payment here, how much is going to interest in how much is going to principal? Well, Excel has this nice I payment function, IPMT that calculates the interests part of a periodic payment for a loan. It's very similar to the payment function. It just has one more argument. The only difference now is the second argument, which is per, that's the period for which you want to determine the interest paid. There is also a PPMT function. This calculates the principle part of a periodic payment, how much of that payment goes towards the principle. It's got the same arguments as the IPMT function. So let's go ahead and implement these in Excel. If I want to calculate the amount in the fifth payment that goes towards interest, I can just do IPMT. The rate is going to be i divided by 12. The period that I'm interested in is going to be the payment up there, so the fifth period. The number of total periods is going to be n years. That's 30 times 12 months per year. The present value, now this is always the starting amount, so that's going to be P, our principal. Our future value then is going to be zero and I can close that parentheses and press Enter. That means in the fifth month, our fifth payment, we're paying $1,400.51. We can go down here, and you can see that in our amortization schedule that we created, it lines up nicely. Then let's finally use the PPMT function. Our rate is i over 12, the period that we're interested in here is five. The number of periods is n times 12, the present value is our principal, and the future value is zero. That means that in the fifth month, $427 is going towards the principal. The nice thing about this is we can maybe years down the road, the 346th payment, you're going to be paying a lot more of your payment that's going to go towards the principal versus interest. So hopefully, this screencast showed you how to make an amortization schedule in Excel, hope you enjoyed.
