QuickBooks Online 2021. Make a loan amortization table, which we will then use to record loan payments within QuickBooks Online, making the loan amortization table with the help and use of Excel. Let’s get into it with Intuit QuickBooks Online 2021. Here we are, in our great guitars a practice problem, we’re going to be thinking about making payments on loans and breaking those payments out between interest and principal. As we do so, we’re going to be needing an amortization table. To help with that.
First, let’s take a look at our balance sheet by going to the reports on the left hand side, we’ll open up the old balance sheet report range, change it up top, we’re gonna end it off at the let’s say 1231 to one, and then run that report, close the hamburger and then hold down Control scroll up to get to that one to 5%, we’re going to go down to a loan down below, we’ve got the loan on the books at the 72,000. That was a result of a couple transactions. But we’re going to imagine at this point in time, we’ve got the loan at 72,000. And we’re going to construct an amortization table based on it.
A few things to think about with the loans. One thing is that in the thing we’ll concentrate in on here, the payment on the loans are a little bit more difficult to memorize. So if we’re doing the loan payments, say through bank feeds, if they’re electronic payments, or if we’re just simply writing a check or doing electronic payment, it could be a little difficult to break out the payment correctly. Because one, there’s three accounts that will be involved, typically, which will be the loan principal, and then the interest and then the cash account that we’re going to be paying.
And to because we’re making the loan amount standard or set most of the time when we’re making a normal kind of installment payment loan, the payment will remain the same. However, the breakout between principal and interest will not remain the same. And the only way we know what the breakout between principal and interest will be most likely will be we’ll have to have an amortization table. So that complicates the automation of us just simply doing the data input to do that process.
Also note that loans could have a short term and long term period to them, meaning if they’re over a year long loan, that we should break them out between short term and long term. Now that complicates things when we’re trying to just think about one account and see that we have the correct balance and tie that balance out to the loan balance. So in that method for that problem, I would recommend putting the loans on the books as a current asset in one account, and then simply breaking out the short term and long term portion periodically with an adjusting entry.
That way, when you make the loan payments, you can tie them out to the proper loan, it kind of makes sure that you’re doing everything well as you do the payments, and then do a adjustment periodically monthly or yearly to break out the short term and long term portion. Next problem loans could have multiple loans, some some companies have a lot of loans, if you’re in construction and whatnot, you may have a whole lot of loans on equipment or something like that. If that’s the case, you could put those loans into one account, and then have another kind of ledger or some worksheet that breaks them all out.
Or what I would do is put them all into different accounts, not breaking them out between short term and long term, but only doing that periodically, but have a different account, one account per loan, and then reference which loan you’re talking about with a low number on it, so that you can then go into it. And you can tie out the balance for each individual loan directly on your statements.
And then I would make it a sub account of a loan account here. So we can have a similar breakout like this, like we do with the payroll. So if I wanted to present it to somebody else and make it look cleaner, we can kind of have a summary balance sheet report that would that would not have all the detail in it. Okay, so those are just some of the issues that we have. Now the issue we’re going to be thinking on at this point is going to be breaking out the the interest and principal portion. To do that, you’ll need an amortization table.
Sometimes the loan company will give you an amortization table, but sometimes they will not. And oftentimes, they’re not going to because they’re trying to just tell you what the payment is. And they’re not trying to emphasize how much you’re paying in interest, because they’re trying not to emphasize that so you could then get the payment terms the loan, maybe give it to your accountant and have them break out the amortization table for you and then use that to enter the payments.
Or we might create an amortization table. Or we might tell the accountant Hey, look, I’m just going to record all these payments to the loan here. And I would like you then to break out the interest in principle periodically in the adjusting entry process at the end of the month or a year. It’s useful for us to actually make an amortization table so we can better understand some of these issues that will come up and so that’s what we’re going to do now I’m going to jump over to Excel. I’m going to do this fairly quickly because I know this isn’t an Excel course we will give you access to the amortization table.
So if you don’t want to build it, you don’t have to, but I would recommend putting it together because again, I think it’s a useful thing to look at Then it’ll give you a better understanding of loan. So I’m going to hit the, I’m going to open up Excel, I’m going to format the entire worksheet hitting the triangle up top right click here, I’m going to then format the worksheet, and we’re going to make it then currency, I’m going to make it currency.
And I’m going to say that, let’s take a look at the no dollar sign, I’ll remove the pennies, even though there might be some sensible outs, keep the pennies, we’ll put the pennies back on there. And then I’m going to say bracketed numbers are read and bracketed for the negative numbers, I’m going to hold down Control, scroll up just a bit. And then let’s just put our data, I’m just going to put the data on top, so we’re going to have a loan, the loan principal amount is going to be 72,000. So that’s the amount of our loan, the monthly payments monthly or appear, let’s say, period periods in months, or the number of months that we’re going to have, which we’re going to call periods here, we’re going to say this is a five year loan.
So I could say five times 12 would be 60 periods, the rate interest rate, we’re gonna say it’s 5%, I’m gonna say point oh five, and then I’m going to identify this cell by going to the Home tab, numbers and percent defy the cell. And then we’re gonna say that the payments then are going to be here. Now the payments is usually the thing that the loan provider will actually give you. And they may not give you like one other type of thing, like, they might actually not tell you the interest rate, because you could find it yourself, even though that’s kind of deceptive to do.
But note, what I want to do here is use Excel to figure out the payment. And then if you had one of these other items that was not provided, you can kind of back into it. And I’ll just show you how you can you can use that feature to do so because I think it’s a useful skill to know, this is a common formula in Excel. So one way you can do it is to go to the Formulas tab. And then I’m going to go to the Insert Function. And then we’ll use our dialog box here. And I want the payment form. So this is the one I want the PMT, so PMT, so I’m going to say let’s take that one. And then it gives me my little dialog box here. So what I want to do first is I want to pick the rate, the rate is in this cell 5%. The tricky part is is that 5% means per year, that’s what a percentage means I want to break it out to a monthly rate.
So what I’m going to do is take that and divide it by 12. So the rate is going to be that divided by 12. That’s the tricky piece of this data input. The number of periods then is not five years, because we have to pay monthly. So we’re going to say it’s going to be 60 periods, 60 periods, 60 months. So we got the months tie out to the rate per month, these two things have to kind of match, right, we got the months and the rate per month. And then the present value is going to be the amount of the loan 72,000. We don’t need a fair value or type.
That’s why they’re not bolded, they could be used in other formats that we use this function for, we’re going to say, okay, and there we have it. Now typically, I want to see it in a positive number. So I’m going to double click on it, I’m just going to put a negative in front of the P which in essence says Hey, take that whole thing and multiply it times negative one, or in other words, just simply flip the sign. So I’m going to say negative here, flipping it, and there we have it. Okay, so now, we’re going to go through and try to recalculate this in a table format.
Because what we have here is our number of payments. And we can kind of figure out what the interest would be then in total, like I could pull out the trusty calculator, for example. And I could say, all right, well, that means that I have 135 8.73 times 60 payments that I’m going to make, that’s 81 523 80 minus the 72,000 72,000. That means that we’re going to have to pay interest over the five years of 9005 2380 cents. But I don’t know how much interest I’m going to pay per period. That way, you know, because I need to I need to calculate the interest as I make the payments. That’s the problem we’ll look at here.
Also note that if you had this data, if they gave you this number, but they didn’t give you the rate, then technically, you still have all the information you need to calculate this. So you could use then I would use a function called Goal Seek to figure that out. So for example, I’m just going to copy these numbers, paste them here, instead of if I know this number, and I don’t know this number. Then instead, instead of me trying to recalculate this formula to kind of figure out this number like adjusted algebraically, because I’m using a function down here, I might just use the same function. So I have the same function.
And let’s pretend I don’t know what this percentage is. I’m going to make that yellow. Well, I could just say I can just guess what the percentage I’m going to say, Well, what if it’s like 10%. And now this numbers wrong, but I want this formula in place. So I can use it to back into this yellow item to know what that should be. So I know what it should be, it should be 135 8.73. So now I’m going to use that to kind of back into this number using the Goal Seek function. So what I’ll do is, and this is just a useful thing to be aware of. So we’re going to say that’s in the Data tab over here.
And I’m going to be off the cell. And I’m going to say that I want to go to the data tools, and we want to then go, I’m sorry, the forecast tools, and the what if analysis, and then Goal Seek, so we want a goal seek. So I want to set this cell right there, I want to set that cell to be and I have to hard code, the actual number that I want it to be, which is 135 8.73. So I want that cell to be that by changing the percentage, because these cells are linked with a formula that formula B and down here, the the system can then just do trial and error.
So I could say okay, and it does trial and error and finds the unknown, which again, of course, is that 5%. So that’s how you can kind of use this function to back into any one of these items that was an unknown, any one of them that are an unknown, you can back into it kind of like you were doing algebra, except you have this kind of difficult function down here. And that’s how you can kind of use your your algebra without doing the actual math to do it, right. So then let’s close this out, I’m going to delete this D here. And now let’s make an amortization schedule. And this is what we’ll actually need to kind of break out the payments.
So first cell is going to be the payment number. So payment number is going to start at zero, and then one. And then I’m going to copy this down to 60 payments, because we’re gonna have 60 payments. So I’m gonna, I’m gonna just take this and use my autofill. Because if I use autofill is going to guess the next number in the series, and this little number that’s showing up down there, I want to get it down to 60.
So I’m going to bring that on down to 60, like so like so. And then I’m going to format these cells to be non, I’m going to go to the Home tab, get rid of the decimals, decimals, and then center this, let’s center those, those are the number of payments. And then I’m going to have the payment amount, payment amount, which and then I’m going to have let’s do all the headers first the interest. And then the this is going to be called the prince the POW which I hopefully I spelled it correctly, because there’s principal and principal. And I get them mixed up sometimes.
So I think I got the right one. And so I’m going to call that the principal reduction, the principal is actually like the loan amount, the amount that’s still outstanding in terms of the loan amount. And then we have the principal that’s left principai. Now these headers are kind of a hardest thing to do on this loan. Once you get that down, then it’s pretty easy to do this. So what I’m going to do is I’m going to select these, I’m going to do some formatting to them. Home tab, let’s go to the alignment, let’s center them. Let’s go home tab font, and let’s make that let’s make it black and white. For the numbering. I’m going to bold a five a whole sheet,
I’m going to click the whole sheet Home Tab font and bold and bold and the whole sheet is now in bold and and so then then we’re going to say the principal is going to start at zero at times zero at that 72. So there’s our 72. And then our payments are always going to be the same, the payments are going to be this they’re going to be equal to this 135 873 the interest is going to be based on this principle which will be reduced. And that’s going to be resulting in a change in the principal and interest that we will have.
So we’re going to say Alright, this is going to be equal. Let’s do this in a calculator first, just so we can kind of see how we would do it in a calculator. So this is how I would do it, I would say okay, we’ve got this 72,000 times the interest rate point oh 5%. That would be the interest if it was for a year. And that’s what the interest rate usually is by default when given and let’s set otherwise they say that’s the interest rate per year, then I’m going to divide that by 12. And that would give us our monthly interest rate. You can also think of it as point oh five is the yearly interest rate divided by 12. There’s our monthly interest rate, which we kind of calculated when we when we did this calculation, we did that 5% divided by 12.
This is why you don’t use that rate, it’s too small. That’s why by default, we use a year as our standard rate or one reason at least. And then I’m going to multiply that times the 135 8.73. And well hold on a second. Let me do that again. Something funny happened point oh five divided by 12 times the 72,000. And that gives us our 300. Again, let’s do that with a formula here. I’m going to say this is going to be equal to that 72,000 here times the 5% That would be for a year, but then I gotta divided by 12. So there we have it. So if this is how much we’re paying, and this is the interest or kind of like the rent on the purchasing power, we subtract those two out, that would be the reduction in the principal.
Now we’re going to take the loan balance to 72,000, minus the principal reduction. So this is our new principle. Let’s do this a couple more times. And then we’ll try to copy and paste this down. So we won’t do it 60 times, don’t worry, we’re not going to do it 60 times. But let’s do it. Again, we’re gonna say this is going to be equal to I’m going to try to use formulas now, this one above it, I’m going to calculate the interest which will now change, which is now going to be equal to the new principal at the beginning of this month, times the 5% divided by 12.
So now we have a slightly different number here, if I subtract out the payment, which remained the same, and the principal, which is now going down, this is the reduction in principal, notice the principal reduction is going up as the payment stayed the same, because the amount of interest the rent is going down due to the fact the principal is going down after each payment. So if we subtract this out, we got the 70,009 41 minus the reduction in principal, let’s do it one more time, then we’ll think about copying this down payments going to be the same interest is not going to be the new principal, times the 5%. But then we got a divided by 12.
And then now the payment is the same, but the interest is now lower. So the principal reduction, the amount that’s going to be reducing the principal is higher. And now the principle is now the prior principal minus the principal reduction. So there we have now Now we want to copy this down, can I just copy the formulas down, let’s check it out. I’m going to copy these three, I’m going to highlight them, I’m going to use the autofill thing by putting my cursor on the little square here, left click and drag down to see if I can autofill it, I something went wrong most likely with these, this one did what we want it, that’s fine. This one did not because it pulled this percentage down.
And that’s the problem, I don’t want this one to go down. So b three, I need to tell the system don’t do anything to sell B three. So I’m going to undo that. And undo this, undo this, I’m going to double click on this number. And in this formula where it says B three, I want to make that an absolute reference, you can do that in Windows by selecting f4 on the keyboard, or just manually put $1 sign before the B and the three, you really only need $1 sign. But I’m just going to do an absolute reference here because that’s easier for me to do. And then enter. And now we should be able to copy this down. So if I highlight these and just autofill it down again, try it again.
There we have it. So now it’s doing what we what we want. That’s what I expect to happen. Now, let’s autofill this all the way down, once we get all the way down this principle after 60 payments should go to zero. If it does not, there’s probably a problem. If it does, that’s evidence to us that we got this whole thing correct. So I’m going to do the old autofill here. And so auto is going to drive it on down. And then Dr. Phil is going to do the calculation. And there we have it. So now we got a zero down at the bottom on the 60 payments. So that looks like we did it correctly.
The last payment that we have here, notice we’re still paying the same amount, but the amount of interest is much lower, and the amount of principal reduction much higher to take the balance down to zero, that’s because the rent on the money goes down as the loan balance goes down. So then I’m going to do a little bit of formatting, I’ll select this whole thing. And let’s let’s put a bracket around it maybe. So we’ll select this whole entire thing. Home Tab Font group, let’s put some brackets around that. So there we have that. So there we have it now now when we make the payments, then notice what I have to do, I have to make that payment with like a check or it might be a direct deposit, it might come out of bank feeds.
But then I got to break out the interest in principle in two separate accounts, which complicates things, especially with the bank feeds, it’s harder for me to memorize that transaction and the site. And not only that, but the second payment has a different amount allocated to interest in principle. So I can’t just simply memorize the transaction to break it out. I gotta break it out differently.
And I will need of course the amortization table to do it this loan summary thing up here, which I’m going to go to the Home tab Font group, and do that too, isn’t enough, we’re going to need we’re going to need an amortization table to do it properly. So we’ll talk more about now taking this amortization table and doing payments on it. making the payments into QuickBooks using it in a following presentation.