QuickBooks Pro Plus desktop 2022 Make amortization table we’re going to be using Excel to help with that process. Get ready because we bookkeeping pros are moving up the hilltop with QuickBooks Pro Plus desktop 2022. Here we are in our get great guitars practice file going through the setup process with a view drop down the open windows list on the left hand side, then we’re going to go to the company drop down home page to the middle maximizing it in the gray area reports drop down company and financial we’re looking at that balance sheet stand ERD.
00:35
And then we’re going to customize that do a range change up top from a 1012 to 1231 to two fonts and numbers change in that font size to the number 14 And okay, yes, please. Okay, we’re just going to stop here on the balance sheet. Because we’re focused here on a number that is located within the balance sheet. That being the liability, we set up in a prior presentation of the loan payable, we now want to think about what’s going to happen when we pay off of the loan payable. So the payments become do we make payments, you would think that would be fairly straightforward in that if I went to the homepage, it would be a decrease to the checking account.
01:13
So you can decrease it with a check here. And you would think the other side would just go to loan payable paying down the loan or possibly even you can have it done automatically and possibly be done with bank feeds, decreasing the checking account the other side, you would think go to loan payable. But not that easy, there’s an extra step to it, what’s that extra step, you’re not just paying down the interest, you’re also paying down principal.
01:37
So now we got three accounts that are affected when only there are usually only two accounts that are impacted, if we’re going to be breaking out the interest and principal. Not only that, but most of the time, the difference, the payment will be remaining, the same decrease to the checking account is the same. But they will be a difference between the amount allocated to interest in principle, making it even more difficult to do something like an automatic payment with a bank feed, which which now you can’t just memorize the payment because there’s a breakout that’s different between the interest and the principal.
02:10
So there’s a couple ways that you could deal with that if I go back to the balance sheet. First of all, in order to break out the interest in principle, you need an amortization table to tell you for each payment, how much was broken out between the interest and principal, that would be the typical thing you would need to do to break that out, you might be able to get it from the lender, or sometimes they don’t give you that but you have the loan and you got the terms on the loan.
02:34
So either you could create the amortization table, which we’ll actually practice doing now. Or possibly you could give the information to your accountant, they can make you the amortization table. But even still, even if you have it, then you’re gonna have to break out the interest and principle using the amortization table.
02:51
So the next kind of method that you could use, you could say, hey, look, I’d still like to make a nice, easy transaction, maybe I want to make it with bank feeds, and I want it to be automatic, then maybe you could set it up, or you could set it up where you’re just gonna say I’m going to make a simple transaction, which is just going to go directly to the loan account, decreasing the loan account for every time I make a payment, even though that’s not exactly right, because I’m not breaking out the interest.
03:18
And then you make a periodic adjustment at the end of the month and or year, either yourself or your accountant, then taking the loan balance and then making the necessary adjustments to record the interest and then taking the loan balance back to where it should be on a periodic basis. That can be a really nice system if you’re if you want to kind of automate it, and do it like an adjusting entry type of process. So remember, with the loans, a couple things you got to keep in mind.
03:47
One is you might have multiple loans. So if you have multiple loans, you kind of I would still try to list them out here separately, possibly listing out the end number on the loan, so you can separate each loan and check your loan balances for each one, possibly having them as sub accounts of a loan payable account, so you can collapse them into one loan payable account.
04:08
Two, they often have a short term portion and a long term portion which when you present them, you’re going to want to break out between short and long term for presentation purposes. However, from a day to day purpose for data input, it’s not nice, it’s not good to have two accounts for each loan, because that makes it more difficult to track the loan balance which would be much easier if you had one account per loan. Therefore, I would suggest that you make one account per loan, possibly a current liability type of account.
04:39
And then periodically at the end of the year or month, you break out the short term and long term portion with an adjusting entry with the the amortization tables, and then a reversing entry, putting them back into one loan account so that you can then do your normal kind of process. And then you’ve got the question of Do you want to break out the interest in principle as you make each payment, which means you got to kind of individualize each payment to break out the proper amount of interest in principle, or do you want to do that periodically, either by yourself or by somebody else like a CPA,
05:16
and then ask them to do a periodic adjustments, making the data input easier on your end, but not exactly correct until the adjustment is made. At the end of the month or year, the adjustment just tying out the loan balance to what is on the amortization table, and then breaking out the difference to interest. that’s those are kind of like your options. Let’s we’re going to actually construct an amortization table from Excel now. So this is going to be an Excel kind, of course kind of thing here. So I apologize for that. But it’s nice to get just an example.
05:47
And even if you don’t work the actual Excel problem, you get an example of you know what it means to have this kind of interest broken out between the principal and interest and what your accountant might do, or what you’re looking for from the bank in a more detailed process. So let’s make one here, I’m going to do this fairly quickly. I know it’s not an Excel course. But let’s give it a shot. Here’s my Excel worksheet, I save this Excel worksheet, just called it a loan payable, I only need one tab. So I’m going to delete these other tabs, I’m just going to say this is one tab sheet,
06:16
I’m going to format the entire sheet by putting my cursor on this little arrow up top, right clicking in the selected area, I’m going to format that sheet to the format that I like to see it in. So I’m going to go to the number I’m sorry, the currency format, negative numbers, bracketed, no dollar signs, and I don’t need I’m going to, we’ll keep the pennies, let’s keep the pennies we’ll keep the pennies on. Okay, I’m going to make it a little bit larger down here, increasing the size of this thing.
06:46
Let’s make bring it all the way up to 170. Making a huge, and then I’m going to say that I’m going to put my data up top, so we’re going to say it’s a loan. And then I’m going to give the monthly the months, the months, we’ll say years, and then months, and then rate we’re going to need and then payment. So this is typically loan information, I’m going to select the whole thing and make it all bold to make the whole thing bold, I like to be bold. So then I’m gonna imagine the loans on the books for the 72,000, just to start it from there for our practice problems,
07:21
although I know that we had to kind of transactions making that, that 72. So so bear with me on that, we’re just gonna pretend we’re starting at 72. To start it from there. If you’re starting in the middle of the loans, you can make the same amortization table from the start, what you would type, what you would typically get from the from the loan from the lender, you’d know what the loan amount was 72,000, even if you’re starting in the middle, you could start you can like do your amortization table from the beginning or from that point going forward.
07:51
And then we’ve got the number of payments, which we’re going to let’s say it was a five year loan, we’re going to imagine, before it’s over a year, you’re gonna have some payments that are due in the current year, and some that aren’t long term, short term and long term, let’s say the the, that means that how many months are we talking about? If we paid each month, five times 12, would give us 16 months, the rate is going to be let’s say just 5% point oh five, I’m going to make that 8% By going to the Home tab number group, and percent define it.
08:22
And then down here, we can calculate the payment. Now oftentimes, you might any of these items that they do not give you. So possibly they don’t give you the rate, which is kind of sneaky if they don’t, but they give you the payment amount you can back into any other item here. And you could do this with a variant of the payment, I’m going to just calculate the payment just to show you how to calculate something on it, I’m going to say negative PMT, the rate, the rate is going to be that 5%, that’s for a year, we want a monthly rate.
08:52
So I’m going to divide it by 12, comma number of periods is going to be this 60 months, comma, and then the present value is the loan amount of the 72,000 and enter. So the payment then is going to be the 1003 5873. This is often the information you have, but you don’t have the amortization table. This tells you how much the bank accounts going to be decreased by but it doesn’t tell you the breakout per period of interest in principle. To do that, let’s make a quick amortization table just for an example purpose here, I’m going to make this cell a little smaller.
09:27
I’m going to say this is the the let’s let’s actually do the the months we’ll do a little trick to get the months and then the payment and then the end tourists. And then I’m going to say loan. Let’s call this reduction, reduction. And then this is going to be the I’m going to call it loan balance loan balance. And then I’m going to move these down. I’m going to cut and paste them right there. And then I do my little header stylized stuff here, going, this is how I stylized the header Font group, making this black and white. And then we’ll center this one here. So it looks like that.
10:11
And then we can pick up our months. So we might say, then we just got the periods are going to be 012. And then I’m just going to copy that down to 60, putting our cursor on this item and copying it down to 60, copy it down to 60. With our with our auto fail, you could see it populating as we go. And so even a long table is pretty doable, given that autofill. And we’re doing Excel here. So that’s what that’s what excels for, I’m going to remove the decimals, I will put the year there’s a little you know, I’ll leave it there.
10:46
So then we’re going to say the payment is going to be the same, but I’m going to start the loan balance at period zero, which is equal. And I’m always going to pull the data from my data set equal to that 72,000. And then we’ll figure out the payments, which are going to be the same each time, so I can just copy the payments down. Now I’m going to do the calculation a couple times, and then we’ll copy it down, which will require an absolute reference. So So those will give you an idea on how to calculate it.
11:14
So we would be taking this the interest amount, which is in essence, the rent on the loan that you’re taking out. So we took out the 72,000 loan, it’s just like if we were living in a place or we’ve rented the office building, we’re using that. And now we got to pay rent on it. In this case, we’re using the purchasing power of this money that we don’t own, and we have to pay rent on it. So we’re going to say alright, the rent is interest, that’s going to be the 72,000, we’re going to be paying then the 5%. But that would be 5% per year, remembering that every time you see an interest rate, it usually means per year, because if they were to put it in the period that we’re actually paying in per month, it’ll typically be a very small number, meaning it would be 5% divided by 12.
11:57
So we typically represent percents, you know the rates in years unless said otherwise, which means that we then have to divide it by 12 to get the monthly rate. And that’ll be 300. So that means we’re going to be paying 300 of interest, that’s basically the rent that just goes away, that’s just an expense, it’s not going to pay down the principal. And then that means if we pay 1003 5873 Minus the 300, the principal is going to be reduced or the loan balance will be reduced by the 1058 73. So we could say 72,000 minus two 1058 73.
12:34
If you were to record this in QuickBooks, then cash would be going down by 1003 5873, which we could do with a check. But then we’d have to break out the difference the other side 300 going to interest the loan reduction being decreased by the 1058 73. That’s great. But what happens next time? Is it the same breakout? Could I just memorize that transaction? No, no, of course not. Because that would be too easy, we have to do something stupid, it would be the 1003 58. And now what happens to the interest payments, the same reduction to the checking account.
13:09
But now we’ve got this new loan balance of 70,009 4127 times the rate of 5% divided by 12. And now it’s that the interest portion is at the 295 59. So now I’m going to take the 1003 5873 minus to 295 59. And now the diff the differences is the 1063 14 loan balance going down 70,009 41.27 Minus the 1,063.142 69 878. So now if I wrote a check, I’d have the same check decrease in the checking account, but interest would be going to 290 5.59 and the loan balance at the 1030 63. So you see the problem there with the data input.
13:59
That causes us a problem because now I need this amortization table to break this out, let’s do it one or two more times, the payment would be the same interest is the new loan balance times the rate divided by 12. The loan reduction would be the payment minus the interest. The new loan balance is the prior balance minus the loan reduction one more time, the payment is the same tab. Interest is the new loan balance times the 5% rate divided by 12. The loan reduction is the loan payment or the payment amount minus the interest. The loan balance is the prior balance minus the loan reduction. Could I copy this down? Let’s give it a shot. I’m going to copy it down one time and see if it works, which it won’t and then we’ll see why.
14:49
And then we’ll fix it and then we’ll do it again. So I’m going to copy this down with the autofill and it didn’t work. So if I double click here what happened? It moved this Sell down, I don’t like that, that 5% move down, anything that’s outside in our data fields is going to need an absolute reference as the general rule. So I gotta say, All right, I’m just going to undo that undo this one right here, where it points to that thing in the data set, the 5%, that’s in cell D four, I need an absolute reference, putting my cursor NB for f4 on the keyboard, putting $1 sign before the B or and for, you only need a mixed reference, but an absolute one works.
15:30
So I will use it. And then we go, we’re good. Now I’m going to copy it all the way down, selecting these items, putting our cursor on the field handle left clicking, making sure you got a solid grip on that fill handle, you might want to put some chalk on that left finger to make sure that you got a solid grip on that handle, and then drag it down to the 60 payments. So you could double click on it, but I kind of like dragging it down because I want to be down here anyways.
15:57
And we’re going to go down to the 60 payments, and it should be zero at the end. And that indicates that we have done this properly at least looks, that’s one indication that we’ve done it properly. So you can see what happens between the interest and the principal, as the loan balance goes down, the rent on the thing we’re borrowing, the money goes down to the payment remains the same, because that’s how we set it up because we want the payment to be the same because that makes it easier for us to budget resulting in this difference in the interest.
16:27
And then the amount of the decrease in the loan is going up until the very end here where you’re making the same kind of payment. But the interest on it is way lower. And it’s almost all loan balance reduction or principal that is being paid until this thing is paid off. So now, when we make our payments, we’re going to we can we can use this schedule to say okay, the first payment, I’m gonna have to do this second payment, I’ll have to do that and so on.
16:55
So we can like I said, we can get this amortization schedule from the bank if they give it to us. If not, we can make it ourselves or we can basically ask our accountant to do it. And then our question is, do I want to do this? Or do do I do I want to then enter my data into QuickBooks in a more simplified way and adjust to the amortization table periodically. Those are kind of my options at that point in time but you want to keep your options in mind will then going to record a couple transactions from the amortization table next time.