 QuickBooks Online 2024. Make amortization table. Get ready and some coffee because we're diving into it with Intuit's QuickBooks Online 2024. First a word from our sponsor. Actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seeing with us but but that's okay whatever because our merchandise is is better than their stupid stuff anyways like this CPA thinking cap for example CPA thinking CAP you see what we did with like with the letters and this CPA thinking cap is not just for CPAs either anyone can and should have at least one possibly multiple CPA thinking caps why because based on our scientific survey of five people all of whom directly profit from the sale of these CPA thinking caps wearing this CPA thinking cap without a doubt according to the survey increases accounting productivity tenfold yeah at least yeah apparently the hat actually channels like accounting energy from the quantum field ether directly into your head allowing you to navigate spreadsheets faster it's kind of like how in like the matrix when neo learns kung fu or at least that's what the scientific survey saying so get one because the scientific survey participants could really use some extra cash if you would like a commercial free experience consider subscribing to our website at accounting instruction calm or accounting instruction dot think com here we are in our get great guitars two thousand twenty four quick books online sample company file we set up in a prior presentation opening up the major financial statement reports like we do every time the reports on the left hand side in the favorites right click on the balance sheet to open link in a new tab right click on the profit and loss to open a link in a new tab right click on the trial balance to open link in a new tab if you don't have that trial balance in the favorites you can search for it let's tab to the right we're going to close the hamburger and this time we will change the range but we want to go out through the second month of operations because now we're in the second month so we're going to go from 010124 to 022824 and then run that report now note that the balance sheet is as of a point in time so it's really only showing us what is happening as of the end of February meaning February 28 2024 but having a range from January to February could allow us to hit the drop down over here and change it to months and then run the report so now we can see where we stood at the end of January and the end of February they're the same right now because we haven't entered any data yet for February let's tab to the right similar process closing the hamburger I'm gonna make the range from 010124 to 022824 so now we're going once again for two months but this report is not as of a point in time but rather has a whole range to it so this would include income and expenses that have accumulated upwards up through the two months I'd also like to see this though on a month by month so let's hit the drop down change it to months run the report you can see in January all the activity has happened nothing happened in February and then they give us a total column which makes sense on an income statement doesn't make sense on the balance sheet because the income statement is accumulating over the two month period to get to the total whereas the balance sheet is just telling us where we stand at the end of each month let's go to the trial balance and do a similar process closing up the hamburger and let's do the similar thing although the trial balance is a little bit tricky here so if I go from 010124 to 022824 and run that report now we have the trial balance which is showing as if it's like a balance sheet as of a point in time doesn't have a range up top but it kind of is a range report as well because it's got the balance sheet accounts on top of the income statement so you can see down here we've got the revenue and the expenses so you would think this bottom bit is kind of a range type of report in essence so let's try to break it out if I hit the drop down and look at it by month and run that report so now it doesn't give us the total column it's still treating it basically like a balance sheet and there's no difference between the two because we haven't entered anything in February but it's still going to be a little bit wonky down here when you try to run a month by month on the trial balance possibly in part due to the fact that QuickBooks wants to close out the income statement on a year by year basis so meaning when I go into the next year that's usually when the income statement accounts will roll into the equity accounts and it doesn't really do that as well when I go from month to month so we'll kind of see that we'll check that out as we start doing data input in February just remember that the trial balance basically works best typically when it's what run on a year by year basis due to that closing process that's being used okay that said let's go back to the balance sheet over here now we're starting the second month of operation so quick recap what we have done started a new company file we then entered the beginning foundational items necessary to be able to do the normal data input including everything under the cog or things located under the cog under the your company like the account and settings the users the payroll the lists products and services for example and the chart of accounts we set up our vendors customers employees we then entered the beginning balances as of the beginning of the year january 2024 or the end of last year december of 2023 and then we entered our beginning balances we imagined came from the prior accounting system and then we finally were starting to enter the data for the first month of operations starting with those transactions typically used to finance the company to get the money necessary to pay for the startup costs which typically means you have to take out a loan or you're going to have equity that you're going to put in yourself into the business once we have the cash then we purchased the furniture and equipment that we're going to use to help generate the revenue and the inventory then we started recording transactions of actually selling stuff making money in january so now in february we're going to do a similar process continuing on february should be a little bit easier now because the chart of accounts are all set up we have a somewhat of a foundation to now copy it should start to get more cyclical in february in terms of the accounting process however we're going to start in february with some transactions that once again are kind of unique transactions or ones that aren't going to be normal all the time such as for example first we're going to be looking at our in our investments i'm sorry our loans here so now we want to think about managing our loans so we took the loan out last time where's where's my loan there it is so we took this out last time but now we're going to have to pay off the loan the problem with paying off the loan is that there's not just one account impacted typically or two accounts impacted there's interest that's going to be involved as well why is that a problem well that's going to throw off our ability to just use the bank feeds because what we would like to have happen is if i took out a loan let's say we pay it off like we do with a mortgage or something paying equal monthly payments as a as an installment loan or something like that well if i was to do that then it would be nice if i could just wait till the payment clears the bank do it with an electronic transfer then use the bank feeds to simply record the reduction to the checking account and the other side going to the reduction of the loan that is there but i can't do that because they're going to charge interest and interest is basically simply the rent of the money it's it's just like renting an apartment in essence you're using the apartment or you're using the office building for work if you're renting it you have to pay for the space well in this case we rented the purchasing power of money in order to get the fixed assets in the inventory that we needed to generate revenue and therefore we have to pay rent interest on that purchasing power that we got in advance so the problem with the interest however is that it changes from period to period that's the cost of us having to have an equal payment an equal dollar amount payment results in the breakout between interest and principal will be different which makes the transaction more difficult because i can't memorize the transaction because it's going to be different every time same dollar amount but different allocation between interest and principal there's a couple different ways you can deal with this one way is that uh is that we can make an amortization table and we can just tie it out to the amortization table each payment another way we can deal with it is you might say hey look i'm on the bookkeeping side i want to automate this thing i don't want to deal with that problem what i'm going to do is just make it a cash to based system and i'm just going to record the the payments as a reduction to the loan payable even though i know that's wrong but it's easy to do and then i'm going to tell my accountant or cpa at the end of the year hey you make the amortization table and you do the adjusting entry as of year in to break out the proper portion of interest and the loan balances the way it needs to be for external reporting and possibly for taxes if you have a good bookkeeper and a good accountant that knows how to do that if that system works quite well because then you can actually automate your bookkeeping as much as possible and not have to try to do that process as you go and then just fix it periodically basically at the end of the year so other problem is that the loan payable might include multiple loan accounts so if we have multiple loans then you could put all of the loans into one loan payable account and then support it with sub ledgers like amortization tables but internally it's easier oftentimes to to have a parent loan account and then break out all the sub accounts of the different loans construction companies for example often have a lot of loans uh farms might have a lot of loans right because they they have loans on the equipment that they need to help to generate uh the revenue so so that that's another thing to think about and then also the next problem is if we pay these off in installments then there's going to be a current liability and a long-term liability of the loan because the amount that's due within a year is current the amount that's due after a year is long term so we don't really want to break out the current and long-term portion however each time we make a payment because it'll be different every time we make a payment and it'll be more difficult to tie into the actual loan balance if we're doing that on a payment by payment basis therefore once again i would make one account here for each loan and then basically uh record everything to that one account and then ask the accountant or bookkeeper to break out the short term and long term portion according to the amortization schedule on a periodic basis possibly monthly or yearly okay first thing we need to do to make this work though is an amortization table if we're going to make the loan payments according to the amortization table generally and they might not give you one like the the people that give you a loan might not give you the amortization table the idea being that you have all the information necessary to create one that's kind of like the legal requirement basically oftentimes uh and so if you if they actually give you one you might look at how much interest you're paying and be like what so hey guys so let's i'll show you how to make one in excel which i think is the best way to actually see what is going on there's also a lot of online calculators that you can use as well look something like this i think it's best personally to make the amortization in excel and then use the online calculator as a double check to make sure everything's lined up because then you can do projections and things like that but so this is going a little bit outside of QuickBooks to do this i'll do this fairly quickly but i think this gives us an idea of the problems that that the loans provide and then it'll help us with our data input so i'm going to make a quick amortization table based on the loan that we're imagining to be seventy two thousand dollars so i'm in excel we'll we'll do it an excel amortization table so the first thing i do in excel is i format the entire worksheet so i'm going to select the worksheet with the triangle i'll typically right click in the middle of the worksheet and format all of the cells to put the foundational baseline formatting in place in which i usually go to currency negative numbers bracketed no dollar sign and then do we want the decimals let's keep the decimals we'll keep the pennies and we're going to say okay and then i'll put my data over here that i'm going to use to create the amortization table so what do we know about this loan i'll make this a little bit larger and we know that it's i'm scrolling in so i'm at 265 percent in we know the loan balance loan balance is we're going to say 72 000 i'm going to make the whole thing bold as well select the whole thing home tab font let's make it bold so that we can see it better you don't have to do that but i think it might be easier to see on the recording and then a year we're going to say it's five year loan let's pretend it's a five year loan this would be on the loan document typically so if they tell us it's a five year loan then we can say well how many months is it because we're going to imagine that we pay it off in monthly installments 12 months in a year so it would be equal to five times 12 that gives us the 60 months we'll have to deal with what's going to be the interest rate so i'm going to say what's the rate year now this is where it gets tricky remember that whenever we use a normal convention of an interest rate we usually say it's per year so so just like a salary if i say someone earns a hundred thousand dollars you're probably not going to assume it's a hundred thousand dollars an hour right or a week or a month you're going to assume it's a hundred thousand dollars a year not just because it makes sense mainly normally but also because that's just the convention if someone asks you how much salary someone earns they often are thinking in a yearly context same with interest rates interest rates are usually in a yearly context but you can give the interest rate for something other than a year so in other words we're going to be paying rent which is interest on the purchasing power of the money monthly so why don't we give us the monthly interest rate well the monthly interest rate would be very small it'd be a small number so it kind of makes more sense to use the convention of a yearly rate because that's just kind of the standard but we're actually paying it off monthly so that gets a little tricky so let's say it's 0.05 or five percent i'm going to make that sell a percent by going to the home tab number group percentify it so it's five percent let's then calculate what the rate is for a month because that's what we're actually going to be paying on a monthly basis not on a yearly basis so how can i do that i can just say let's take that five percent divided by 12 let's make it a percent looks quite small i can't even see anything there so let's go to the home tab numbers group and we will percentify it so we can recognize and then numbers and add some decimals so it keeps on going but we can put like four places out so it's about 0.1476 percent which is a small number that's why it's not very useful to use in normal language although of course excel can see that number quite well also note that excel is going to going to calculate this based on the decimal repeating not just based on the four digits that are showing okay so then we're going to say we can then calculate the payment now normally they would give you the payment and it's possible that they don't even give you the rate if they give you the payment because then you can calculate the rate and you might have to back into the rate right that would be kind of a shady thing to do it seems to me or they might give you the rate in different contents context they might say the rate is this and and then per month when you're normally thinking in years so you have no idea what that rate means it sounds quite low but that's because it's per month instead of per year but now i'm going to calculate the payment though just to show you the calculation of the payment and this could be useful if you're thinking about getting a loan then you can adjust any of these inputs and think of the payment so the payment calculation i'm going to start with a negative which isn't exactly proper but it's the easiest thing to do pnt is the function double clicking on that now the rate that we're going to use i'm not going to use the five percent i'm going to use the monthly rate and i'm not going to type it in there i'm going to put this rate in there because it calculates it's more than those four digits or you could take the five percent divided by uh divided by the number of months and you get the same thing right and then i'm going to say comma and we're going to say then the so it goes the next one is the the number of periods that's what the n per stands for not five years because we're talking in months so it's five times 12 or 60 60 months because the periods are in months so we have to have these two match we have to have the rate match the periods it's monthly periods monthly rate comma and then the present value is the loan balance 72 000 all right let's close that up and enter and there's the payment so 135873 is the payment okay so now let's let's we could do a quick calculation of total payments then total payments so what's this going to cost me over the life just in dollars without considering inflation and it's going to cost me that payments times we're going to make 60 of them 60 payments so that's going to cost me 81 523 73 okay so what's the total interest that we're going to end up paying well we we borrowed 72 and we're going to pay back 81 5 right so that's the difference between that is the interest 81 5 minus the balance so that's nice to know but it's it's not really that helpful for us for bookkeeping because i need to know the interest per payment that we're having and and so you know so what we need to do then is break out an amortization schedule so i'm going to make this a skinny skinny c and i'm going to put my headers here i'm going to say i'm going to put it at the bottom years and then i'm going to put months and then i'm going to say these are the payments and then i'm going to say this is the interest and then i'm going to say this is the uh loan reduction and then the loan balance okay so so and you might call this the principal the loan principal and then the loan balance but i always misspell principal and so i make it the wrong principle and then people make fun of me so i say loan reduction so then uh then notice i put these in two cells here you could put it in one cell like loan reduction but then you get this long cell how do you deal with that well you could wrap it home tab alignment and wrap it and then center it but that's cool but it doesn't make it perfect still and then it makes this wide cell over here so if i'm using a table then i will do that because because i need one cell to make a table but if i'm not doing a table what i'll do is i'll just break it out into two two uh things here two rows and then i can make it look like a header by just selecting this up top and going home tab and i'll make my header font group and make it black and then white uh and then centered a white over here and then centered so now now now i don't have to deal with that that fat number one or the long number one the number one's got a long face like a horse so then i'm going to put the number of months so i'm going to say months there's going to be 60 of them this is easy to do in excel though i can just say zero one and then i'm going to select those two put my cursor on the fill handle drag it down to 60 you could use a function to do this a fill function too but i don't think this is that difficult it gives you that nice little number thing so we could just go down to 60 boom boom right there and then that looks good now we might not want the decimals on this one so i'm going to go home tab number let's get rid of the decimals so it's cleaner looking we could even center it but i won't do that and then it's useful possibly to know what year we are in as well so notice if i if i look at this this gives me like if i'm down here somewhere it's like well what year am i in that also could help us to summarize the data by year as well so how can i get the year uh well i could one way i could do that so i could say this is going to be equal and i'm going to use the round up function and i'm going to take this number the months divide it by 12 and then i'm going to round it to negative 0.01 oh man what did i do negative 0.01 and that just tells us that tells itself the digit that we want to round it to so i'm going to say boom and it gives me a one let's see if it works now so if i scroll down when i get to 13 see now i'm on year two so that looks like it's doing what i want so if i copy this all the way down it gives me the year that i'm in in each row note if you're going to build a table you want something in each row i don't want a one here and then nothing and then a two down here if i'm going to build like a pivot table from it i want something populated in in each row so up top i could do the same thing that should give me zero and then i could get rid of the now that i have the decimals i can see it's right i can get rid of the decimals so boom get rid of the decimals okay and then we have our payment but what i'll typically do is start off with the loan balance so that's why i like to start off with zero so i'm going to pull in the loan balance that's the original loan equals i'm pulling it from my data on the left boom 72 000 the payments are always going to be the same we calculated them right here so i'm just going to say that's always the same the payments will remain the same so if i'm going to drop that down i'm going to select f4 on the keyboard putting a dollar sign before the b and the six because the payments are the same and that means it's when i copy it down by double clicking on the fill handle it's not going to change that cell the other way you can do that by the way which i often use is useful is you can go to the next cell down equal the one above it and then copy that down so now it just keeps on equaling the one above it but let's do it let's go back to the other way and oh the other way just like let's say this is going to be this and then f4 absolute reference double click to drop it down okay so then the interest calculation this is going to be the interest per period now the thing with interest it's like rent right even though i'm going to be paying this amount of interest i haven't incurred it until i actually use the purchasing power of the money it's like saying you signed a contract to rent an office building and it's going to cost you you know uh ten thousand dollars or whatever hundred fifty thousand dollars a year or something like that that's fine you you're locked into doing that you've committed but you haven't actually incurred the fifty thousand because you haven't used the office building yet same thing has happened in here we've kind of locked ourselves into this agreement but the interest although that's the total interest we expect to pay hasn't happened yet so the first month means that the first interest payment will then be calculated how do we do it seventy two thousand loan balance times the we could take the five percent divided by twelve or i can take the monthly rate so there it is uh three hundred dollars now if i copy this down uh i want i want anything that's in my data set typically the general rule is i need to make absolute because over here this cell will follow down as i copy it down this cell will not so this is b5 i'll put my cursor in b5 f4 on the keyboard dollar sign before the b and the five so that when i copy it down it's not going to move that cell down so i'm going to hit enter put my cursor back on it double click the fill handle and uh it didn't do it right kpa so oh that's because i have it so that's fine it still works but i have to do the rest of it so then the loan reduction so if this is the loan payment which is the same and the interest for the first month is three hundred then what's going to happen to the reduction of the loans this minus this this is how much of your payment of that is going to the reduction of the loan the difference three hundred dollars is rent of the purchasing power the money that's just gone so now we're going to say the new loan balance is going to be equal to this minus this and so there we have it so now we were at 72 000 and then we paid this amount 300 of it was interest therefore the loan reduction only went down by the 1058 73 here's the new loan balance if i copy that down we copy this down i can copy this down same thing but now the loan balance is seventy thousand nine forty one which means if i keep the payment the same then which i want to do because that's easy for budgeting that's why we loans are often set up that way but then the interest in the and the loan reduction are different now so now i have a different amount of interest because the loan balance is different and therefore the rent on the outstanding balance is different and therefore the loan reduction is different and that gives me a different total so if i copy that down that's the trend that we will see here's the same payment but notice what happens to the interest the interest is going down why because the rent on the purchasing power of the money is less because we have less money that we're purchasing because the loan balance went down last time right and so then and then the loan reduction amount goes up so you're paying a lot more interest at the beginning of the loan then the end of the loan if i copy that down i can double check this going to the bottom and saying there if it gets to zero after 60 payments that's a good indication that we have done this properly now i would we can also check this against online tools so i could say let's go into this tool this is i'm not affiliated with them or anything but if you just type in online amortization calculator or something you'll get something like this so we have a loan amount 72 000 i just put 60 months interest rate five percent we calculated it there's our our total payments which i think we had here right we did that there's that and there's the interest did and then if i look at the monthly schedule it gives me this monthly schedule here the first one 300 on the interest 295 290 and so on uh so it looks like we've done it properly here okay so the so that's nice because now when i make a payment which will do in a future presentation when i make this payment i can't just reduce i can't just impact two accounts like like the loan balance and the and the cash account i have the interest i have to deal with and i can't memorize the transaction because the interest changes even though the dollar amount that i pay does not so that's what we have to deal with next time now also note if you're looking for long-term planning or something like that you might want to try to make a table where where all of this is is uh is is in a table right so let me show you how to do that i'll just do it there's a couple ways to do it let's just do it with a pivot table though i'm going to put zeros here so that we have zeros and then what i do is i make this into a table now this is going to be a little bit messy because i made these two columns so the table headers are only going to be right here but that's okay i'm going to put my cursor somewhere in here and go into the insert and then tables i'll make it into a table i like to have it in a table format and i don't want it to include that top row though so i want it to be from here to here because because that top row is a header so that's a little bit messy but i don't want to include that top row okay so i'm going to say boom and there's basically kind of a table format from it now now i'm going to take this table and you don't have to make it into a table first i just kind of like to do it when i'm when i'm then going to be making a pivot table for it for some reason but now we have it in a table let's go ahead and then say we want to insert and this time i'm going to make a pivot table so from table range boom and so now it's picking up the table range and i think i want to add the headers on it so i'm going to add the headers so it has headers in the table let me pick that up so i'm going to say boom there it is worksheet existing now i'm going to put it in an existing worksheet i'm going to put it over here so now it's in that worksheet instead of in a new worksheet uh choose whether you want to analyze multiple tables i'm just going to say boom that's all i need and then we if i pull this to the right we get this little pivot table thing so what i'd like to do then is take a look at the years if i click on them it tries to sum them up because there's a number in it but i want the years to be actually in the rows so now it's showing me the five years and then i wanted to give me the data in terms of payments interests uh reduction of the loan balance and the balance so i get this nice little table that is created on a yearly basis from from my amortization table so then i can close this back out and then and then then notice the formatting of the numbers isn't perfect so maybe i can go into here and say that i want to say drop down and i want to value uh field settings and then i want this to be summing so that's correct but i want to edit it and then i'm going to say currency on the format negative numbers no dollar sign and we'll do that boom boom all right so it's just format i could do that with the interest as well so on that column i'm going to i'm going to value edit summing summary and then the number format i want it to be currency negative numbers bracketed no dollar sign boom boom same with the uh reduction one the last one's a little bit different so let me show you this one i'm going to say this is going to be uh that's good summing it number format currency negative no dollar sign boom boom now the last one i don't want it to sum i wanted to show me the balance after a year so for example so this one i'm going to say value now this one i don't want it to sum all the data there i wanted to show me the min number out for everything that has a year one in it for example which will be the lowest number the balance at the end of the year so i'm going to say okay uh hold on a sec and then i'll format it so that's good then let's format it so i get the uh currency negative numbers no dollar sign boom boom and then i can close this out and then let's make these a little bit smaller like that and then i can i could once again wrap these home tab alignment and wrap maybe and center and so there we have it so now you can see this is this is kind of nice the payments of course you know are what they are for for uh the year so i can just add up the payments it's just going to be this times 12 right if i add up this you get the 16304 that looks right per year but the interest will change per year so it's a significant difference so if i look at this i could say okay the interest is 330477 versus the next year the interest is 2639 even though the payments are the same and that can help you with your budgeting because the interest is deductible if it was a business and and so it's it's significant that the interest is significantly different even though the cash flow is the same then the the reduction uh is going to be different too because it's going to be impacted by the difference in the interest even though the payments the same so you can see the reduction for the first year is here 12999 right and then the ending balance where are we going to be after each year so at the end of one year we're going to be right here uh right there 5900002 right so 5900002 and then at the end of two years where are we going to be we're going to be right here 45334 uh 45334 so you can you can find online tools to kind of do this as well but the reason i think it's really nice to do in excel is that if you wanted to then use this to budget or something like that or you're thinking about different loan options and so on then you can actually use this as a template to then uh uh to to do your calculations you can you can use this to then run a run a budget on and if i change any of the data over here like let's say it was 7 percent you know you then we can recalculate all this and this table is a little bit tricky you have to refresh the table but we can do that pretty easily right clicking and refreshing and then we can use this data to then build basically a budget if we wanted to do that let's bring it back to the 5 percent and so i think that's actually useful uh but we're we're let's refresh it back to here boom but we'll just use it to record our calculations i'm going to put some home tab font group here okay so we'll use the amortization table in a future presentation to record our uh payments so that is what we will primarily use it for