 In this presentation, we're going to think about our loan payments. We have a loan outstanding. We're going to make payments on that loan. We're going to have a principal and interest portion that we're going to have to break out for those payments. And therefore, we're going to be constructing and looking into an amortization table. Time to engage with SAGE 50, Cloud Accounting. Here we are in our Get Great Guitars file. We're going to be opening up our financial statements to consider our objectives. So we'll go to the reports drop down. We're going to go down to the financial statements. We want to be opening up our favorite, that being the balance sheet. So we'll open up that balance sheet, changing the dates this time for February. So it's actually in the correct range that time. So that's good. It's going to keep it where it's at, month of February. Going to say, okay, now we're going to be going down to the loan payment information. You'll recall last time we consolidated our loan into the 72,001 loan amount. We want to think now about the payments on the loans. When you consider the payments on the loans, you have kind of a couple different options that you may want to set up for making the payments. And oftentimes you think about the accounting department as separate from basically the adjusting department or possibly the accounting department from the CPA firm that might help with tax preparation and financial statement preparation. So you might do some different methods between those two departments. For example, if we have a loan such as this, typically we're going to have a short term and long term portion. That's going to be an adjusting type of entry. In other words, part of this is going to be due within 12 months. And we would typically want to break that out between short term and long term. I wouldn't do that each payment, however, because that would be quite tedious. And therefore we would do that periodically at the end of the time period. And then we have the payments. The payments are typically going to have an interest in principal portion related to it. So to break out the interest in principal portion, we would need an amortization table, which sometime is provided to us by the loan when we take out the loan. But sometimes it's not. Sometimes all we know is the payment amount. And we need to basically create the amortization table if we need it. Also, it could be a little bit more confusing even if we have the amortization table to break out between interest and principal. So another method you could use is to simply say, hey, look, at the end of the time period, I'm going to have the accountant or the adjusting department adjust, make the amortization table, and then make the adjustments to the amortization table recording the interest portion of the loan periodically at that point in time. So those are just a couple methods you can think about. To further understand this, let's take a look at and actually create an amortization table just to think about how it would go through. So we're going to do that here. We're going to be spending some more time in Excel. This is going to be kind of an Excel intensive area. We won't get into too much detail on Excel. In other words, I'll go fairly fast in it because it's not an Excel course. But we wanted to show just the amortization and what type of typical problem that would have and then use the amortization table to record the actual transactions and then show a couple payments and see why the loan payments can be a little bit tricky to track. So here we have our 72,000. We're going to say it's going to be 60 months, which is going to be five years, right? Five years is 60 months. In other words, if you took obviously the five years times 12, you would get the 60. We're going to say the rate is going to be 5%. Now, normally in a loan, you're typically going to be given the payment amount and you'll know the loan balance typically and the rate. So you'll typically have those, but then you won't know the interest and principal portion that you're going to have to be breaking out per payment. But it's possible that they don't even give you something like the interest rate or something like that on the loan. They give you just the payment amount and you'd have to basically impute the interest rate to make the amortization table. Let's just show how that would be first. And first I want to think about the payment calculation. So if I knew, in other words, the loan, the number of periods and the rate, then let's use Excel to just give us the payment amount. Then I'm going to use that format, that formula to then back into the rate if we didn't know what the rate was. All right, so let's think about that. We're going to say the loan payment is going to be a typical kind of formula here. So I'm going to go to the formulas. We're going to go to Insert and this is going to be a present value. Present or I'm sorry, this is going to be payment. Let's say loan payment. So this is going to be a payment calculation. So this is the one we want payment calculation. So I'm going to say, okay, it'll give us a dialogue box. I'm going to select the rate. This is the trickiest component because the rate is 5%, but that's per year. I'm going to take that and divide it by 12. That would give us the monthly rate. Then the number of periods, the number of periods is going to be this item, 60. And then the present value, the present value is going to be the loan amount, which is the 72,000. These two, I don't need these two items down below. That's going to give us our amount of the 13 5872. Notice it is rounded. So we're going to round this item. So we're going to say, okay, there's our payment rounded to the dollar. Now, if I want it to be a positive number, I could double click on this. And I usually just put a negative in front of a P. And that basically takes the entire thing and multiplies it times negative one or flips the sign, however you want to think of it. So there we have it. Now there's our normal formula. Now the normal thing that people don't know, however, is usually this number. In other words, they know this number, but we don't know possibly the interest rate. So what if I have this entire thing here if I said, if I copied this and I didn't know the interest rate? The reason I want to look at this this way is I don't need another formula necessarily to calculate the interest rate. I just, I'm going to use the same formula, which is this formula. And I'm just going to say, I don't know what this number is. I'm at a zero interest rate. I know this number, this number needs to be that, right? But I'm going to use this formula that's currently in there to figure out this rate. So the yellow one is the one I'm going to figure out. So this is one way you can do this really useful technique for a lot of different areas. So the way you would do this is you'd say, OK, I got this information. I put this formula in. It's not giving me the right number because I don't know this number, but I know what the formula there should be. Let's ask Excel to then figure out the zero balance through just basically trial and error. To do that, we're going to go to the data section. We're going to go to the forecasts. We're going to use the what if analysis and then the goal seek, the goal seek. So notice I'm not on the sale here. I'm just going to, I'm basically going to not be on the sale that we're adjusting. That's kind of weird for Excel, but that's the way it is. And then I'm going to say, Hey, Excel, this is, this is me asking Excel. We're going to say, Hey, Excel, would you please set this number to be? I'm going to hard code it to what I think it should be one, three, five, nine. That's rounded, but hard coded to what it should be. That's the payment amount. And then I would like you to do that by changing this number. So once again, Excel, would you please make this number be 1359 by changing the yellow number and say, OK, and it just does trial and error and can basically figure that out. So that's that's one thing you can figure out. You can say, OK, now if I need to figure out the interest rate, I can kind of impute the interest rate or any other unknown. If we didn't know the number of payments for whatever reason, you could do that. All right, I'm going to, I'm going to delete this. And then I'm going to create the amortization table. So amortization table, I'm going to say one, two, I'm just going to say the number of payments. I have two payments. I'm going to drag this down to get to 60. And this is going to be auto fill. I'm using auto fill to just fill down the, the series of numbers here. And we're going down to 60. And actually, I would like it to start at zero. Let me start that over again. I would like it to start at zero, then one, two, then I'm going to highlight those two. And I'm going to bring that down to 60 payments. So it's going to go down. There it is. And I'm going to say, all right, down to 60. All right. And then the payment amount is always going to be the same amount. I'm going to pick this one up again. Remember, it's rounded. I'm going to pick that up the, the interest actually, let me delete that. For zero, time period zero, we're going to prick the principal, which is 72,000. That's it. And then I'm going to go down to payment one. And then we have the payment amount, one, three, five, nine. Now the interest is going to be calculated as the 72,000 times the 5%. But that would be interest for a year. So I need to take that and divide it by 12 because we need the interest per month. In other words, if we take out the trusty calculator, and let's do that with the trusty calculator. So I'll pull the trusty calculator up and up here, we'll say, let's take a look at this. I'm going to make it a little bit smaller. Can we make that a little bit smaller? And then we're going to say that if we have the 72,000 times the 0.05, that would be for a year, interest for a year divided by 12. That would be for a month. Okay. And then I'm going to say then that if this is the amounts equals the amount of payment minus the amount allocated to interest, this is the principal. So in other words, this is like the rent. This is like the loan, the rent on the money. We have to pay back the rent. And then the deduction of the principal is only the 1,059. So then we're going to say the 72,000 minus the 1,059 is what is left on the loan. So note, if I do this for payment two, it's going to change things. Notice the payment's the same. But of course the interest is different now. Why? Because it's got to be equal to the new balance times the 0.05%, so the new balance is different, obviously, divided by 12. So now interest portion is only going to be 9 or 296 equals the payment minus that interest portion. This is the reduction in the principal then, which is going down. So you can see there's not a big difference because we're talking about because of the loan. But in any case, you can see this is going to be changing every time. That means we can't really memorize the transaction as easily if we're going to record the payment. So let's do that one more time. This is going to be equal now to the 69, 878 times the 5%. Actually I'm sorry, the payment is simply going to be the same. Then the interest is going to be equal to the new principal times the 5% divided by 12. So there's the new principal. The principal reduction is the 1359 minus the interest. So there's that. And now we have a different number there again. And then if I take the principal minus the amount of reduction in principal, there we have it. Now you might ask, can we copy this down? Could I copy this formula down? We'll try it. We'll use the autofill to copy it down. But it's going to have a problem. So I'm going to say, okay, let's try to copy this down. I'm going to put highlight these and then we'll use the autofill handle if it'll let me grab that, there it is. And there we have it. Now there's a problem. This number looks right. This number looks wrong. Why? It brought down the 5%. And I want it to keep that 5%. So what I'm going to use is an absolute reference. I'm going to close this back out or zero that back out. I'm going to go back to this one and I'm going to say this cell, that 5%, I don't want it moving anywhere when I copy it down. So what I'm going to do is I'm going to go to that cell, which is in B3. And I'm going to say F4 and the keyboard to put a dollar sign before the B and the 3. That's just going to be a code for Excel saying don't move that cell down. And then let's try this again. I'm going to highlight these and then I'm going to bring it down one time first just to test it out. And then we'll go all the way down. So that looks good. This looks good. This looks good. And that looks right. So now I'm going to copy it all the way down. Once I get to 60 payments, this amount should be at zero. And that's how we can verify that our amortization table is doing what we would expect it to. So I'm going to highlight these. I'm going to put my cursor here. I'm going to autofill all the way down. It's not autofill. This is autofill. It's an autofill all the way down. So auto is driving it down. And then once I get down there, Dr. Phil does the calculations autofill. And then we have the zero at the bottom. So the zero at the bottom represents the fact that it's going to be fully paid off at the end of the loan. Notice the difference here. This amount is the interest. I mean, this is the payment. This is going to be the interest amount, which is very low now. And most of it is a principal reduction. Why? Because there's not much interest being paid on that principal balance. So you can see this change between interest and principal. Very big change over the life of the loan, even though the payment is the same. So now when we record the payments, as we'll do next time, we'll use this amortization table to record the payments. Just realize that you could do this a couple different ways now. Every time you make a payment, you could properly break out the interest and principal portion, which will take three accounts each time. Before you could say, hey, a accounting department or adjusting department, I would like you to make the amortization table and I'm just going to reduce the loan by the full amount. And I want you in the adjusting department to then adjust out or break out the interest periodically at the end of the month or the year. And that could just be part of the loan kind of adjusting entry process. So those are the options that we have. We're going to take this amortization table next time and we're going to record the first two payments so we can see how those first two payments can be recorded and how they will differ given the fact that there's a different amount of interest and principal portion for them both. That's it for now. Let's get out of here.