 QuickBooks Online 2023. Make amortization tables to support our loan balance and loan payments. Get ready to start moving on up with QuickBooks Online 2023. Here we are in our Get Great Guitars practice file. We started up in a prior presentation using the 30-day free trial. 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. We also have the free QuickBooks Online sample company open. If you want the two open at the same time, we suggest using the incognito window or another browser. You can open incognito if using Google Chrome by selecting the three dots in the browser, incognito window, search in the search engine for QuickBooks Online test drive to open the sample company. We're going to be using the sample company to look at the difference between the accounting view, the view Get Great Guitars file is in, and the business view, the view the sample company is in. If you'd like to toggle back and forth between the two views, you could use the cog up top and switch between the views on down below. So we're going to duplicate some tabs to put reports in, go into the tab up top, right click to duplicate it, and then we'll right click again and duplicate it again. Let's go back to the middle tab and open up the reports like we do every time. And we want to be opening up this time on the standard reports, the favorite balance sheet. If you were on the business view, by the way, then it would be under the business overview. And then the reports on the left there. Then let's tap to the right. We're going to open up the income statement reports on the left income statement PNL profit and loss closing up the hand boogie. We're now in month two. So you could use the same full range which goes from 010123 to 123123 and see this includes month one's data or possibly let's run it this way this time. Let's go from 010123 to 02282328. I think it's 23 run that. And so we have January and February. And then I can use my little drop down here and say that I want to see it on a month by month breakout. So that gives us a nice tool. I'm going to run it because we're going to be working on February. So this will show us January numbers and February numbers and then the total for January and February, which is quite nice tapping to the middle closing up the hamburger. Now for for the balance sheet, we could do the same thing, but we probably don't really need to. I'm just going to go from 010123 to 123123 because this is going to show the balance for the year to date in essence. And because we're not doing an accumulation report, we're just showing where we stand at a particular point in time. We're probably good with just the end result numbers. So now the first part of month number two, February, second month of data input. We're going to first start with some transactions that are a little bit more unusual than the day to day transactions. So if I hit the plus button up top, these items in here represent in essence the day to day transactions. And we talked last month mainly about transactions that we tried to focus more on a cashed based system. In other words, we didn't enter bills, for example, too much. We use the expense and check forms to make payments. Now we're going to do more focusing on a cruel transaction like bills. And we're going to start off thinking about some transactions with regards to our financing on the loans. How are we going to deal with the payments of the loans? So that's what we're going to talk about here. So down below we put some loans on the books. So here's we've got our loans. Now there's a couple issues with dealing with loans. One, there could be an issue between a short term portion of the loan and a long term portion of the loan. How are we going to break those two out? My suggestion is that we try to put them into one account. I put them as a current loan, even though there might be a long term portion to it and then break out the current and long term portion periodically with adjusting entries done either by us, the bookkeeper or business owner, or periodically at the end of the year or month by the accountant or CPA firm for taxes or financial statement reporting purposes. So that's one issue that we'll talk about. And then number two, we've got an issue of what if I have multiple loans? Should I put multiple loans in one account here for loan payable or multiple accounts of loan payables? For that, from a bookkeeping standpoint, I think it's easiest to have one account for each loan so that you can tie out the loan directly to its amortization table or you can have a CPA do that at the end of the year. So we'll talk about that a little bit more in future presentations. Right now we'll just be dealing with this one loan that's in one account. If we have multiple loans, that's when it becomes an issue. Some businesses have multiple loans because they finance things say like equipment, for example, for a construction company. And then number three, what happens when I make a loan payment? It's not as easy as many other transactions, which only really have two accounts affected oftentimes, such as when I pay the utility bill, I decrease the checking account and I put it to utilities expense. When I pay down a loan, I'm going to be decreasing the cash account. And then you would think the other side would just go to loan payable, but we also have the rent on the loan, which is called interest, interest expense. So now three accounts are impacted. That complicates things a bit, but it gets even more complicated because the breakout between the interest and the principle per payment will differ from payment to payment, even though the amount that we're paying in cash remains the same. That makes it difficult for us to automate the transaction to use the bank feeds to basically try to make the transaction automatic. So how can we deal with that? There's a couple of different ways we can deal with that. We could try one way is we could try to stay in a cash based system and automate our whole process by saying I'm on the bookkeeping side, I'm going to do things in a cash based system. I'm not going to recognize interest expense, but just record it to the loan payable and then use your accountant or yourself at the end of the month and the year and do adjusting entries to the amortization table. That makes that allows you to automate everything and then just make period adjustments possibly at the end of the year when you do your tax returns or we can get the amortization table and every time we enter the transaction, we have to make the adjustment between interest and principle in accordance with the amortization table. To do that, we're going to need an amortization table. Now when we get a loan, sometimes they'll give us the amortization table, but they might just give us the terms of the loans and not give us the amortization table. The main thing that the loan negotiator will be focusing on is the payment that you'll be making and you got to be careful because the other important thing is the interest, how much you're actually paying, how much you're financing over the over the period of time. So it's best to see that by making an amortization table. So how can you make an amortization table? There are some tools online. If you just search a Google search for an amortization table, you can use the data to build one. I like just doing it in Excel and possibly using those online tools to double check the amortization table that we're going to be putting together. So that's what we'll do now and I think it's a useful tool just to build an amortization table because then you can see what I'm talking about with some of these problems between interest and principle, short-term and long-term. It'll be more clear if you're familiar with an amortization table. When you just look at one, it's overwhelming because there's just too many numbers. So if you build one, then it's actually not too difficult and then you kind of understand it better. So I'm going to build one even though it's not an Excel course and Excel so we can see how it works. So I'll do this fairly quickly because I know it's not an Excel course but this is what we'll use then to record our payments. Okay so I'm just going to make up a loan terms here so I'm scrolling in a bit. First thing I do is I sort the whole page to be the format that I want. The way I'm going to do that I'm going to select the triangle, right-click, format the whole thing to I like to choose currency, bracketed numbers and then we'll say I'll keep the dollar sign. Let's take that off and we'll keep the decimals this time and let's say okay I'll also make it bold so it'll stand out hopefully in the video format fairly well. Alright so then we've got the loan. I'm going to say the loan amount let's say is 72,000. So that's the amount of the loan. I'm going to make up the amortization table as of basically this point in time going forward and currently we have the liability on the books at that 72,000. So I know we actually had a couple transactions to make that 72,000 but we're imagining at this time 72,000 and we're going to start the loan amortization table from there. Years, let's say the years are five years on the loan. Now if they give the years oftentimes it's going to be broken out into monthly payments if it's a standard installment note. So if it's five years 12 months in a year we're going to say this equals I'm going to go up one five times I'm just going to hard code 12 that's a two 12 60 months 60 months five years okay and then the rate now usually this is where it gets a little tricky the rate is represented in terms of yearly rate meaning it's going to be 0.05 or 5 percent I'm going to make that a percent by going to the home tab numbers percent and that means five percent a year usually unless it's specified otherwise but when we do the amortization table we're really using kind of like the monthly rate for each payment but we don't usually say the monthly rate because the monthly rate is going to be quite low so the yearly rate is the convention that we'll usually use to say a rate but if you hear a rate that's not in the range of like a whole number right if it's like 0.0325 then they broke it down into something other than a yearly rate so for example if I this is the rate this I'm going to say year and this is going to be the rate per month per period that we're using which would be equal to equal to five percent divided by 12 and if I make that a percent and I add a couple decimals so now they could say well the rate is 0.417 yeah but that's the monthly rate we don't usually think in monthly rates we think in like the yearly rate right so so just that's something that's a little bit tricky when you think about these loans and then the payment that we're going to make the whole goal of the loan is to make the payment be the same and the way we have to do that is to adjust the interest and principal portions of the payment typically now there's a payment function here that we can we can use to calculate this so I'm just going to do this fairly quickly I'm going to say a negative instead of equals that's what I usually do to flip the sign otherwise you'll get a negative number and then I'm just going to type in PMT payment this is our payment function and then I'm just going to enter in according to this little thing down here the payment function so it's going to be the rate I usually choose the yearly rate but I could take the monthly rate I usually don't put the monthly register have the yearly rate and then I'm going to take that and divide it by 12 meaning if that's the yearly rate I've got to break it down into its monthly rate component because I'm making payments on a monthly basis not a yearly basis or I could just put the monthly rate which is this one here comma and then the number of periods is going to be not five years but 60 I could have done the same thing I could take the five times 12 or just take the 60 that I broke out in months here comma and then the present value is the current amount of the loan which is 72,000 that's all we really need so I'm going to close up the brackets and there's the payment so there's our payment amount if you break out your loan like this then you can then you can start to experiment on your own and put in up here well what if I had 60,000 or something and you can adjust your payment what if the rate was four percent and you can start playing with this now you can also play with this inversely you can figure out what the loan balance is using a present value calculation or what the rate would be giving everything else being constant now let's just take a quick look at some tools online to do that so if I went online so I typed in like amortization calculator here and then if I search into some of these most of them are for like home loans but but you can have a loan amortization and let's say we had 72,000 and this one and the rate was five percent number of payments we're going to say was 60 and we're going to say they're monthly and so there we're going to go ahead and calculate and so if we calculate that then we get the information five percent 60 the total payments add up to this amount for all the payments and then you've got your payment which is the one three five eight seven three so that's what we have here one three five eight seven three one three five eight so I kind of like to double check it with those amortization tables and then of course if you do want to calculate total payments so what are you going to totally pay over the life of this thing it's going to be equal to your payments times the 60 payments that we're going to make and then if you want to think total interest interest that you're going to make over and above the loan amount it's going to be this amount of total payments minus the loan so we're going to have interest of nine thousand five twenty three over the life of this thing if you talk to a finance person they're going to focus just on this number you're a little bit concerned at with this number this number how long your loan is going to be as well our factors that you want to take the time to move away from the loan advisor and think on yourself critically before you go into anything I'm going to make this one a little bit smaller and then this is going to be our headers months payments let's actually do I'm actually I can actually do years months and let's say payments interest and I'm going to call it loan reduction now notice little trick and excel reduction or a little thing to keep aware of that's too long really so I could either make this sell quite wide if I have a long header or I could wrap it home tab alignment and wrap but that makes everything else kind of long and it doesn't show me where I want to make the break like it guess is pretty good I but in any case the way I like to do it here if I'm not making this into a table is to delete this part and just put reduction in its own cell and then I'm going to pull all these down I'm going to cut them and paste them right there so that then I can make it look like a header by selecting these and make it formatted as a header home tab font group I'm going to make this black and white and then I'm going to center it so now that looks like a header even though it's on you know two two lines there and that that doesn't mess up anything to the right or left of the table we're working on okay so now we'll just do our calculations I'm going to put the months in first I'm going to go from oh zero month one two and then I'm going to select those set those three cells put my cursor on this fill handle and drag down to 60 periods so you can see the counter so it's quite easy to do because it gives me the counter there's down to 60 periods right there I'm also going to get rid of the decimals number group don't need the decimals I'm going to take those off and I'm going to center it here now there's a little trick that you can kind of figure out the years on this side because it's useful to know the year that we're in as well so let's let's do it with this cell right here and try to say I want all of these to represent the fact that I'm in year one so everything that's in year one I want to I want to have shown as a one over here and that makes it so I can actually make tables out of this if I wanted to because I want to know which year I'm in as opposed to the month I'm in which could be a little tricky when we get down to a later part to the table so one way you can do that as you can say okay what I'm going to do is I'm going to say this is equal to the month and then I'm going to divide it by 12 well well let's do it this way I'm going to say round round up and then bracket so I'm going to round up to the whole number I'm going to take the month that I'm in and then divide it by 12 so that'll give me a fraction of a number but I'm going to round it up to the whole number so then comma the number that I need to round up to it's going to be negative 0.01 and that just that just tells the the system which unit where we want to round to which is going to be a whole number so I'm going to say brackets that gets a little bit tricky kind of got to just know that but I'm going to say okay and then if I copy that and I put my cursor on the fill handle just to test it if I take it down to like 13 payments there's there's number two right so it looks like it's working here because that's a new year so if I take this all the way down so there it is year five so it looks about right so there's the years that we're in I'm going to take off the decimals here I'm going to center it and then I'm going to put a zero up top as well okay so then the loan the loan I have the loan reduction and I need one more thing here which is the loan balance so I'm going to say let's I'm going to make this black white and center it okay so then the loan balance is going to start with equal I'm going to take this from my dataset this is common practice when you're building these tables because then they can change automatically as you change the tables and you can make your estimates on that if you want to kind of think about your loans let's make this one a little bit smaller I'm going to double click on it and this one I'm going to double click here okay so then the payments are always going to be the same they're going to be equal to this one three five eight seventy three but the interest in principle you could call this principle I always spell it wrong so I just call it loan reduction because there's like two principles and they look I no matter what I do I just can't get it in my head what the difference is so I just say loan reduction so people don't get don't call me stupid so now let's go let's say this is going to be equal to the seventy two thousand dollars and then times and then we could take the rate which is the five percent but that's a yearly rate so I have to divide it by 12 or I could have just taken the monthly rate that I calculated here so that's going to be then interest that's the interest we're going to have to pay the loan reduction is going to be equal to the payment minus the interest the interest is like rent on your apartment or whatever right it's gone you know it's not going to reduce the loan it's just rent for the usage so this is going to be the amount that reduces the loan therefore we've got the loan balance minus the loan reduction now I could copy all this down but it's going to cause a problem so I want to think about how can I make it so I can copy this down I'm going to select this whole thing copy it down and then look for the problems what's the problem this moved down I don't want it to do that anything that's in my data set and not in my table I usually have to make absolute I have to tell Excel not to move it down because it's going to try to move down to relative references this one I did did what I wanted it to do here but it moved this down and I wanted to stay on five percent this one relative references looks good this one relative references looks good so what I'm going to do is delete this double click on the payment that when I don't want it to move down I'm going to make what's called an absolute reference I'm going to select f4 on the keyboard dollar sign before the b and the six you only need a mixed reference but an absolute reference works dollar signs don't have any meaning towards dollars it's just code for excel to say don't move the cell down so if I do that and then if I go to the interest double clicking on that that five percent outside of where I'm working that's what I need to deal with so I'm going to put my cursor in b4 f4 dollar sign before the b and for making it absolute this one I do want to move down so I'm going to leave that this one both of these I want them to move down relative so that one's good this one both of these I want to move down relative because I don't have anything my data set to the right that's the general rule okay so I'll select those four put my cursor on the fill handle and drag it all the way down if I get down to zero after the 60 months it's probably good boom so it looks like it did what I would expect so that looks good so then I can just put some formatting touches on this I'll just select this whole thing and we can format it say like I'm going to make this brackets something like that and then I could format these maybe put some brackets around that okay so the point is every time we make a payment the payment's the same but I'm going to have to break out the interest and the loan reduction in two separate amounts so for example if I went back over to QuickBooks here and by the way you could double check your amortization schedule with this amortization schedule or one that you generate online to kind of see if you're or you can just do it you could do it online but I think it's useful to do it in excel but then when you make a payment in here let's say we're going to make a payment it might come through the bank feeds that you make the payment and you still have to adjust it because of this issue with the interest and principal so I could go in and say okay I'm going to do an expense form and then I'm going to reduce the checking account by category but then you'd have to choose the category of interest interest paid and then the other category is going to be the loan payable and these two amounts for interest paid and loan payable will differ each month as we can see here that means I can't automate the payment even if I had the bank feeds on because it's it's going to get messed up even though the payment is the same I can't automate between words going to allocate on these two accounts how could I get around that well one I could manually go in there each time I make a payment and adjust it so I could do that that's one method that we could use another method is that we just make all the payments uh to the loan balance which will make it incorrect because we're not accounting for interest but then at the end of the year give the CPA or have the CPA create an amortization schedule or your tax professional and then adjust the loan balance and the interest in accordance with the amortization table right and that way you can make everything on a cash-based system and and work with your network your accountant to then break out the accrual components making the data input fast and and automatic and then making the periodic adjustments so that might be an efficient way to do it that's one way that you can do it now if I look at the end of the first year here the other issue is is this how do I break out the short-term and long-term portion of the loan which again I'm not going to break out right now I'm going to break it out periodically at the end of the year possibly if I need to so in other words right now this amount is at 72 000 but short-term loan payable is the amount that's going to be due within a year so you might think well that would just be I'm going to pay within a year this amount times 12 but that's not really the short-term portion of the loan because the short-term portion of the loan doesn't include interest the short-term portion of the loan is this the sum of the amount that you're going to pay for the loan through through the end of the year which is going to be that and so there's our there's the amount why don't I include the interest if I know I'm going to pay it as a liability that's short-term because we haven't incurred it just like we haven't incurred the rent yet we haven't incurred the interest on that so that gets a little bit a little bit tricky and then the long-term portion would of would of course be this minus this and or the amount after the 12 months that's where we'll be after 12 months now you can also make this into a nice if you want to see it on a year by year basis you can make a nice like little pivot table which usually you want to create zeros here to do that and I'll just show you how to do it real quick if I select these and I go down and say go down to five years and then insert a pivot table pivot table and then I'm going to put the pivot table in my worksheet so I'm going to put it right there so I'm not I'm going to put it in the existing worksheet and the location is going to be right there let me do that again hold on a second I'm going to choose this and then I'm going to say insert pivot table and then I want it in the existing worksheet and I'm going to put it right there and then add the pivot table now I'll do this fairly quick so I don't take forever on it but basically I'm just going to pull this stuff into the pivot table I want the years but not the months so I'm going to take everything but the months payments interest reduction balance and the balance so now I've got my data I'm going to say that the the years I want as the rows so I want to pull this one in the values to the to the rows and there's basically my pivot table on like a year by year basis so now you've got the sum of the payments for the year the sum of the interest the sum of the reduction and so there's a little bit more formatting we need to do here so now I'm going to try to format this I'm going to hit the drop down here and say I want to I want to value look at the value of the fields and this is the sum of the payments so that looks right the sum is correct I'm going to take the number format and I'm going to make it currency bracketed numbers no dollar sign and we could probably well let's keep it at two decimals we'll do that and say okay and then I'm going to do the same for this field some of the interest and say value field the sum is what we want so that's right on the interest but I'm going to number format it and say currency bracketed no dollar sign two decimals okay and then the the sum of the reduction so this is how much we paid off on the principle of the loan each year or we'll do and I can say the sum is correct number I want currency bracketed no dollar sign two decimals and this last one's a little bit different I'm going to say value formatting this one I want I want it to give me the minimum number because I want to see where we are at the end of the period the end of the year so that one's a little bit tricky and I'm going to say number format this is the same and so there you go and now this is something the reason I want to show this is this is something that you can't really do with these online kind of tools and it could be a useful tool to do something like this I'll just put some brackets in here because now you can see everything on a year by year breakout and see that the payments that you're going to make on a year by year and then you can see the interest that you would pay in year one versus year two versus year three noticing it goes down and then the principle that you would pay the reduction in the loan balance from year to year in other words if I was to take this minus this there's our difference these amounts five nine four five oh three three should be the amount at the end of each year there's the five nine at the end of year one year two four five three three four four five three three four so you can do a lot more with these customizations or possibly projecting on what loans you want to be doing if you can get the loan amortization table or make it in excel then then using some of those online tools because this this little breakout can be quite useful okay so that's the general idea so in future presentations we'll make some loan payments based on the amortization schedule and show you how the payments will look and how the balance then will tie into the amortization tables once we're done with it