 Zero accounting software 2023. Make amortization table with the help and use of Excel. Get ready to become an accountant hero. Support accounting instruction by clicking the link below giving you a free month membership to all of the content on our website broken out by category further broken out by course. Each course then organized in a logical reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems, PDF files and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it with zero 2023. Here we are in our custom zero homepage going into the company file we set up in a prior presentation get great guitars duplicating some tabs to put reports in like we do every time right click in the tab to duplicate it right click in the tab up top to duplicate it back to the tab to the middle accounting drop down we want to go to the balance sheet report as it's thinking tab into the right accounting drop down we can go into the income statement report. Let's go back to the tab to the left and adjust the dates hitting the drop down up top to do so customizing the dates and I'm going to hit the drop down up top to 2023 and go to the end of 2023 and update noting we're now on the second month of data input so when we started the practice problem we started out by setting up the company file laying down the foundational items necessary to populate future data input like the general ledger and the items inventory and service items we put in the beginning balances and then we started the transactions for the first month of operations with those typical for a new business including financing transactions those necessary to get cash financing often happening by us the owner putting money in or getting a loan from the bank so we have the cash going up and we have the other side going then to the equity account for the owner investment and a loan account for when we finance the liability the cash the business with a loan and then we used that money generally with which is what businesses do to buy fixed assets so the fixed assets are what are going to be used in order to help us to generate the revenue and in our case we also had to buy inventory because we sell inventory so that's the typical starting process and then when we did the data input from that point we focused more on cash related transactions and less on accrual type of transactions and this month of operations we're going to start off with a couple transactions that are related to the payment of the loan which are somewhat standardized transactions but can be a little bit tricky and then we'll talk about more transactions that are on an accrual basis as opposed to a cash basis in the second month of operations so we're going to start off with this loan payable now this loan payable was on the books when we financed the business and we needed cash we increased cash the other side goes to loan payable now we're going to have to pay down the loan oftentimes loans like a mortgage loan are in an installment format so you might be paying down the loan on a monthly basis although business loans could quite often have any you know different kinds of structures in terms of how you're going to pay back the business loan but let's imagine it's a general kind of installment loan and then if it's an installment loan the next question is well when I make the payments on the loans how can I standardize that transaction you might want to say can I just use the bank feeds pay the loan down automatically using bank transfers and then use the bank feeds to record the loan payment that's trickier than you would expect even though the loan payments are the same in terms of full dollar amount typically if it's a normal installment loan because of the breakout between interest and principal so there's a couple different ways you could deal with that but it's not as easy as you would you would think as other kind of payments that you're going to be making periodically to basically simply memorize the transaction now to fully understand that let's build an amortization table we're going to do this in excel if you don't want to do it in excel then you can just wait till we finish the amortization table and we can think about how we're going to use that in order to record payments but the amortization table can be a little bit overwhelming and if you actually build one I think it really makes it solidifies what's going on also note there's there's many different online calculators that you can use to build an amortization table as well I think it's still a lot easier or not maybe not easier but better to do it in excel if you can because then you can use that for other projections and stuff so I'm going to open up our excel worksheet here and I'm just going to save the excel worksheet and then we'll populate this so I'm going to say file save as and let's browse and say we're going to record this in our folder I'm just going to call it an amortization table all right save it and then I'll zoom in a bit so we can see what's going on a bit better and then I'm going to select the entire worksheet this is what I'll typically do to start any excel worksheet I'll do this fairly fast since this is not an excel course but just to get an idea I'm going to right click format the cells and I usually start off with currency negative numbers bracketed and then no dollar sign I'll keep the decimals and then okay now whenever you're building something in excel it's often useful to put your data on the left so that you can build from the data instead of hard coding the data into what you are building so for example I'll put the information for the loan I'll make this a little bit larger I'm going to say we have a loan I'm going to make the whole thing bolded to I'm going to select the triangle to do that and font and embolden the entire thing so we'll say the loan let's say the loan is 72 000 so I'm imagining that the loan you know I know there's two items that we put into here to get to the 72 000 loan right there but I'm going to start it as if we're starting the loan at this point in time now obviously when you have the loan documentation they're going to give you uh the the terms of the loan they may or may not give you an amortization table because the amortization table is something that you can compute yourself with it so they're probably going to if you sit down with a finance person they're going to be talking mainly about how high your monthly payment will be but you want to take the time on your own away from the salesperson which is usually the finance person as well to calculate and think through the interests you're going to be paying and you might want to build your own table or something like this to do that or use the online table so we've got the year I'm going to say it's for five five years to loan so if it's a five-year loan and we're going to pay back monthly then the periods are going to be in months so if it's five years it's going to be five I'm going to say equals the one above it times 12 or 60 months that's the number of periods involved and then the rate uh is going to be a yearly rate and remember that is important because whenever we're talking about rates usually people talk about rates in years unless they say otherwise and you can and and people get a little bit tricky with the rates too when you talk to finance people if they say something like yeah the rate is 0.417 percent well they're not talking about a yearly rate right they're kind of being a little bit manipulative unless you unless they tell you explicitly that you're talking about monthly rates and you understand what's going on otherwise we usually use a yearly rate such as in a similar fashion we talk about salaries as annual salaries right someone says they make 70 000 a year we assume annual salary so we're going to say 0.055 percent I'm going to make that a percent home tab number group percentifying it and then I'm going to put the rate for a month for a month because if that's the yearly rate but we're going to make payments monthly then we need the monthly rate for calculations even though we don't usually talk about the monthly rates we usually call it the yearly rate right so the the monthly rate is going to be equal to uh five percent divided by 12 I'll make that a percent notice it's a it's a quite a small number I'm going to add some decimals that's why we don't typically talk in terms of of monthly rates because then we'd have to be dealing with these small numbers and whatnot and and that's one reason the the customers on a yearly rate and so then we're going to say the payment now notice you would normally they would give you the payment right and they might not give you they might try not to give you the rate or something like that and you can back into the rate but I'm going to use the excel calculator to calculate the payment based on this information and this is a typical tool used if you're trying to figure out how much loan you can afford because then you can figure out the payment based on the rate so I usually start this with a negative it's not the most proper way to do it but it's the easiest way I believe so that's what I typically do we type in PMT that's the payment function you can double click on the payment down here and then it gives you your argument down below to be populating the payment the first point is the rate now we could take the 5% yearly rate divided by 12 because what we really need is the monthly rate or I could just pick up this monthly rate we already calculated comma the number of periods that's what this stands for so we could say five years times 12 because we want the periods in months we already did that though that's 60 here comma and then the present value the present value is the 72,000 that's the loan that we got the 72,000 and we can basically end the argument there so I'm going to close this off and stop it there put the brackets around it and that gives us our 135873 so oftentimes if you take out a loan this is what they're going to focus on the 72,000 loan amount dollar amount that you need the money for and then the payment which is important because that's what you have to pay back on a monthly basis but you also want to keep in mind of course how much you're paying in interest and that's going to be important now once I have this number you might say well that's all I need to record my transactions from a bookkeeping standpoint but it's not because I need to know how much interest we're going to be recording now I can figure out the total interest but I can't really figure it out per payment so for example if I said how much am I actually going to be paying I can say total payments is going to be equal to this number times this number I'm going to be paying 60 payments of that amount which comes out to the 81,523 I can figure out my total interest in by taking the total payments minus the loan amount that I got so that means I'm going to be paying 9,523,73 in interest over the life of the loan but I'd also like to see the interest broken out per payment because that's going to help me to do my actual journal entries you know or my transactions when I make the payments so let's build our amortization table that also helps us double check this number by the way so I'm going to make this C a skinny C over here I'm going to type in my headers I'm going to have a year header I'm going to put this down here year and then I'm going to have months and then I'm going to have payments and then I'm going to have interest and then I'm going to have two lines here which are loan reduction notice I'm using two lines instead of wrapping the text because if I wrap the text it'll make this like a fat one a long one I guess a tall one and I don't want to do that unless I was using the table if I was going to make it into a table I might do that balance the loan balance and then what I do instead is uh notice you might call this principal reduction to and principal balance the principal of the loan but I always spell principal wrong and people make fun of me so I less often spell this wrong so that's what I do so I'm going to select these and then we're going to go home tab alignment and center these and then we will go into the bucket I'll make this black and white so it all looks like one header even though it's on two separate cells like over here all right then we're going to put the number of periods first and months because that's what we're going to count in 60 periods so zero one and then I'm just going to select these two and use my fill handle to drag that down to 60 so excel makes it pretty nice and easy drags it down to 60 boom right there and and by the way I'm going to scroll back up I'm going to get rid of the decimals I don't really need decimals here and let's center it if you were using the online uh loan calculator you could then have the data here right it would be like 75 000 and number of years five years zero months interest rate five percent calculate and then some of these some of these amortization calculators will will help you to calculate this did it on a yearly basis let's say 60 months and calculate it's still calculated on a yearly basis there here I've got one compounding monthly uh so I'm going to say okay pound monthly so here I got one 72 000 five year and then it gives us our little summary over here and then it'll it'll give us an amortization table so this is another thing that we can kind of check against as we do the data input you can check you can do it on this online calculator and see if your numbers tie out to that but let's go back on over here now then there's another little trick that you can that you can do to put the years because you might not just want the months you might want the years over here as well and the years can be useful because then you can use the years to help you to make like a pivot table and break the thing out by years which we might take a look at which can help for more planning purposes so we can do that by going here I'm going to start on this one so we can see a number when I populate it I'm going to say equals round up I'm going to use this round up function and then I'm going to say I want you to take this number and divide it by 12 because there's 12 months and and then I want you to round that up comma to the nearest whole number which you have to put 0.01 to get that round up to a whole number so if I say okay now it rounds up to one so in other words if I copy this down to like 12 down here it does one more time to 13 notice when it gets into year two month 13 it rounds up to year two so that works quite nicely for us I'm also going to copy it up here which will just be a zero and then if I copy this all the way down it'll give us the years so that'll help us to orientate ourselves when we're trying to look at our table to see what kind of year we're in if we want to look at it that way I'm going to remove the the decimals again get out of their decimals and then we'll center it and I'll make this a little bit smaller a little smaller okay so then we'll start our amortization table and a loan balance of the 72,000 and then the payments are always going to be equal to this number the payments will not change so I'm going to say f4 on the keyboard making it absolute so I can just simply copy it down dollar sign before the b dollar sign but for the six telling excel don't change those cell references when you copy it down and then here on the interest I'm going to say this equals the 72,000 divided by I'm sorry times the interest rate of fire of I could take the 5% and then divide it by 12 because that would be the yearly rate but we're going to take the monthly rate I'll just take the monthly rate here since it's here and then I'll say there is that now notice okay I'll leave that for now and then the loan reduction is going to be equal if I'm going to pay that minus the interest the loan is only going to be reduced by this amount so what's going to be the new loan balance after I pay this 1,358.73 it's going to be equal to the 72,000 minus the loan reduction amount and the interest is simply going to be an expense so the first payment that I make then I'm going to pay this amount we're going to expense 300 in rent which is gone just like if it was rent it's interest which would be similar to rent on like your office building and then the rest is going to be what pays down the liability bringing the liability down to 70,941.27 now I would like to be able to copy that formula down this one I can because I made it absolute so if I copy that one down I'm good this one it's not going to work if I copy this down and try to say what did it do notice it it moved this down I don't want it to do that so I have to do something to that one to copy it down so I'm going to double click on this this cell right here B5 I need to make that absolute or mixed I'm going to make it absolute because it's the easy thing to do dollar sign before the B and the five and I can copy that down and boom this one is actually good because these two cells will copy will change relative so if I copy that down then it does what I want it to do this one should also be good because I'm not pulling anything from my data set so if I just copy that one down it's good to go if I select these items here just double click on the fill handle boom it should copy it all the way down now if everything is done correctly at the bottom of this thing it should go to zero after 60 payments because we will have a loan balance that has been zeroed out as it does here so that looks good looks like it does what we would expect it to do all right scrolling back up then uh that that now notice the breakout between the interest and the loan reduction or principal changes that's what's going to throw off our bookkeeping that's why we need the amortization table simply from a bookkeeping standpoint in order to populate this and we'll talk more about that in the future presentation for now let's do a couple other fancy things to this I'm going to select this here let's go to the home tab font group I'm going to make that bracketed I'm going to make this whole thing bracketed let's say put some brackets around this I can compare these payments if I wanted to to my uh what we had on this online sheet 300 290 291 or 296 291 uh 300 296 about 291 because it was rounded over there all right and so and then I can try to make this into a table that breaks things out by year which is useful for planning purposes this is useful for data input uh in terms of our payments but you might want to say well how much am I going to pay per year and see it that way you can use a pivot table to do this or I can use formulas let's let's take a look at a couple ways I'm going to hit the c button I'll do this fairly quick because I because we're going beyond normal uh bookkeeping here but I think this is useful information so actually let's let's just do it with a pivot table I'll do it with a pivot table so so I can so I'm not going to select this is where the pivot table gets a little messed up because I have a head or row up there and I don't want to include that if I'm going to make a table out of it so I'm going to select everything from here down and I'm going to break this into a pivot table and that'll help me to break out the years which is kind of cool so let's just check that out we're going to select all that I'm going to go into the uh insert and we're just going to go into a pivot table and boom it selects that whole range that's what we want I don't want to put it in a new worksheet I'm just going to put it in the existing worksheet so we can see where the data comes from and then the location I'm just going to put it right next to where we have this one right there and there it is so I'm going to add it and then we get this little pivot table uh uh thing over here and what I'd like to do is pull everything in except for the months I want to see it by year so I'm going to say give me everything payments interest uh reduction and balance but I want to see it that way and then I want to pull over the years down here notice there's four boxes I want to pull the years over to this column so I'm visualizing this column so I'm going to pull this on this side so now it gives me everything by year so it looked up everything in this column by year and then now it's just summing up all the other columns on this side which is what we want for most of them but not all of them so now let's just do some formatting so I'm going to select each of these items to format it a little bit nicer and I'm going to select the value field setting and the first one I want it to sum that's right but I want it to look with nicer without all those decimals so I'm going to format the numbers and go to currency and get rid of the the dollars on and boom so there we have let's do the same for this one I'm going to sum of the interest so I'm going to value field it I want it to sum but I'm going to figure out the formatting here fix the formatting in the same way so that's pretty nice and then the sum of the reduction I'm going to hit the drop down and say let's format that one field settings number and make it currency boom no dollar sign this last one's a little bit different I don't want it to take the sum I want to take the lowest the lowest value in that range because I want it to be here that's where we where we end off at so it's a little bit different of a formula so we're going to say all right what can we do with that one drop down value field and I wanted to take the min the smallest number and then I'm going to number format it currency boom boom and okay so there we have it I can make these maybe a little smaller and wrap the text on the headers maybe wrap the text let's center the text and so now we've got this nice little table breaking it out by year so obviously that these payments we knew what that was going to be because they're all the same so I could just like take that by times 12 but the sum of the interest is a little bit trickier right so because it's different from year one which is comes out to 3304 right there then year two we're paying less interest in year two this becomes important when you when you start doing if you're doing tax planning or budgeting and stuff like that and then the loan reduction similar and then on the on the ending balance this is where we are at the end the 59 000 after one year of payments is where we're going to be right there so it's pulling that minimum number after one year minimum number after two years is 45 334 45 334 okay so so next time we'll take this amortization table let's save it and use it to actually make payments in zero and think about how we can break out the interest and the principle and do it as easily as possible there's a few different methods we can use