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 get great guitars practice file, we set up with a 30 day free trial holding down control scrolling up just a bit to get it out one to 5% currently in the homepage,
00:22
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.
00:38
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,
00:56
duplicating the tab, go back to the tab to the left, or right clicking on it again and duplicating the tab again, noting that the reports if we were in the accounting view by jumping back 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, that’s going to be in the business over view area.
01:21
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,
01:37
I’m in the standard, I gotta 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 see the ranging of the changing from Oh 101 to two to 1231 to two and run it and we’re going to go to the tab to the right.
02:02
And we’re going to go then to the business overview. And I gotta switch then to the reports. The standard reports Profit Loss, otherwise known as the income statement, closing the hamburger doing the ranging changing a 101 to two to 1231 to two and run it.
02:26
So now we’re going to be looking at the second month doing data input into the second month. Notice that as we do so you can run the Profit Loss 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,
02:44
Oh 201 to two. 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.
03:00
So there’s has been no data entered after January. If I was to change the date up top or the beginning date to
03:07
Oh 201 to two 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 have a wrist I don’t have the data in here.
03:30
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. So I’m going to change it back to a 101 to two and then run it.
03:50
So we’re now going to be looking at the loan payments. 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.
03:59
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 could put them into one loan account.
04:15
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,
04:25
possibly put in 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.
04:44
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,
05:01
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 their 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 breakout for reporting purposes,
05:21
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.
05:38
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.
05:46
So in other words, in 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 in automating them, or by basically having the system at least memorize the last transaction.
06:06
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.
06:28
That’s another problem that we want to consider. Now one way you can fix that problem is if you want to automate your system and make one payment, then you can basically say, hey, look, I’m going to be a cash basis bookkeeping system.
06:42
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,
06:59
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 and you give them that scenario, you’re saying, Hey, I’m
07:17
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.
07:31
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.
07:49
So that’s what we’ll do. Now I’m going to open up just write an Excel. I know this is not an Excel course. But I think actually seen 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.
08:09
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.
08:19
So I’m going to then I’m going to increase the size here. And so we’re at 140, let’s make it 161 60. 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.
08:40
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 signs, I’ll keep the pennies. So that’s going to be the format that I’m going to use here.
08:54
And then I’m going to put my data now typically, when you use Excel, you kind of want to put I’m going to grab this cell and make it a little bit larger by grabbing it in the middle left clicking and dragging.
09:05
Typically, you want to have your data on, you know the left hand side and use the data in order to 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.
09:23
So that’s kind of a good practice to get into, I’m going to say the loan amount is going to be 470 2000. I’m also going to make the entire worksheet bolded because I think that might be easier to see. So I’m gonna hit my triangle. I’m going to make the whole thing bold. And now I’m going to assume the loan is 72,000.
09:40
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 and then 50,000. I’m gonna imagine that we basically updated the loan and and it’s now at a $72,000 loan just for our practice. purposes, problem purposes. Okay, so
10:03
let’s go back in. So that’s going to be the loan amount, we’re going to say the years years of the loan, let’s say it’s a five year loan, and then the months, months months,
10:14
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 gonna have to pay monthly, and that’s going to have an adjustment on the interest and principal.
10:26
So if it’s five years, how many periods do we have in terms of months, it would be equal to the five times 12, or 16 months, the rate that we’re gonna have is going to be the 5%, which I’m going to type as point oh five,
10:41
which would be the decimal, and then change it to a percent by going to the Home tab, numbers and percent to five, I call it percent to find it.
10:49
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 percent defied this, so any case, then the payment, now will actually do the calculation for the payment.
11:05
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.
11:15
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 it, we’ll use the payment calculation here just to show you how the payment calculation is calculated. Good tool, if you were to practice, you know,
11:31
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 gonna, I’m going to say negative to flip the sign, otherwise, the result will be a negative number, you should put the negative somewhere in the reference. But I’d like to just start off with a negative if I can to flip the sign of the whole thing.
11:48
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.
11:57
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.
12:07
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 import the fact that one there’s a convention that we talked about just rates in years, and two,
12:21
because if we talked about rates that apply 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.
12:29
So if we’re going to be talking about monthly periods, which is the period that we’re looking at here, in terms of the payments, we’ve got to make the rate match the period.
12:37
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 five years times 12. Or we already did that 60.
12:50
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 a 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.
13:08
So we’re at the 13 585 8.73. Notice, it’s actually a little bit longer than that, oh hold on a sec, it’s actually a little bit longer than that if I add some decimals, so it’s rounded.
13:20
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.
13:28
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 the payments, and then we’ve got in to rest and then we’ve got the loan.
13:47
And notice I could do this when I when I 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,
14:05
I would do that. In other words, if I planned on going to insert and making 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.
14:24
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, and in case I’m scared to spell in principle, I
14:45
spell it wrong. But we’ll sit 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 what I do for my headers, black and white on the headers.
14:59
And so Then we need to make 16 months. So I’m going to start at 012, we want to go down to 16, we can use Excels, Excel will recognize that pattern, if I select the three cells will 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.
15:29
And there we have it, and then I can get rid, let’s 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 rid of the decimal number group, I don’t want those decimals,
15:41
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.
15:51
Well, let’s start up a 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 1003 5873. 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,
16:16
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.
16:28
However, there will be a difference between the interest and the reduction in principle, let me check my spelling. By the way, while I’m doing 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,
16:48
let’s do it with the with the trusty calculator first, because that might make it a little less intimidating. So we’ll do the trusty calculator.
16:57
And we’ll say, Okay, well, if the loan amount was 72,000, and then the rate was point o five, then that would be 3600. For answers for a year. But it’s only been a month.
17:10
So I got to divide that by 12. And we’re looking $300, you can also calculate that this way, you can take the point oh five, that would be the rate for a year divided by 12. There’s that monthly rate.
17:25
And you can see why we don’t talk in 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 270 2000.
17:40
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 h three times before, I’m going to take that and divide it by the divided by 12, just a hard coded number,
17:57
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.
18:11
I’ll copy it down later, I’m not going to get into that. Now I’m just going to, 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 whatnot.
18:22
So this is going to be equal to the payment amount, we’re going to be paying the 135873 minus the $300, that means the loan reduction is going to go down by that 1058 73. So in essence, what’s happening here, we got $72,000 of a loan. And that’s if you can think of it just like renting something else, like you’re renting the office building, you’re gonna pay rent on it,
18:47
what’s the rent on on money that you’re using for the purchasing power of, it’s called interest. And so when you pay the interest, it’s not coming back, you use you paying for your renting the purchasing power of the money. So that means the loan reduction is going to go down by the amount after the interest.
19:05
So if I make five pay 1003 5873, I’ve got to pay the rent on the purchasing power of the money that I borrowed, which is interest $300. 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 1058 73.
19:24
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.
19:39
So we’re going to say now the loan amount is the 70,009 4127 times the rate which is the 5%. But 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.
19:54
And so then I’m going to say all right, that means the loan reduction is the payment minus the interest. So Here’s the loan reduction. And so now what 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,
20:11
I’m not doing it 60 times, this would be the 669 878 times the 5% divided by 12, the loan reduction is going to be equal the payments minus the interest, the loan balance will be the prior balance minus the loan reduction.
20:31
One more time, no vase mas por five or interest is going to be equal to the 60 881 0.56 times the 5% divided by 12. Loan reduction is equal to the 135873 minus d 28671. And we’ve got the loan balance equal to the 60 881 0.56 minus 2107 2.02. Okay, so could I copy it down? Now, let’s copy it down, there’s going to be a problem,
21:06
I’m just going to select these four cells and try to autofill 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.
21:22
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 that I usually have to make an absolute reference from, that’s the general rule, if I’m referencing something in here, don’t usually need to do anything,
21:39
it’s going to do what I want it to do, if it’s in my data set, that I typically need to make it absolute or mixed. So I need to make that one absolute. This one is picking up is picking up the this amount much times Hold on a second.
21:57
This amount was 5%. Here. So this is Oh, I see it took the it took the 5% down, of course. So again, we took something from the data set, so I gotta make an absolute reference there. This one’s okay.
22:10
And this one’s okay, so let’s fix it. I’m going to 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.
22:27
Therefore, I’m going to make it an absolute reference by hitting f4 on the keyboard, or you can simply put $1 sign before the B and the five, you could make a mixed reference with $1 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.
22:50
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 four B four, what’s a B four. So we’re going to put that cursor in there and select F four,
23:07
and B four. And then we got $1 sign before the B and $1 sign before the four 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.
23:26
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 that they’re going to move down, that’s what we want.
23:39
Let’s select the four cells again, put our cursor on the field 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 five years there it is at zero.
23:56
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 that’s going to be an issue for us because when I make these payments,
24:13
I can’t just basically subtract cash reduce cash and have the other side go to one account I have 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.
24:30
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 I’m going to do some formatting here let’s let’s like select these items and put some put some brackets around it maybe that’s bracket ties them another I’m verb making a verb out of them or something.
24:50
But there’s needs did these need to be bought arised these need to be bought arrived home tab Font Group board authorized 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.
25:10
And we’ll start to enter this information using, in essence, an expense form or cheque 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.
25:32
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