Doing a basic loan amortization model - Microsoft Excel教程

- [Presenter] One of the most basic types of financial models is an amortization schedule. And this illustrates what you're typically trying to do in a Excel model. We're trying to go through and use a series of steps to answer a question that we're interested in. For example, in this case, we're starting with a loan of $250,000 and we're trying to figure out how that loan balance changes over time. So let's go through and build this model out. I'm in the 02_01_Begin Excel file. We've got our $250,000 loan, a 4% interest rate, and a term of 180 months. So to get started, I'm going to go ahead and figure out what my payment is and we'll zoom in just a little bit so we can read this more easily. So I'm going to put in equals PMT, open parentheses, and I've got my interest rate here. Now my interest rate is 4%, but that's an annual interest rate. I need a monthly interest rate, so I'm going to divide that by 12. Next, I'm going to take my number of periods, that's 180, and this is already in months, so I don't have to do anything to it. And then I've got my present value. What's the amount that I'm borrowing? Well, that's $250,000. What's the future value? How much do we owe on this loan after it's been paid off? Well, probably zero. And then we have the final term, which is are the payments made at the beginning of the period or the end of the period? In a typical amortization schedule, those are going to be made at the beginning of the period. And I'm going to go through and I'm going to anchor my cells so I can drag and drop these things down. And to do that, I'm just going to put in a dollar sign in front of the values that I don't want to change. So in this case, I'm most concerned about the row. So when I drag this down, it'll show up and it won't change the row reference. Okay, so now, I've got my payment. Now what's my new balance on this loan after making that payment? Well, it's going to be the payment less, that beginning balance. Okay? But that doesn't completely answer kind of what's happening to the loan, in particular, I haven't taken into account interest rates. So I need to go through and I need to figure out what my interest is going to be. So that interest is 4% divided by 12 months times my beginning balance. And I'll put in some parentheses here. And again, I'm going to anchor my cells, but just the column, 'cause I want the road to change as we go down. Now the issue that I have here is my interest. Oh look, that interest is positive. I need to put in a negative sign in front of it, right? Okay, so I do that. Then this lets me figure out what my principle is. What's my principle? Well, it's the payment minus then interest amount. So I pay off each month $1,015 and 89 cents in principle, less the interest payment as well, giving me a total payment of 1849.22. Now to figure out my new balance, I could take out that payment, but then I'd have to add back the interest charges. So that gives me my correct new balance. Alternatively, I can, and this is a little bit simpler, just take out that principle. And again, I need that to be a positive sign since the principle is negative. And there we are. And then my beginning balance in the next period is just what the new balance was at the end of that prior period. Now if I've done this correctly, just take this right down to the next row and see if it updates. Nope, looks like we missed something. What did we miss? Well, we've got to go through and update our values here based on the interest rate and our beginning balance. So what's changed? What have we missed? Well, as we can see, we didn't properly anchor our interest rate row. So now, I'm going to go through and update this and voila, it's now correct. So you've got to be very careful. Now, I did this to try to illustrate the importance of kind of being careful and checking yourself, but if you get that pound value sign come up, that tells you you've missed something in building that Excel row out. All right? And that's why I like to start by just dragging down one row rather than go all the way down. Now from here, drag down a few more rows. Looks good. I'm going to go all the way to the bottom by clicking that fill cell handle. And we get to the bottom. And I've got one last value here, which is sum. So in this case, I'm going to go through and I'm just going to sum up the set of payments. Okay, well that first one wouldn't necessarily make a lot of sense, right? But having done this for one, I can carry this across all of the others and then I'm going to take that out. And column B, where it doesn't necessarily make very much sense. And again, we'll see, it doesn't make a lot of sense here in this last value. So what do we find? Well on this loan, at the end of the period, we have a zero balance. We've paid $331,010 and 35 cents of that $82,026 and 23 cents is interest. And 248,984.11 is our principle. But as you see, we need to go through, we need to correct this. So I've got to go through and correct and make sure I'm going C2 all the way down to C181. And there we are. And this lets me go through and now, I've built out my complete amortization model and I can figure out what the total amount of principle and interest paid is, and I can check and make sure that aligns with kind of what I would expect initially, but that's how we go through and build out a simple and straightforward amortization table.