Creating an Amortization Schedule

Hi guys and welcome back to my channel. In this video I want to show you how to put together a loan amortization schedule. So this one is very much for the accounting professionals out there. Now if you're not sure what an amortization table is, it is something that is used frequently in the accounting industry. It's a table that displays periodic loan payments showing the amount of principal and the amount of interest that makes up each payment until the loan is paid off at the end of its term. And you might have received something like this from your lender for mortgage payments on your house. Or maybe if you've taken out a loan with your bank, you'll usually receive documentation which shows an amortization table so you can see how many payments you need to make, how much interest you'll pay each month, and by how much the balance decreases each month as well. So if this sounds like something that you're interested in, please keep on watching. So here we have my loan amortization schedule that I need to complete. And you can see that I've already added in a little table down here and just applied some formatting to this table. If you want to work along with me then please feel free to download the workbook. Now when it comes to working out all of these different calculations, we need to have our base information. And you can see at the top here I have a little table that contains the information required for us to work out our payments. So that is exactly what we're aiming for here. We want to complete this table so we can see how much we're paying each month, how much interest we're paying, how much that is decreasing the balance until we get to our ending balance. So if we take a look at this table, you can see I have some basic information. I have the loan amount. So we're borrowing 400,000 pounds. I have the loan type. So when I make my monthly payment, am I going to make that at the beginning of the month or at the end of the month? And I'm going to make my payment at the end of the month. I then have a balloon payment. Now you don't have to have a balloon payment section in your schedule, but this would be if you're going to a bulk payment at the end. So I'm going to be paying off this loan every single month until the loan gets down to 50,000 pounds, at which point I'm going to make a payment of 50,000 pounds to clear that loan. So that is my balloon payment. I then have my interest rate. And notice that this is an annual interest rate. The loan period, so the number of years over which I'm paying this loan, that is 30 years. The number of periods per year, so essentially the number of months, the number of payments I'm going to make each year, which is 12. And then we have the start date of the loan. So I'm going to start paying this loan back at the end of October. So this is all of the information I need in order to be able to complete my amortization schedule. Now, before we dive into this table, there are a few calculations that we need to perform just above. And you can see I've separated this from the rest of the table just by highlighting these cells in blue. And for me, I find that's helpful so I can separate my inputs from my actual calculations. So the first thing we need to work out here is the period rate. Now, remember, everything we're doing here is monthly. We want to make a monthly payment. And currently, looking at my table above, my interest rate is displaying as an annual interest rate. So in order for this calculation to be correct, I need to work out what the monthly interest rate is going to be. So this is a very simple calculation. Let's type in equals. We want to do the interest rate divided by the number of periods per year, which is 12. And that gives me the monthly interest rate for this loan. The next thing I need to find out is the total number of periods. Basically, how many months am I going to be paying this loan over? Well, I've got in my table above the loan period in years, which is 30. I need to work this out in months. So this is going to be a very simple calculation again of the loan period in years multiplied by the number of periods per year. So 360 months. Now that I've worked that out, I can now use the PMT function in Excel to work out my monthly payments. And PMT simply stands for payment. And you can see that we have five arguments just here. The first argument is rate. The second argument is NPER. That means the number of periods. The next argument, PV, is present value. We then have FV, future value, and type. And notice that FV and type are contained within square brackets, which means that those are optional. Now we're going to use all five of these arguments. So the first thing we need to provide PMT with is the rate. So we have the rate, the monthly rate, just here, D13. Comma. The number of periods. Well, we have that as well. The number of periods is 360. Comma. What is the present value of that loan? Well, the present value is basically the loan amount, which we have in cell D6. Now one thing I'm going to do here is I'm going to put a minus sign in front of D6. Because if I don't, when we complete this PMT calculation, the monthly payment is going to show as a negative value. And I actually want it to show as a positive value in this case. So a way to do that is to just add a minus in front of that loan amount. Comma. FV, future value. This is the first optional argument. Now this relates to that balloon payment. Basically, what am I trying to work my loan down to? Well, it's not going down to zero, because I'm making a bulk payment at the So future value is $50,000. Comma. What is the type? Am I paying at the end of the period or at the beginning of the period? Well, I'm going to be making my monthly payments at the end of the month. And if I take a look at my information above where it says loan type, I can see I've got there that I'm paying at the end. So my final argument here is zero for end of period. Close my bracket. Hit enter. And that is my monthly payment. Now this is currently showing in dollars. I'm just going to change that very quickly to pounds. There we go. Now this PMT calculation is really going to be the basis for a number of things that we do in this table below. So it's so important that we get this right. Now that we have this information, let's go through and start completing our table. So the first thing we need to add in here is the payment number or the period number. And I can see that my total number of periods is 360. So I basically want to have 0, 1, 2, 3, all the way down to 360 in this payment number column. Now I'm not going to go through and start typing in 0, 1, 2, so on and so forth, because there is a much better way of doing this. Let's type in 0 into the first cell. Remember, if you want to stay in the cell that you're in, enter. And what we can do here is we can hover our mouse over the autofill handle until we get that tiny black cross. If we hold down our right mouse button, drag down one cell and drag back up and let go, it's going to present us with this little secret menu. And what I can say here now is fill in the series. I want to fill it down the column. The step value is 1 and I want it to stop at 360. When I click on OK, it's going to fill all of that down for me. And if I press control down arrow to jump to the last row, you can see there we have 360 at the end, which is perfect. Control up arrow to jump to the top. So now we have our payment number, our periods. The next thing we need to complete is the payment date. And my payment date is going to be the last day of every month. So my first payment is going to occur on whatever the start date is of this loan. So I can see up here that is 10, 31, 20, 21. And for anybody watching this video in the UK, I actually have my Excel set to American date format, if that is confusing you at all. Now, I want to drag this down. Now, I don't want to go through and have to enter in manually all of these payment dates. So you might be thinking to yourself, well, can we use the autofill handle? Well, let's give it a go and see what we get. I double click to copy these dates down. Take a look at what happens. It basically lists out every single day. So we have the 31st of October, then the 1st of November, 2nd of November, 3rd. Now, I don't want that. What I want is the last day of every month. So hopefully what we can do here is we can use our smart tax. Take a look at the bottom. Can you see this little icon just here with my autofill options? If I click, I then get a series of choices. So what I can say here is fill the months and it's going to give me the last day of every single month, including February. So you can see here, it's recognizing that February only runs to the 28th this year. And it's worth noting that if it is a leap year, it also recognizes that as well. So now I have my payment dates in there. We are looking good so far. The next thing we need to complete is the PMT, so our monthly payments. Well, we have our monthly payment just here. So all we need to do is link to that cell. Now, I want to drag this down. So I'm going to need to lock this cell by pressing the F4 key and hit enter. And then I can simply double click my autofill handle just to copy that payment down. Now, the next thing I'm going to complete in this table is the ending balance. And remember, this ending balance is going to reduce as we are making our payments. But at the beginning, our ending balance is $400,000. So we just need to link through to that loan amount. Now that we have that, I'm going to go back and work out the amount of interest. Now, this amount of interest is going to be different for each period because as we reduce our balance, the interest is also going to go down. So our interest payment is going to be the balance multiplied by the period rate. And because we're going to drag this down, we need to lock D13 and hit enter. So currently with our first payment, you can see that quite a lot of the amount that we're paying here is made up of interest. Next, we need to work out the principal reduction. So how much are we actually paying on this loan that is an interest? How much are we reducing our balance by? Well, this is going to be our PMT, so our monthly payment minus the interest. Now, if you're somebody who likes to work with the keyboard, what we can do here is just use our arrow keys to go across to PMT. And we're going to say minus, use our arrow keys again to select the cell. Control, enter will leave us in the cell. So with my first payment, you can see I'm paying 1,777.15. 1,250 of that is made up of interest, and I'm actually only paying 527.15 on the actual loan. Finally, I'm going to complete my ending balance. So again, this is going to be a very simple calculation. I'm going to say equals. We want the balance minus the reduction. Control, enter. And there we go. We have our base values. Now that we have these in there, we can simply select them all and we can just double click to copy down. And what I'm going to do immediately is control down arrow and take a look at that last row to make sure everything is working correctly. So I can see here that my final payment, payment 360, is going to occur on the 30th of September, 2051, which seems a very long way away. And take a look here. My ending balance is 50,000 pounds. Remember, that is what we set this PMT calculation to work out because we're going to make a balloon payment at the end of 50,000 to clear this loan. Now, if we go back up to the top, control up arrow. With this PMT calculation, the balloon payment was this here, the future value. Now, if we were paying this loan down to zero, we could just put zero in there or leave it blank. Now, whenever you're doing anything like this and you're dealing with money, it's always best to make sure that you're using the round function as well to round the numbers to the nearest penny. And what that means is when we round, you're probably going to find that the ending balance just here of 50,000 is going to be a few pennies out because we've used the round function. But that's not too much of a problem. We want to make sure that we are being extremely accurate when we're dealing with money. Now, in order to round this entire table, there are two formulas that I need to change. The first one is this PMT calculation. So what I'm going to do is go up to the formula bar and we're going to use the round function. The first argument is number. Well, the number is going to be generated by the PMT function. So let's click on the end, comma. How many digits do we want to round to? So we're going to round to two digits, close the bracket and hit enter. And our entire table is going to update. The second formula we need to round is the interest paid. So I'm going to go up to that formula bar and we're just going to round that as well. And we're And I need to copy this formula down by double clicking that auto fill handle to get everything to update. Now, the reason why we only need to round those two is because all of our other calculations feed off of either the PMT calculation or the interest paid. So everything is going to update as long as we've got these two rounded. So now if we go control down arrow to take a look at you can see that it is out by a few pennies. But as I said, when you're dealing with money, we need to make sure that we're being super accurate. So that is it. That is how you create yourselves a loan amortization schedule in Excel. It is also worth noting that within Excel, there is a template for loan amortization schedules. If we go up to file and down to new and I'm just going to search for a template. Let's type in loan amortization and hit enter. You can see in here we do have a few templates. So these are definitely worth checking out as well. But at least now you have the ability to create one from scratch and hopefully you understand how this all ties together and how everything works. That's it for now, guys. If you enjoyed this video, don't forget to give that like button a smash. Maybe consider subscribing to my channel and don't forget to hit that bell icon to turn on notifications so that you know when I release a new video. That's it for now. See you next time.