Calculate loan payments and investments with PMT and FV functions

- [Instructor] Excel has well over 50 financial functions. And if you're curious about what they are and what they mean, you can go to the Formulas tab in the ribbon and under Financial, here they are. Two of the more popular ones, and probably two that are likely to be used by those who are not necessarily in financial circles, are the PMT and FV functions. The PMT function is used to calculate payment on a loan. So suppose you're borrowing money, say, for a car or house. The example here in column B, we're borrowing $25,000. We want to pay this off over 48 months. The interest rate, 3.25%. And once you learn how to use these, and they're relatively straightforward, you can, of course, experiment with the numbers or check that other credit unions, see what their rates are, or the bank, so on. Equal PMT. The rate, here it is, 3.25%. Nearly always, this is going to be a monthly payment, so we divide it by 12. If it were a quarterly payment, we divide by 4, comma, and the number of months, the number of payment periods. 48 in this case, meaning four years. If this is the number four, referring to years, we'll simply multiply by 12, comma, and the amount we're borrowing. Notice as we put in commas here, the terms successively rate and in PER, the number of periods, and now PV, meaning present value appear. And there are people in financial circles who could probably explain that term better than I could. But in this case, what does it mean? The amount we're borrowing. $25,000. Enter. Now it is expressed as a negative. That's our monthly payment. Now if you would rather work with this more easily, it is easier to work with positive numbers. We can simply change this by double-clicking, put a minus right in front of the PMT, or right in front of the B4 in this case, either way, and the result will be positive. Of course, we're still paying the money. So that's the rate. And we can experiment, we can change that to be 60 months to see what the difference is or borrow more money, that idea. Maybe the interest rate is higher than this. We can change that. And here's a similar situation but with a much larger number. This might be for a house. 360 months. We could simply copy this formula to the right since we're using the same relative cells here. So if you borrow $200,000 and you need to pay if off over 30 years, and this is your interest rate, your monthly payment, principle and interest only, remember it's not taxes and insurance here just yet in our formula, there's the monthly payment. And the question sometimes comes up, if you're not familiar with the concept, what if this were a 15 year loan, that's 180 months? It certainly will not be double what we see here, but it's going to be substantially higher. And we see that. So there are situations where you can experiment with this, and you could even want to set up a grid. Let me undo this here, and consider the following. Maybe you're in a community where the housing prices are around this number, but some are higher. You might build your own little list here. And then right below this, I'm going to put it 205,000. And let's suppose you've got a little bit of flexibility. We highlight both of these, we'll simply drag down a bit. Some other possible borrowing amounts here. And maybe the interest rate is in the three to 4% range. Maybe a little bit higher. I'll start with 3.25%. Type it that way, and over here I'll put in three and 3/8, that's 3.375%. Highlight both of those, and drag across to create another series. And we could easily build a formula here, we'll use 360 here as a constant, and simply write a formula here. And we can either create here and then copy it, or simply highlight all these at once. Equal PMT. We do want to want to be getting our rate out of different cells in row two here. So we'll press the function key F4 multiple times to make sure the dollar sign is just in front of the two. We need to divide that by 12, comma, and now I put in the 360. Not the constant, we want that to be an -- address. Function key F4. And the amount we're borrowing, we'll put in a minus initially. And that's coming out of column E, so we'll click right here. We want the dollar sign in front of the E. Pressing the F4 key repeatedly will give the dollar sign in front of the E. And I'll press not Enter, but Control + Enter, and we've got a variety of answers here depending upon how much we're borrowing and the different interest rate. So occasionally, you're in that situation where you've got a little bit of flexibility, maybe you're looking at different lending institutions and what they pay, and you've got different numbers here that you can experiment with. And you can certainly change these easily too as well as these. But there's our basic calculation. And in the display here, we're not seeing the pennies, but we can certainly change that too. Now FV, sort of the reverse in one sense, means future value, and 2% is, at the time of this recording, is still too optimistic for most lending institutions, most banks. But let's say that every month, you want to put aside $250 for the next five years, and that's the interest rate. A quick multiplication of these gives us $15,000. But if we get interest on this, we will get more. So what do we use? FV, left parentheses, future value. Here's the rate, here too, we will divide by 12, comma, term, number of periods right here, it's 60 months. And so B17, comma, and the amount we're setting aside. And like in the previous formula, we'll put in a minus first, 250. So instead of $15,000, if we could somehow put this away every month for the next 60 months, that $250 will grow to $15,761. I'll double-click on here just to give us a quick look at this again. So two different functions here using similar techniques, that allow us to come up with answers, both for the payback on a loan and the amount of money saved over a period here with constant contributions into the payment.