Chapter 6 Video: Loan Table

This Excel tutorial deals with an amortization schedule. In this example gonna take a look at buying a car. Cost of the cars in B2 is $4,000, which means basically I bought a used car. I can get a half percent interest per month and I want to pay it off in 12 months. Now, what I'm trying to figure out is when I make a payment, how much it goes to interest, how much goes to principal and what's my ending balance? In order to do this amortization schedule the first thing I need to figure out is, what is my payment? So my payment will be equal PMT. My rate is in B3. I'm paying it off over 12 months, which is B4 and my present value is a negative B2. And you can see that my payment is $344.27. Now, let's look at our amortization schedule. In month 0 my balance is B2, the cost of the car, $4,000. When I make that first payment to figure out how much interest of that first payment, how much interest is taken out of that first payment, that is simply equals B3 times my ending balance. Okay, which is $20 of that first payment went to interest.

Now to make it easy for these calculations I'm going to lock B3 because that never changes. I do want the E9 to E column to increment because the balance changes after I make each payment. Now, to figure out how much went towards principal, that's very easy. I'm going to take my payment and subtract off my amount of interest that went in. Okay. And this instance again, the payment is locked never changes, but the interest the C column is going to increment so I'm not going to lock it. So it turns out that three hundred..$324.27 went towards principal, which means after I make my first payment my ending balance is E9 minus D10. Now I want these to be able to increment when I change the formula, so I'm not going to lock any of those cells. Okay, now that I've got this set up, I can simply highlight these three grab the corner and I can finish my amortization schedule. Very easy to do. Notice that the last payment I make only a $1.71 goes towards interest, the rest goes towards principal and the loan is paid off. Now you can also actually not develop the amortization schedule. You can actually directly find a remaining balance in Excel. It's pretty easy to do.

So, I'm just going to type here balance after the sixth payment. Now to do this what you're doing is you're taking the remaining payments and you're bringing them back to the sixth period, that's what you're doing. So take a look when I said those words here's what it looks like in formula. It equals present value, my rate is a half percent per month. Now, how many months do I have left? I have six payments left. So I'm going to put a 6 here. My payment was in B6 and that's all I have. So it turns out that my balance after the sixth payment is $2,029.92. Notice that I got the same answer in my amortization schedule. So you can either develop a schedule like this or you can directly calculate what a balance is after any payment by simply bringing the remaining payments back to the time period that you're interested in. In this Excel tutorial I've covered how to do an amortization schedule or to directly calculate an ending balance or a payoff amount.