This presentation and we’re going to create a loan amortization table. And this will help us to track our loan payments and break out the principal portion and the interest portion of them. Let’s get into it with Intuit QuickBooks Online. Here we are in our get great guitars file, we’re going to first start off with our reports, we’re gonna go down to the reports down on the left hand side and then we’re going to be opening up our favorite report that being the balance sheet report, opening up the balance sheet report scrolling up top, we’re going to be changing the dates from a 10120 to 1230 120. Then we’re going to run that report. Then I’m going to close up the hamburger to get it out of the way so I don’t doesn’t bother me and I’ve got the 125 on the zoom holding down control scrolling up to get there.
00:45
Now we’re going to scroll down to the liabilities we’re concentrating in on the loan payable that we had just consolidated to the 72,000. Now the 72,000 we’re going to make payments on that loan now and we’re going to say that this is going to be structured kind of like a standard type of loan when we have a car loan or something like that, in which case, we’re going to pay it off in monthly payments. However, those monthly payments include both interest and principal within it. So that complicates how we’re going to record the loan into the system because when we record it, we can’t simply just write a check and decrease the loan account, there’s going to be three accounts that are affected, we’re going to have interest affected the loan account affected, and of course cash going down with every payment.
01:26
Not only that, but the amount that’s going to be allocated to the interest and the principal is going to be different every time we make a payment. Why? Because of course, when we when we’re trying to make the payments, we’re trying to make them standardized the same payment each month, but it really kind of shouldn’t be because the interest changes as the principal goes down. So in order for us to standardize the payment amount, we have to adjust the interest in principal portion of that standard payment amount. For that we need an amortization table. So for this presentation will actually be Make one in Excel. So this isn’t an Excel course. But I want to be able to just go through Excel and put together an amortization schedule for a few different reasons. One reason is that we need to understand the relationship between the interest and principal.
02:12
So we can come up with some kind of system to enter this into QuickBooks. And once we have that, we can come up with different systems to enter it into QuickBooks, we can enter it according to the amortization schedule each time. Or we can basically have an adjusting entry, we can work with a CPA firm, possibly an interest one way, and then ask the CPA firm to make the adjustment at the end of the time period to account for the interest or something like that. But in order to do any of those systems, we need to know what the what the interest in principle, what we’re talking about with regards to interest in principle and making an amortization schedule is a great way to learn that.
02:47
The other reason is that because oftentimes, when you get when you finance something like a car or something like that, they may give you all the information but not an actual amortization table. And so then you would basically have to create one or ask your CPA firm or something like that to make the amortization schedule in order to properly break out the interest in principle, so that information isn’t always, you know, you always don’t just have the amortization schedule, even though you have the information basically, to make one up, what they typically give you is the information. And then what your monthly payment is, they don’t really break out the monthly payment interest in principle, per payment, which was what we need to enter it into the system.
03:25
Therefore, we can take what they give us and basically make an amortization schedule that would look something like this, you could do it in Excel. So we’re going to say we have an amortization, we’re going to say that we have a loan for 72,000, we’re going to say that there’s monthly payments for 60 months, which means basically a five year loan, right? So if we have a 516 months, divided by 512 months, or 12 times five is the 16 months, so there’s going to be monthly payments 16 months over five years, so we’re going to be making monthly payments for five years or 16 months. And then we’re going to say the interest rate is going to be 5% And then we can have the payment amount.
04:02
Now they’re typically going to give you the payment amount. But let me just show you how you can basically calculate the payment amount. Because in a typical kind of loan, if you’re trying to figure out what your payments would be with different types of loan options, you can use excel formula for the payment amount. So this is a common formula to use. So let’s just take a look at it. If you one way you can get there is go to the formula tab over here. And we can go to the Insert Function, which will give you a format box kind of helps you out with the payment.
04:30
Then I’m going to type in what I’m looking for here. I’m going to say loan payments. That’s what I want to calculate loan payment. And then the second one here is the one we’re looking for the PMT, calculate the payment for a loan based on constant payments and constant interest rate. So I’m going to say okay, that’s the one I want, then it gives you a little formula box to help you to fill this thing out. So we can then say that the rate with the rates going to be it’s going to be that 5% now this is the trick Peace because that 5% whenever you hear interest rate, it means per year unless said otherwise, just just the convention that we typically have. And we know now that the rate is basically going to be per month.
05:12
So that is the yearly rate, which we’re going to divide by 12 to give us the monthly rate, then we’re going to say okay, then we’re going to have the number of payments, which is going to be 60. So I’m going to say 60 payments is going to be this box. And then we need the PV, which is the present value, the present value is the loan amount, the amount of a loan at the current time period, which is the 72,000. So that’s, that’s what we need. These two are not necessary for the calculation we’re doing here. This should be calculating our interest payments. So I’m going to go ahead and say okay, and there we have it, that’s going to be the payment amount. Now, I like to see it as a positive number.
05:50
So what I typically do is I just double click on the thing, and I put a negative in front of the P which basically just says Hey, take that number and flip it, make it a positive number please. So we’re going to put a negative in front of And there we have it. Now this is often the information you’ll have, you’ll give, they’ll give you this in a contract type format. And that’s great. You know what your monthly payments are. And I know what the interest rate is I can figure out the total interest rate and whatnot, but I can’t figure out, you know, easily what the interest is per payment, how much interest per payment of this amount? Do we have, we don’t know, because it’ll change each time given the format of the loan.
06:23
So that’s one thing I just want to show here. Also note that it could be possible that you have all the information you have the payment information, but for example, don’t know the interest rate, right? Or if you have any other kind of unknown, you can also use this same formula. There’s different types of formulas. So for example, if you wanted to learn or know what the interest rate was, based on you making payments of or someone making payments of 1003 59 for 60 months, and the loan being 72,000. And you’re saying well, then what’s the interest rate? They didn’t give me the interest rate.
06:57
They just told me what my payments are and the They told me that the monthly payments and the loan balance but they didn’t give me the rate. Well, we could figure that out, they still gave you all the information to be able to figure that out, right? So we could then say, What if I delete this? Now notice that this amount, I’m using the same formula down here, I’m using the same formula down here, even though this is an unknown right there.
07:24
So I’m going to say that’s the unknown. And instead of having a different formula, we could use a different formula to solve for the unknown, I’m going to keep this formula. And then I’m going to ask Excel to just hate Excel, put whatever you need to put there, in order to make the balance what I know it should be, which is 1359. That way, I can use the same formula, which is a more common formula to basically figure out the interest rate. And so I just want to show you this, this tool, because I think it’s a good it’s a nice tool to have and like to show it off anytime, you know, it could be useful in different situations.
07:57
So that tool that I wanted to just show you here, it’s Going to be in the Data tab up top, it’s going to be in the forecast. And it’s called the what if analysis, I’m going to go to the what if analysis. And we’re going to be using the Goal Seek tool Goal Seek. Now this is what we’re going to say is, is Excel, I would like you to set this cell, make that cell B, and then I’m going to hard code this number. And also note that I’m not actually on any cell, I can be anywhere in the sheet. Because it’s not missing. I’m not making a formula in this cell. I’m asking Excel to do a function for me by basically trial and error.
08:34
So then we’re going to say, bye, we want to change it to you, I’m going to hard code this number 1359. That’s what I want it to be. And I want you to get it there by changing by changing this cell. So think about that. Again, we’re going to say hey, Excel, please set this number right there to be 1359 by changing this number, and then I’m going to say okay, and again Sales just going to keep on trying whatever it can try to get to to get to the proper percentage and it gets to the 5%. So though you can use the same format, even if the even if the contract doesn’t give you the percentage and it gives you the payment, you can kind of back into the percentage you have all the information you need to back in the percentage.
09:17
Notice it’s not exact here because we have rounding involved. I’m going to put it back on just the 5% and and just realize that I have rounding on. So if I was to increase the decimals, it’s actually 1358 73 and so on. Alright, but I’m going to take away the pennies. And then we’re going to go down here. Now we’re going to make the schedule. So once I have that information, I can say okay, well how much how much interest in principled am I paying per section.
09:43
So for that, I’m going to have the headers up top, the payment number, this is the number of payments, which is simply going to be one, two, then I’m going to highlight those two and I’m going to go to the autofill scroll all the way down and there should be 60 of them. All right, I’m just gonna go down to 60. And they’re 60% Then we’re going to have the payment amount. And actually, sometimes I like to actually start at zero here, I’m going to start it at zero, and then one. And then we’ll bring it all the way down to 60 payments from there. So I’m going to 60 payments, all right, and we’re going to bring this down. Like so. Alright, let’s go back up top, then we’re going to say that the principal amount, and so the principal is going to be the amount that is still owed on the loan.
10:33
So at at times zero, it just equals to 72,000. Then we’re going to have the payments, the payments are always going to be the same, they’re going to be this 1359. Again, remember, it’s rounded. So there’s some pennies that are so there’s some rounding, and then we’re going to calculate the interest. So the interest then is going to be calculated as I scroll back up top. It’s going to be the 72,000 times point O five That would be for a year, however, interest for a year and we only want a month. So divided by 12. So there’s the 300. You can also think of it as as the calculation we had. point O five is the interest for a year divided by 12. That very small number would be the interest for a month.
11:20
If you move the decimal over b point 416 6%. That’s why we don’t usually use monthly interest because the interest rates would be very small. That’s why we typically default to a year. And then we’re going to say that times 72,000, and that would be the 300. So let’s calculate that with a formula here. I’m going to say that equals this 72,000 times the 5%. And then I need to double I’m going to double click on that and take that and divide it by 12. Now, you could put brackets you don’t need brackets, because it’s all you know, order of operations, it’ll multiply and then divide.
11:58
So we’re going to say okay, Then that means the principal is going to reduce by the amount of the payment minus the interest. So remember the interest is the rent is the rent, like if you’re just renting an apartment, you got to pay the rent on it, we’ve rented money, that’s what the interest is, we’re paying the rent on it interest $300. And then we’re paying down the rest of it pays down the actual principle that we borrowed, we’re giving back part of the apartment, we’re giving back part of the money the value back, and that’s going to be the 1059. So that means that we have now the new principal 72,000 minus the reduction in principal, or 70,009 41, this minus this now, so no, if I keep doing this, there’s going to be a change in the amount of principal and interest reduction because we want the payment to remain the same, so that we can budget our cash flow.
12:50
That’s kind of why we forced the payment to be the same. But by doing that, we have to work the very the interest in principle why because now I’m not renting as big of an apartment, which is The analogy I’m not renting as much money, therefore the amount of interest is less for month two than month one. So if I was to calculate it, it would be now this number times the 5% divided by 12, which is a little bit less, right. And then I would say, Okay, now the difference is the payment minus the interest, which is now a little bit more of principle, because it’s the same payment, less interest more principal, then the principal is going to go down by this number minus this number. Let’s do it one more time. And then we’ll autofill all the way down will practice the autofill.
13:34
So it’s going to be the same, I’m just going to say equals the same number as above interest is going to be equal to this now the 69 878, which is now the principle times the 5%, which is up top divided by 12. Because we’re doing this monthly, not yearly, a little bit smaller of principle, and now we’re going to say the total payment is that minus the principal now that the principal action is now higher, the amount of the principal will be reduced by is higher than prior month, the prior principal minus the principal reduction brings the principal down to here.
14:11
Now, I’m going to try to autofill this all the way down and see if, if it does what we want. I’m first going to highlight these cells, I’m going to do this with one cell, and then I’ll see if it did what we want, then we’ll go back to it. So I’m just gonna autofill down and say, does that do what we want, I don’t like this, something happened there. This one did what we want. This one looks out of whack. Why? Because it brought the 5% down. I don’t want it to bring it down. I wanted to stay at that 5% I tried to bring it down. So I’m going to use an absolute reference there. And then and then it should be okay. So I’m going to delete this. And then I’m going to go back to the interest portion, double click on it and see this one in X for that x four, I want to make an absolute reference. To do that.
14:53
I’m going to push s f four on the keyboard or you can simply put $1 sign before the X and $1 sign before the form, you really only need $1 sign before I believe the four to make a mixed reference, but an absolute reference would be fine. And that basically tells Excel Hey, don’t move that. So when I copy it down, don’t move that. So because the convention would be to move every cell down in relation to the copied so so I’m going to say don’t move that one. Keep that one where it is. So now I’m going to highlight these four cells again, use the autofill to fill it down. Once again. This one looks good. This one now looks like it’s doing what we would expect what we want. This one looks good. This one looks good.
15:38
Now I’m going to go ahead and autofill all the way down. And once we get to the bottom, the principle number should be at zero. That’s kind of like our check figure that we did this correctly. So then I’m going to put my cursor right here, going to autofill all the way down. So auto drags it down. Dr. Phil’s gonna do the calculations and then let go. And there it is. So now we’re at zero at the bottom. Look at this last payment, notice how big of a difference you have same payment amount, but very little interest involved on it. And it’s all principal. So that last payment you make on the loan is basically all principal as compared to the first payment you make on the loan, which is basically has a lot more interest involved in it right.
16:18
So that’s going to be our transaction. Now, when we start to make the payments, then this first payment is going to involve the same amount decrease in the checking account, but then it’s going to involve the 300 of expense and the principal is going to go down by the 1059, then the second amount is going to be same amount of cash going out of the checking account, but interest is now going to be less and the principal reduction more. Therefore this is why we need the amortization schedule to record the payments properly.
16:46
Now note you can also do this periodically, you could come up with a system and just say hey, I’m just going to record everything to the loan, and then have my CPA. Adjust the interest portion periodically monthly or yearly. Or you could say I want you to accounting department just to record it to the loan. And then we we in the adjusting departments are at the end of each month will record the interest in accordance with the amortization table. That way you can make the data entry very easy, very repetitive, and and sort of just copy the last transaction and then you can simply adjust it from time to time. So that makes the data input a little bit faster.