 In this presentation, we will calculate the payment on a loan, on a note, on an installment note, and the annuity table for an installment note. Support Accounting Instruction by clicking the link below, giving you a free month membership to all of the content on our website broken out by category, further broken out by course. Each course then organized in a logical, reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems, PDF files, and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. In a prior presentation, we recorded the initial journal entry for taking out a loan, and that's going to be a very simple process for us to calculate the initial journal entry. Then we're going to have to record the payments that we make, and that's going to be a little bit more difficult. So when we break out the loan terms, we have the loan, here we have $100,000 loan, interest is 9%, number of monthly payments, there's going to be 36 monthly payments. To record this, it's pretty straightforward. If you see this in a book problem, you just record the cash and the notes payable, that's it. The more interesting or the more difficult thing, of course, is going to be the calculation of what the payment amount should be, and then how are we going to record those payments, because we're going to have to make a payment every month. Now, as we do this, we're going to go over here to our calculation here for our annuity type of calculation. A couple of ways that this happens in practice, one, we do a quick calculation just to see what the payment amount is, and then it's very useful to see the annuity table we needed when we're going to record the information on a payment-by-payment method. Note that most of us think of a loan as some type of installment loan. So if we take a loan out for our car, if we have a mortgage or something like that, we typically think of loans as all being in the same format, meaning we pay back the same amount each month, and that same amount is paying both interest and principal. That's not the only way we could format a loan. We could format a loan closer to how we see bonds formatted, meaning we can have the whole loan paid back at the end and just pay back interest as the loan goes, or we could pay back interest and principal at the end. So there's a lot of different ways we can actually format the loan, but to have the same monthly payment is a very common way because it does give people standardization. We can say, okay, we're just going to pay the same amount each month. So that's what we are working with here. We're going to pay that same amount each month. So the problem with calculating that is that oftentimes when we see that in a document, they may actually give us the payment amount and the number of payments because that's what we really want to know. What we don't know when we are given that information is how much we're actually paying, you know, because we don't know the interest rate. We don't know how much interest we're paying over and above the original loan amount, the 100,000. So it's useful to have an amortization table. What we're going to do here is we'll do the calculation of the payment based on this information and then we'll amortize it so that we can use it to record journal entries in the future. Note that if we don't have any one of these information but all the rest of it, we can use an Excel function to figure out whatever is missing. In other words, if we knew the payment but not the interest rate, then we can calculate the interest rate and figure that out. So here we're going to use the payment function. We're going to just, we're going to go, let's do it up here. We'll go to the formulas up top, formulas, and we want to look for the payment. So if I type in payment, then it's going to be actually this one, PMT that we're looking for. So I'm going to say I want the PMT and we're going to say the rate. Now, the trickiest thing about this is the rate for most people because they're going to give us a rate of 9%, which is 0.09, but that's for a year and we have to make these payments every month. So really what we want to do, we have to get this down to a monthly pay period because we need to use 36 pay periods, not yearly periods. So to do that, we have to equalize the amount of interest per period. So we're just going to take that and divide it by 12. So that'll give us a monthly interest amount. And then the number of periods that we're going to have will be the number of periods is 36. And clearly if you were to think this out, you know, 36 divided by 12 is three years. So someone might say we're going to, you have to pay every month for three years. They're probably not going to say 36 times, but you say, okay, three years, that's 36 payments. And so we're going to say, all right, number of payments is 36. Number of periods is 36. And then the present value is going to be this 100,000, the loan that we have upfront at this time. So 100,000. And then it should calculate for us down here. We're going to go ahead and say, okay. And there we have it. It makes it a negative because it's a payment. So I'm going to double click on it and flip the sign. I like to see it as a positive number. Negative doesn't tell me too much. I'll just put a negative in front of the P. Some people will say you should put it somewhere else or whatnot, but that works. So we'll flip the sign. That basically just means you can want to take that number and multiply it times negative one. Just flip the sign. Okay, so there we have that. Now this is the payment we're going to have. Now note that most contracts, this is all they give you. But when we record it, if I record the interest in principle, I need to know how much of that is interest and how much is principal. And there's no way for me to know here because I'm going to make 36 payments. I know how much we're going to pay because we're going to pay 3180 times 36. So we're going to pay 14,480 total interest because that's over the loan amount. But I don't know how much per period we're paying in terms of interest, which I need to know because we need to record it each period when we make the payment. In other words, I know I'm going to pay 3,180 cash. How much of that should I record to interest expense versus the reduction of the principal? So to do that, we need an amortization table. So what we have here is a table that we'll build. So this is a common type of problem. If you know the format of the table, then this is really easy to do by hand or with Excel. Or at least for the few periods by hand. So you just need payments. I usually set it up this way. Payment and then interest amount that will calculate then the principal reduction and then principal. So just make a grid, put it on a piece of paper. You've got to do this by hand for some problems and just know this format. Just always write down this grid. And then most problems will only make you calculate the first couple. If we do it in Excel, then obviously we can do the whole thing very easily. So for period zero, I'm going to say the principal is just the 100,000, the loan amount. 100,000. We can even say it equals. Let's say equals this number. Then we need to know what the payment is. Now here we've got the payment. So that we calculated the payment. So now we're just doing the annuity table. So the payment is equal to this amount that we calculated. Now note that this could be rounded and it is. Is it rounded? Oh, it actually is. We made one that's perfect. Nice. So there's no rounding here. So then the interest on this is going to be equal to the 100,000 principal times the rate 0.09 divided by 12. That would be per year the 9,000 divided by 12 monthly, 750. Or you could think of it as 0.09 divided by 12, which gives us a very small rate. But that's the monthly rate. That's why we don't quote rates in a monthly term typically because they make really small numbers. Times the 100,000, 750. Let's do that here. I'm going to say this equals the 100,000 times we can pick up this rate divided by 12. So if we're paying this amount and this much is interest, the difference is principal. So the principal reduction is this amount, the amount we're paying minus the interest portion. That's how much is reducing the principal by. So the principal then left over after the first payment will be equal to the first month. Of course, this amount, the prior principal minus the principal reduction, the 97,570. So when we make this payment, we'll credit cash, debit interest, reduce the loan amount by this amount. And that will give us the principal of this number resulting here. We'll do that when we do some journal entries. Then we'll do the same thing here and then we'll try to copy this down. Once we do this all the way down for 36 times, we should get to 0. We won't do it 36 times, but we'll use Excel to help us to do it that many times. We'll do it manually one more time. The payment is going to equal the same amount. So I'll just say equals the amount above it. The interest is going to differ, however, so the interest is going to be the same formula, but it's going to use this number. That's what's happening here. The principal is going down, so the interest we pay in month two is a little less because the principal is a little less. So we take that principal times the 9% and then divide it by 12 to get the monthly. So now in month two, we're paying only 732 of principal of interest, I should say. And then that means the difference then. It's the same payment, but only 732 of it is interest. That means principal is going up. So the 3,180 minus 732 principal, the amount allocated to principal is going up, meaning the principal left over is going down by more each time period because we're making the same payment, interest is going down, and therefore principal is going up. So we're going to say this equals the prior principal minus the reduction in principal. Let's do this one more time and then we'll copy it down. This is three periods, probably all that a book problem would ask you to calculate here. So we're going to say the payment's the same. Interest once again is going to be equal to the new principal, which is now reduced, times the percentage divided by 12. So now it went down again and we're going to say that the principal reduction equals the principal amount here and then we're going to say minus the interest gives us the reduction in principal. And then notice it picked this one up automatically. I'll delete it and do it one more time. So that means that this is going to equal the 95-122 minus the reduction in principal. So the reduction in principal is going down and that's because the reduction in principal per payment is going up, which brings the principal down at a higher rate and that's because the payment is the same, but the amount of interest per time period is going down because the interest goes down every time the principal goes down for each payment. Okay, so now we're going to try to auto-fill this all the way down using Excel. Now we're going to select these one time, we're going to select these cells. I'm going to auto-fill it one time, see if it does what we want and if it does, we'll take it all the way down. If it doesn't, then we'll fix it and it's not going to do what we want. We'll have a problem we'll have to fix. We'll put our cursor on auto-fill, I'm going to bring it down one time and we can see that obviously we have a problem here and we have a problem here. So if I select that looks right, this one has a problem. What's the problem? It took this number, that looks correct, but then it brought this number down. It should be taking the 9% and it brought it down because it's a relative reference. Usually you would want to do that, but we don't want it to do it here, we want it to keep this one. So we need to fix that, so I'm going to say okay, and then this one does what we want but it's off because this is off and that makes everything wrong. So what we'll do is I'm going to delete that and we'll try to fix this cell so when we copy it down it does what we want it to do. So if we double click on it, we're going to say okay it's 012, which is this number. We want that to move down, that's right, but 04, this number, would move down if I copy it to 36. That's what we don't want. So we need an absolute reference and to do that we select F4 on the keyboard or you just put a dollar sign before the O and a dollar sign before the 4. We really only need it before one of them, but it doesn't hurt to put it before both. So we could use a mixed reference in other words, but we're just going to use an absolute reference because it doesn't hurt. So there we have that and that just means don't move it down. Dollar signs don't have anything to do with dollars, they're like a code for Excel saying hey don't move the cell down when we copy the formula. So we'll say enter, now select those four and we'll use autofill to go down one time and see if it does what we want. This looks good. That looks right now because it's picking up the right number. This looks okay, this looks good and this looks right. So now we'll autofill it all the way down selecting these four numbers, put our cursor on autofill and select all the way down 36 payments and that should bring this down to zero and that's how we really know it's correct. So in other words, if we did a calculation to figure out the payment amount or the interest rate, it's kind of like you're kind of in limbo just depending completely on Excel to know if it's correct or not. If you put it then into a table and it comes out correct meaning after 36 payments to zero, which isn't too difficult to do in Excel, then you have a lot better idea. Also the table is necessary in order for us to record the payments properly because the first payment is going to have to be broken out. All payments will have to be broken out. This is how much we'll pay, cash goes down. Interest expense will be this portion of it, debit that. The reduction in principal, debit to principal will be this bringing the balance in the principal account down to 97. Also note that interest, of course, as the principal goes down, the interest portion per month goes down because it's going to be calculated on the new reduced principal and that means that the amount of payment stays the same. The interest portion goes down to the amount of those equal payments is going to increase the principal portion. So we're paying off more principal, in other words, with each payment even though the payments are the same. So that's the confusing thing about making this type of loan, this type of installment payments and the thing that we have to understand when recording these. When you get a loan in practice, note that they often don't give you the amortization table. They'll just give you something like this and then you've got to figure out the amortization table and in order to make the proper recordings.