 QuickBooks Desktop 2023. Make amortization table to support the data input related to loan payments. Let's do it within 2-its, QuickBooks Desktop 2023. 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. Here we are in QuickBooks Desktop. Get great guitars practice file. We started up in a prior presentation going through the setup process we do every time. Maximize in the home page to the gray area in the view drop down. We got the hide icon bar open windows list checked off open windows open on the left hand side. Reports drop down company and financial P and L profit and loss range change 010123 to 123123. Let's customize it with the fontine and numbering changing it to let's say 14. Okay, yes. And okay, let's open up the balance sheet as well in the reports going on down to the company and financial this time. The balance sheet standard. Let's customize it up top with the range and changing 010123 to 123123 fonts and the numbers to change to 14. Okay, yes. And okay, that's the setup process we do every time. We're now going to be thinking about some of the logistical issues related to making loan payments. So let's scroll down to the loan payable account we have down here. Note the loan payable account represents us owing money to a third party such as a bank it will typically go up as it did in this practice problem when we take out a loan often happening when we want to get capital get cash in order to start the business or expand the business so that we can purchase things like property plant and equipment and inventory that are going to be necessary to generate revenue in the future. So there's a couple of things we have to keep into consideration with loans that are kind of problematic. One is the fact that for external reporting, we really only want one or two loan accounts because we want a short term loan and a long term loan possibly. But in practice, depending on the industry we are in, we might have multiple loans outstanding and we would like to tie each of those loan balances out to the amortization table. So if we had multiple loans and we might do this more in future presentations, we might want to have a parent loan account and then the multiple loans listed under it indicating which loan we're talking about possibly with the last four digits of the loan number to identify them. So that's one issue that we have to deal with. Another issue is that most of the loans we deal with are installment loans, therefore we pay them back in monthly installments for example. And that means that there's a short term portion to them, the amount that's going to be paid back in 12 months and a long term portion for them, the amount that's going to have to be paid after the 12 month time frame. So now we've got the concept that we need to break out short term and long term components of each of the loans. Now that's an issue because if one loan has a short term and long term portion, every time we make a payment on it, there's going to be an adjustment of the short term and long term account. So in practice, when we're doing the data input, it's easier to have one loan payable account as we do here, even though there is a long term component to it so that we can do the data input as easy as possible and then break out the short term and long term portions periodically at the end of the month and or end of the year for reporting purposes and financial management type of purposes. So that's what we'll suggest there. We'll talk more about that when we get to the adjusting entries. Then we also have a problem in that when we make the loan payments, there's not just going to be two accounts that are impacted as we see with most transactions, there's going to be three accounts if we're trying to tie out to the amortization table. Because when we pay off the loan, we're going to decrease the cash, and then we're going to decrease the loan payable, but only for a portion of the payment because some of the payment goes to interest, which is the rent on the payment of the loan, which is on the profit and loss. So that becomes an issue. It also becomes an issue because the allocation of interest and principal will differ with each loan payment. So even if I get the loan payment correct, I can't really memorize the transaction so I can enter the check easily in the following months. And if you're doing bank feeds, that's another area where we like to memorize the transactions. So we have to deal with the fact that we've got this interest and principal that will be broken out. And how do we deal with that? There's a couple of different ways we could deal with that. One, we make the amortization table. So we'll see that problem here as we do that. And then every payment, we have to adjust the principal and interest so that we can tie out to the amortization table. Or two, we can say that I'm just going to make the payments go through the bank feeds as easily as possible, possible, and then just put the other side to the loan payable, not accounting for the interest, and then adjust the loan payable account periodically at the end of the month or year. In that way, you can make the data input automatic, easy, possibly using bank feeds, and then do a periodic adjustment to account for the interest. Okay, so I know that's a lot to look, consider right now. What we want to do is construct an amortization table, which will hopefully hone down and get a better understanding of some of these issues, and then we'll do some data input based on the amortization table in future presentation. So I'm going to open up an Excel worksheet, and I'm going to build an amortization schedule. Now, this is not an Excel course, so you don't have to build this in Excel. You can also do this in Google Sheets, if you so choose. But I think the actual construction of it will give a better understanding of what's going on with a typical kind of installment loan. You might get the amortization table when you take out a loan from the lender, they might give you that, or you might ask your CPA firm to do something like this to help you to construct an amortization table. You can also use tools online to construct the amortization table, but I think Excel is the best thing to do. But so I'm going to open up a new Excel sheet, I'm going to hold control and scroll in so we can zoom in on it. So I'm at like 265% at this point. And I've also brought my screen back down to 100% rather than 150, which is what I'm working on in QuickBooks. Then I'm going to format the entire sheet first. I do that by selecting the triangle up top, right clicking on it. I'm going to format all the cells to my underlying baseline formatting, which I usually go to currency, bracketed and read for negative numbers, no dollar sign. And then do I need decimals? I'll keep the decimals there. And we'll say, okay. So then I'm going to say the loan, I'm going to put my data on the left hand side and then construct my table from the data. This is a good way to set up your Excel sheets when you're doing budgeting or something like that. So you can change the data and run multiple scenarios. I'm going to say a loan amount. I'm also going to make the entire thing selecting the triangle bold font group and bold, so that we could see it a little bit more boldly. So I'm going to say the loan is for 72,000. The years I'm going to say of the loan, we're going to imagine the loan is outstanding for five years, we got a five year loan 72,000, it's an installment loan, we're going to be paying back in equal monthly payments. So the number of periods, the number of months then is going to be equal to five times 12, five times 12 or 60 months. The rate on the loan, the interest rate that is, I'm going to say is 0.05 or 5%. I'm going to change that to a percent format by going to the home tab, numbers, percentifying it, and then I'm going to say the payment. Now I'm going to recalculate the payment. The payment is probably most likely something that will be given to you on the loan. That's going to be the thing that the loan officer is going to be, you know, focused on. They might not even focus on the rate. So if you don't have the rate, but they give you the payment, you can back into the rate, which is a good thing to do so you can compare it to other loans. But I'm going to show you how to calculate the loan payment, which can kind of give you a double check that everything is working. If I calculate the loan payment, then I know my amortization table should work. So based on this information, this is how you're going to calculate the loan payment. I'm going to say negative instead of equals because that'll flip the sign to a positive number. Otherwise, you would end up with a negative number as the result. I'm going to say PMT. That's the payment formula. You can double click on it down here. You get your little box, which will help you to put the data. You look for the rate first. I'm going to pick up that rate. That's a yearly rate, however, and we're going to be paying this back monthly. So I need the monthly rate. This is where the little trick comes in. You got to divide that by 12. That would give you the monthly rate. Then comma, we got the number of periods, which is going to be 60. That's in months, not years. I'm not going to say five years. We're going to make 60 monthly payments comma. And then the present value is the current loan balance, which is the 72,000. That's all we need to populate this. So I'm going to say enter. And there's the 135873. So this is the amount that we're going to pay for 60 payments in order to take out to close out this loan. Now I'm going to actually make an amortization table with this because the payment is not enough for me to enter the data into QuickBooks, because I need to know the interest and principal per payment in order to get the proper loan balance. So what I'm going to do is I'm going to make a skinny E putting my cursor between E and F and skinnerizing it, making it skinny. That is, that's not a word I know. I think it should be though, skinner, I skinnerized it, skinnerizing the call. So I'm going to say headers payment or months payment interest. Let's do let's do one more column here. I'm going to say this is years, and then months, and then interest. And then I'm going to I'm going to instead of saying that I could go like loan that or loan reduction. And then I could wrap this column because that's getting long so I could wrap it. But I don't really like doing that because it messes everything else up. So since I'm not making a table out of this, I'm just going to say do it this way loan and then put the reduction on the bottom. And then I'm going to say loan balance. And then I'm going to bring these down. So and this needs to be a capital. That should be a capital. And then I'm going to I'm going to cut these control X cut and I'm going to paste them right there so they're on the bottom. And then I'm going to format it so it looks like I don't have these two cells by making it color by coloring it home tab font group. Let's make this like black and the and the letters white. That's what I often do for the headers. Let's center it so then it looks good. Alright, so I'm first going to look at the months then. So I'm going to make these a little skinnier. So I'm going to go from period zero, and then one two. And then I'm going to bring this all the way down by selecting it. I'm going to bring it down. How many months are there? There's going to be 60 months. So I'm going to select those. I'm going to put my cursor on the fill handle, left click on it and drag it down and you can see it's giving me the numbers on the right hand side. Just going to go down to 60. Notice that even if you had like a home loan, and there was 360 payments, you can do this pretty easily, pretty quickly. I'm going to say okay, so there we have that I probably don't need the pennies here. So I'm going to take the pennies off and center it because we're talking months, not not dollars. Now you could do a like a nice little trick if you want to know the years involved because the first 12 months is one year. So I could say okay, let me let me try to I'm going to put my cursor here and try to do a little trick to round this up so I can see the years. So if I say this is going to be equal, I'm going to say round up. This is a little tricky little way to do this. I want to take this number divided divided by 12, that number divided by 12 in comma. And then I want to round it up to the next digit which is going to be I'm going to say negative one. So negative, it's negative. Oh one point one. There we go negative point one. So once again, round up that number divided by 12 and then I'm rounding it up to to the digit one. And so if I copy that down, and I take it down to to the 13th month, you can see it rounds to two here. So that way I can I get this nice little thing that gives me the years. So I could take this all the way down. You don't have to do that. But it's kind of nice to know which year we're talking about. So we're going to say let's take the pennies off of that. So there's that this is I'll just put a zero up top. And then it goes in. And you can see it goes take the pennies off that center it. It goes to five years of course, okay. So then I'm going to say the loan balance starts equals I'm going to pull the data by saying equals pull the data of the 72,000. And then we'll do our payment payments, which is always going to be I missed the payments. So I need another column for payments here. I missed the payment. So I'm going to insert a column between G and H, put in my cursor on H, select in the whole column, right click on it and insert. And that'll put a column between there and I need the payment payment. All right. And then I'm going to say this equals this number. Now that payment's going to be the same all the way down. So if I just hit enter, and then I like copy this down, it doesn't do it. So what I need is to make that absolute reference. That's one way you could do it. And I could do that by saying double click on it on it. F four dollar sign before the D and the five, you only need a mixed reference to make this work, which is one dollar sign, but an absolute one is easy. And then if I click on it, again, I could just double click on it and it goes all the way down. I could add the pennies back here. Let's add the pennies back. So we've got those. So there is that. And then the interest calculation is going to be the previous loan balance times 5%. But that's the yearly rate. So I got to divide it by 12. So it's going to be equal to the 72,000 times the 5% yearly rate. If I hit enter, that would give us the interest if it was for a year, but we're talking about one month. So I'm going to double click on that and take that whole thing and divide it by 12. I don't need brackets because order of operations, it'll multiply before dividing going to divide it by 12 just hard coding the 12. And then which means just typing it in there. There's the 300. The loan reduction then is going to be if I paid 135873 cents, but interest minus is 300. That means the loans only going to go down by 105873 of the 135873 we paid. So if I subtract this out, this equals the 72,000 minus the loan reduction, the loan balance after the first payment is now at 70,941.27. So if I was to enter this first payment into QuickBooks, I would have to enter in essence a check that would decrease the checking account by that amount. But the other side has two components to it. I would have to have interest expense, which is in essence the rent on me borrowing the money, kind of like the rent on me, you know, living in a or, you know, having an office building that we're renting. And then the loan balance is going to go down by this amount. So that's fine. It's a little bit more complex. But then it changes because now the loan balance has changed. So the next interest is going to be based on the new loan balance. So now the interest for month two is going to be the 70,941.27 times the 5% divided by 12. Hold on what happened there, the 70,000 times 5% divided by 12. So now the interest is only 294.59 because the balance went down last time, right? The payment's still the same, because we want to keep that the same, that's how we structured the loan. So the down part of keeping the payment is the same, is that the interest and the loan reduction change each payment. So now I've got 1,358.73, we're going to pay minus interest, which went down, which means more of it is going to reduce the loan balance. So now the new loan balance after the second payment is 70,941.27 minus the 1063.14. So there's our new loan balance. So now after the second payment that we make, I can't just copy what I did. If I was doing this in QuickBooks, I can't just copy what I did before, it's going to be the same payment amount decrease in the checking account. But the allocation between interest and loan balance is now different. That makes the memorization difficult. If I'm doing bank feeds, that means I can't really memorize the bank feed transaction as easily one, because there's two accounts affected and two, because those two accounts have different amounts applied to them. So again, there's multiple different ways we can kind of deal with that, we could just change it every time we make a payment with the amortization table and tie into the amortization table, or we can try to come up with some system that we can make things automated, and then do periodic adjustments with adjusting entries. Okay, I'm going to I'm going to try to do this again, I'm going to delete these and enter this first one in a way so I can copy it down. So if I double click on this one, anything that's not inside the table, I need to make absolute. In other words, anything that's in the data on the left, I typically need to make absolute. So for example, this D four, right there, I don't want it to move down when I copy it down. This number right here, I do want it to move down. And this is a hard coded number, I want it to stay the same, no problem. So that D four, I'm going to put my cursor in it, F four and the keyboard dollar sign before the D and the four that has nothing to do with dollars, it's code for Excel to say don't move this cell down. When I copy it down, you only need a mixed reference, but an absolute one works. I don't need to do that here because I'm inside the table, nothing's coming from our data set. And I want these two to copy down when I copy the cell down. I don't need to do anything here because these two are in the data in our table, they're not in the data set to the left. I want them to move down as I copy them down, I can then select these three items, and just double click on the fill handle button. And it should copy this whole thing down. And you can see the interest port, the payment stays the same. The interest portion goes down, which means of course, the loan balance of the loan, you know, reduction, the amount of the reduction in the loan goes up. And so at the end of the day, if we got this correct, then after five years, it should go down to zero. And that's our verification that we've done the data input properly. I'm going to put some brackets around this now, make some brackets, and put some brackets around that. And I'll put some brackets around this. Now again, I know this isn't an accounting course, you don't have to put this together. We'll see if we can provide you a link to this to do the data input, but we're going to use this to do the data input into QuickBooks. You might get this information from the from their financial institution, you might have your accountant build something like this so you can do the data input. But hopefully, you want to kind of understand, I mean, this looking at an amortization table can be overwhelming if you don't really understand what it's doing. So hopefully, that gives us an idea of what it's doing, and how we can then use it to do the data input into QuickBooks.