 QuickBooks Online 2022. Make amortization table with the help and use of Excel. Get ready because it's go time with QuickBooks Online 2022. Here we are in our Geek Ray Guitars practice file. We set up with a 30-day free trial holding down control, scrolling up just a bit to get to that 1-2-5% currently in the home page, otherwise known as the Get Things Done page. And the business view as opposed to the accounting view. If you wanted to change to the accounting view, it is something you can do by going to the cog up top and switching down to the accounting view. We will be toggling back and forth between the two views, either here or by jumping over to the sample company file, which is in the accounting view. Back on over, we're now going to open up a few tabs up top by going to the tab up top, right-clicking, duplicating the tab. Go back to the tab to the left, right-clicking on it again and duplicating the tab again, noting that the reports, if we were in the accounting view by jumping back on over to the sample company file as those tabs are thinking, would be in the reports area. When we jump back over to the business view, it's going to be in the business overview area. And we want to then go into the reports section. And we want to then be opening up the, looking at the balance sheet report to start out with. My computer's a little slow. Give it some time. It's thinking. Oh, there it is. I'm in the standard. I got to go to the standard tab over here. Close up the hamburger and open up the balance sheet. So going into the balance sheet, let's do the ranging of the changing from 010122 to 123122 and run it. And then we're going to go to the tab to the right. And we're going to go then to the business overview and I got to switch then to the reports. The standard reports, profit and loss, otherwise known as the income statement, closing the hamburger, doing the ranging, changing 010122 to 123122 and run it. So now we're going to be looking at the second month, doing data input into the second month, noting that as we do so, you can run the profit laws basically to the year to date so that we have the first month in place or we can concentrate just on month number two, which means we can change the date range to, let's say 020122. And of course there would be no data in there at that point in time so we can run the year to date or just the current month. If I go back to the prior tab to the balance sheet, that's not the case because it's a point in time. So there has been no data entered after January. If I was to change the date up top or the beginning date to 020122 and run it, I've got no change to the actual balance sheet in terms of what's presenting here on the balance sheet because it's as of a point in time. However, that date range change does change the fact that if I drill down on something into the sub ledger by clicking on it, I don't have the data in here so that's why the range on the balance sheet is good, not because it changes the balance sheet report or at least a standard balance sheet report up front because it only represents the end date but because you get that range change that you can drill down on, I'm going to change it back to 010122 and then run it. So we're now going to be looking at the loan payment. So we're going to start off imagining that we're going to be paying off one of the loans that we have on the books. So I'm going to scroll down and say we've got this loan on the books down here of the $72,000. Now when you've got the loan on the books, remember there's a couple things that you want to keep in mind. One, you might have multiple loans. If you have multiple loans, you can put them into one loan account but I would recommend from an internal standpoint it would be easier to make the multiple loans, breaking them out by the loan, people that are giving you the loan, the financial institution, possibly putting the last four digits of the loan number to distinguish each of them so that you can then tie each of those loans out to the amortization table, possibly using the loans payable account as a parent account and the other loan accounts as sub accounts within them. And the other thing you want to keep in mind is that you could have a short-term and long-term portion of the loan and if that was the case, then I typically would not recommend breaking out the short-term and long-term portion when you're doing the normal transactions but rather putting them simply into one account, typically the current asset account and then periodically in an adjusting entry type of fashion at the end of the month or year breaking out the short-term and long-term portion and then reversing it making it go back to one account allowing you to track the balance in one account and break out for reporting purposes short-term and long-term portion when you need to display the reports periodically for financial reporting needs. So those are two of the main things. The other thing you want to keep in mind is that when you pay off the loan amount there's three accounts that are going to be affected, typically if you're following along with the amortization schedule which makes the loan payments a little bit more difficult than other type of payments. So in other words, and this would work also if you had like bank feeds or something like that that was turned on, you'd like to be able to memorize the transactions so that you can make your data input as repetitive and easy as possible either through bank feeds and automating them or by basically having the system at least memorize the last transaction when you do the data input. It's more difficult to do that as we'll see when we make the amortization schedule for the loans because there's three accounts affected and two of them are going to differ with each payment that we have even though the actual cash that will be impacted when we make the loan payments on a standard installment loan will in essence be the same. That's another problem that we want to consider. Now one way you can fix that problem is if you wanted to automate your system and make one payment then you could basically say hey look I'm going to be a cash basis bookkeeping system and basically just record all of my transactions to one account lowering the loan payable ignoring the interest portion altogether and periodically then at the end of the month or the end of the year adjusting to the amortization schedule breaking out the interest portion correcting the loan balance and putting the short-term and long-term portion at that point in time as an adjusting process and that could work quite well if you're a bookkeeper especially if you're working with a CPA firm or accounting firm and you give them that scenario you're saying hey I'm going to record things on a cash basis I'm going to tell you what the loan information is and I would like you then to make the adjusting entries periodically and as long as you have those two roles set then that can work quite well as well or you could say that I'm going to enter these into my system but I need an amortization schedule which sometimes you can get from the financial institution when you make the loan but sometimes they don't give you the amortization schedule you just have to derive it from in essence the the terms of the loan so that's what we'll do now I'm going to open up just write an Excel and I know this is not an Excel course but I think actually seeing at least watching the amortization table be constructed and constructing it yourself is a really good exercise to see how it's going to impact the loan payments and the bookkeeping side of things as well so I'm actually going to construct an amortization schedule here in Excel I'll do it fairly quickly because I know it's not an Excel course but we'll do it fairly quickly here to do a little bit of Excel so I'm going to increase the size here and so we're at 140 let's make it 160 160 I'm going to put my cursor in the triangle up top and I'm just going to format the entire worksheet in the format I want to start out with which I'm going to say let's go to format the cells and then we're going to say let's make it currency let's make bracketed numbers or negative numbers bracketed and red I'm going to remove the dollar sign I'll keep the pennies so that's going to be the format that I'm going to use here and then I'm going to put my data now typically when you use Excel you kind of want to push I'm going to grab this cell and make it a little bit larger by grabbing it in the middle left clicking and dragging typically you want to have your data on the left hand side and use the data in order to draw from when you're entering this information into the system allowing you then to change the data if you need to which will populate or make adjustments to your entire worksheet so that's kind of a good practice to get into I'm going to say the loan amount is going to be for $72,000 I'm also going to make the entire worksheet bolded because I think that might be easier to see so I'm going to hit my triangle I'm going to make the whole thing bolded now I'm going to assume the loan is $72,000 I know when I entered it into the system like I entered it two times here so we had an original transaction that was $50,000 or $22,000 and then $50,000 I'm going to imagine that we basically updated the loan and it's now at a $72,000 loan just for our practice purposes problem purposes ok so let's go back in so that's going to be the loan amount we're going to say the years of the loan let's say it's a five year loan and then the months months are going to be how many months then because we're going to say that most loans are going to be these installment loans which means you're going to keep the payment the same but you're going to have to pay monthly and that's going to have an adjustment to the principal so if it's five years how many periods do we have in terms of months it would be equal to the five times twelve or sixty months the rates that we're going to have is going to be the five percent which I'm going to type as .05 which would be the decimal and then change it to a percent by going to the home tab numbers and percentified I call it percentifying it that's not a real word but it's fun to say so it kind of annoys people sometimes apparently but I feel like it's a legitimate term so percentified so any case then the payment now we'll actually do the calculation for the payment now in practice you might they might give you the payment and they could actually not give you like the rate or give you the rate in some other format or something like that and and then you can use this kind of this kind of table to back into whatever unknown that you have in the system but we'll do in we'll use the payment calculation here just to show you how the payment calculation is calculated good tool if you were to practice financing or if you were kind of borrow money and you're trying to figure out what the payment amount would be and so on so we're going to I'm going to say negative to flip the sign otherwise the result will be a negative number I should put the negative somewhere in the reference but I'd like to just start off with the negative if I can to flip the sign of the whole thing and then it's a PMT function PMT for payment I'm going to double click on the payment function I'm going to pick up the rate now the rate is the trickiest thing and notice we got this little thing down here that allows us to do this and tells us what we're doing the rates the tricky thing because anytime you hear rate the rate means for a year and so because we don't talk about rates typically that are like monthly rates or daily rates unless we indicate that specifically due to in part the fact that one there's a convention that we talk about just rates in years and two because if we talked about rates that applied to the period of a month or a day or a week we'd be talking about very small numbers so that's going to be the convention so if we're going to be talking about monthly periods which is the periods that we're looking at here in terms of the payments we've got to make the rate match the period so if it's 5% per year I'm going to take that and divide it by 12 and then comma the number of periods then is going to be 5 years times 12 or we already did that 60 so we'll just pick up the 60 comma to go to the next argument and the present value represents the loan amount at the current date and for us that's going to be the 72,000 at the current date that's the present value we don't need the future value or type to wrap this up so I'm just going to close it at that point and enter so we're at the 13 58 58.73 notice it's actually longer than that hold on a sec it's actually a little bit longer than that if I add some decimals so it's rounded so you got to be aware that when you're in Excel it's actually going to use if I use this cell it's going to calculate based on that rounded number and that could kind of cause confusion sometimes but just be aware of that then we're going to build up our table on the right hand side I'm going to have the months the payments and this the payments and then we've got interest and then we've got the loan and notice I could do this when I have a long heading I could say loan reduction and then wrap the text alignment and wrap but that really kind of messes up everything to the left and the right of it so if I was to make a table out of it I would do that in other words if I planned on going to insert a table then I would need to do that if not and I just wanted to format it nicely I'm going to undo that and I'm just going to use loan and then reduction down here and then loan balance balance you often hear it called loan principle but I always spell it with the wrong principle there's two principles and people get mad at me for that so I just avoid the whole term all together now in any case I'm scared of spelling principle I spell it wrong but we'll select these items then let's go ahead and center alignment and center and then we'll go to the font and make this black and white this is what I do for my headers black and white on the headers and so then we need to make 60 months so I'm going to start at 012 we want to go down to we can use Excel will recognize that pattern if I select the three cells put my cursor on what they call the fill handle fill handle clicking on that and dragging down and it gives you that nice little number that shows you where you're at as you drag on down to 60 periods down here 60 periods and there we have it and then I can get rid of the decimals on this one and maybe center it because we're talking about months so maybe I get rid of the decimals number group I don't want those decimals I don't want those in this one and then in the alignment we're going to go ahead and center it here there we go okay so the payment is going to be the same well let's start up at period zero at period zero I'll just pull in the loan balance I'm going to say equals to 72,000 I'm always going to be pulling in from my data over here whenever I can and the payments then are always going to be that 1,358 73 so I'm just going to say that'll remain the same every time I'm going to do the calculation manually a couple times just so you can see where the problem is with regards to our bookkeeping which is the fact that the payment will remain the same cash in other words will be going down by the same amount every time we make a payment however there's a difference between the interest and the reduction in principle let me check my spelling by the way did I spell it? I spelled it right so that's where the issue is that's why you need the amortization table for just the bookkeeping process so let's go ahead and do this calculation a couple times here let's do it with the trustee calculator first because that might make it a little less intimidating so we'll do the trustee calculator okay well if the loan amount was 72,000 and then the rate was .05 then that would be 3,600 for interest for a year but it's only been a month so I got to divide that by 12 and we're looking $300 you can also calculate that this way you can take the .05 that would be the rate for a year divided by 12 there's that monthly rate we're talking monthly rates because that's really small and it would be hard to talk about so yearly rates are typically what we discuss unless we say otherwise generally then we multiply that times the 72,000 and we get the 300 let's do it with a formula here this is going to be the 72,000 times the rate 5% so H3 times B4 I'm going to take that and divide it by 12 just a hard coded number I don't need any brackets because it's going to do the multiplication before the division due to order of operations so we're going to say enter and there it is now I can't copy that down because I'm going to have a problem with it I'll copy it down later I'm just going to calculate a couple of these before we get into how we're going to copy it down in absolute references and what not so this is going to be equal to the payment amount we're going to be paying the 1,3,5,8,7,3 minus the 300 that means the loan reduction is going to go down by that 1,058,73 so in essence what's happening here we got 72,000 dollars of a loan you can think of it just like renting something else like you're renting the office building you're going to pay rent on it what's the rent on money for the purchasing power of it's called interest so when you pay the interest it's not coming back you're renting the purchasing power of the money so that means the loan reduction is going to go down by the amount after the interest so if I pay 1,358,73 I've got to pay the rent on the purchasing power of the money that I borrowed which is interest 300 dollars therefore the loan reduction is going to go down by the amount I paid minus the interest and that's going to give us then the 1,058,73 so this is going to be equal to the last balance minus the loan reduction amount so now the next payment is going to be the same you could do that by saying this is going to be coming from here but the interest and the principal are going to differ so we're going to say now the loan amount is the 70,941,27 times the rate which is the 5% that's the yearly rate so I'm going to divide it by 12 to get the monthly amount and so I have a different interest amount because the loan balance went down and so then I'm going to say alright that means the loan reduction is the payment minus the interest so there's the loan reduction and so now this was the last loan balance minus the loan reduction let's do it two more times and then we'll copy it down we won't do it 60 times don't worry I'm not doing it 60 times the 69,878 times the 5% divided by 12 the loan reduction is going to be equal to the payments minus the interest the loan balance will be the prior balance minus the loan reduction one more time UNO, VACE, MAS, POR, FAB or interest is going to be equal to the 68,810 0.56 times the 5% divided by 12 loan reduction is equal to the 135,873 minus the 28671 and we've got the loan balance equal to the 68,810 0.56 minus the 1072 0.02 okay so could I copy it down now let's copy it down there's going to be a problem I'm just going to select these four cells and try to auto fill down and note the problem and then we'll fix the problem once noted so we'll put our cursor on the fill handle drag it down and note problem so this one it dragged it down so anything that's outside of my actual calculation area where I'm putting my information is usually something in other words if it's in the data set it's something I usually have to make an absolute reference from that's the general rule if I'm referencing something in here usually I need to do anything it's going to do what I want it to do if it's in my data set then I typically need to make it absolute or mixed so I need to make that one absolute this one is picking up the this amount times hold on a second this amount was 5% here so this is oh I see it took it took the 5% down of course so again we took something from the data set so I got to make an absolute reference there this one's okay and this one's okay so let's fix it I'm going to undo undo undo undo undo undo undo so if I double click on this one is it coming from here or is it coming from my data set it's coming from my data set I don't want it to move down when I copy it down therefore I'm going to make it an absolute reference by hitting F4 on the keyboard or you can simply put a dollar sign before the B and the 5 you could make a mixed reference with one dollar sign but an absolute reference will work it tells Excel hey don't move that cell down the dollar signs mean nothing in terms of dollars they're just code for Excel to not move it down why did they use dollar signs I don't know but there it is and then we're going to double click on the interest this one has that 5% right there which is in cell B 4 B 4 so we're going to put that cursor in there and select F4 and B4 and then we got a dollar sign before the B and a dollar sign before the 4 we only need a mixed reference but absolute will work obviously the hard coded number is fine it'll stay what it is and this one we want to move down so it'll do it properly because it's inside of our table if I double click on this one these two items are both inside my table not from the data set so they are going to move down and that's what we want these two are both inside the table and they're going to move down that's what we want let's select the 4 cells again put our cursor on the fill handle and drag it down and if we did it right we're going to come to zero at the end of the day or at the end of the 60 days that is at the end of the 5 years there it is at zero so you can see this huge difference payment stays the same but huge difference between the interest and principal portions as we make our payments when we do our bookkeeping process then that's going to be an issue for us because when I make these payments I can't just basically subtract cash reduce cash and have the other side go to one account I have to get two accounts and those two accounts will change in terms of the dollar amount that will be going into them so I can't memorize the transaction as easily that's the problem so we'll go into next time and we'll enter two of these transactions just so we can see the difference within them I'm going to do some formatting here let's select these items and put some brackets around it maybe let's bracketize them another I'm making a verb out of them or something but these need to be borderized these need to be borderized home tab, font group borderized so there we have that let's go ahead and save it I'm going to save it and then next time we're going to go into our QuickBooks software and we'll start to enter this information using in essence an expense form or check form or possibly going right into the register and imagining that we're making some of these loan payments which when that happens we're going to see then a reduction to the loan amount of course we'll see a reduction to cash we'll see a reduction to the loan payable loan payable and we're also going to have an interest component on the income statement that we'll be dealing with