Using Financial Functions in Microsoft Excel 2013

Microsoft Excel 2013 contains Functions that are useful in personal financial contexts pertaining to savings and loans, such as the PMT Function. This PMT Function calculates the periodic payments required to amortize a loan with a particular interest rate and a set number of payment periods.

There are five Arguments associated with the PMT Function, namely Rate, Nper, Pv, Fv, and Type. Rate corresponds to the interest rate per pay period for a loan, Nper to the total number of payments, and Pv to the Present Value. Fv and Type are Optional Arguments, whereas the first three are Mandatory

Watch the free video here, transcripts for the entire video follow:

Video transcripts:

Welcome back to our course on Excel 2013 Advanced. In this section we’re going to start to look at financial functions in Excel 2013. Many of the financial functions are geared towards professional use and we’ll be looking at some examples of those later on. But in this section and the next one I’m going to look at those financial functions that are generally used in a personal context in relation to things like savings and loans.

Now as a first example of a personal financial function I’m going to look at the PMT function which is one of the most commonly used financial functions in Excel and basically it computes the periodic payment that you need to amortize a loan over a specified number of time periods. Now this function requires a number of arguments and then it generates a result which is the payment that needs to be made. And what I’m going to do is to show you a typical way of laying out a use of this function. Although to some extent we can use the function in a much more simple way, in terms of the sort of analysis we’re going to look at later on in the course I believe it’s a good idea to get used to laying out this kind of use of a function in a pretty structured kind of way and once you’re used to doing it you can do it very quickly.

So first of all I filled in some headings here: Rate, Nper, Pv, Fv, Type. They’ll make a little bit more sense in a moment. And what I’m going to do is to calculate the payment that’s needed using the PMT function to amortize the payoff, all or part of a loan. Now I’m going to put down here another heading which is Payment and I’m going to type in here the formula that includes the function I’m going to use. So I type Equals, the function is PMT, so P, there it is, PMT. And note having selected it and got as far as the name of the function and the left parentheses I can see that there are five arguments and those five argument names are the five headings, the five labels that I’ve put in here.

Now I’d like you note a couple of things about that. First of all if you look at the list of arguments with the PMT function the last two, Fv and Type, are optional. They’re in square braces. And Rate, the first one, the one that’s highlighted at the moment there is the one that I will be entering if I type a value in now. Now I’m not going to type in literal values. I’m going to use cell references. But I’m also going to use that very useful Function Arguments Dialogue that we talked about earlier on. Now in anticipation of using this kind of function later on I want to make this spreadsheet explaining quite a bit of detail exactly what I’m doing here. So using abbreviations like Nper and Pv and Fv isn’t really going to help somebody to understand what I’ve done. If they just opened this spreadsheet and they look at it and they say, What the heck is Pv and Fv? You may or may not know what those arguments mean. So the thing that’s going to help to do a lot of this is that Function Arguments Dialogue.

So let’s click on the Function Arguments Dialogue and as you may recall from the earlier section we get an explanation of what each of these arguments is within the dialogue. Rate is the interest rate per period for the loan. Now this is actually a very important description because when you’re dealing with many functions you may think that when you’re dealing with something like, for example, interest rate it would automatically be an annual interest rate but that’s generally not the way that Excel works. Excel talks about periods. And you can pretty much work in any period you like provided you’re consistent throughout the particular use of a function. So if I wanted to work out monthly payments the easiest way is to express everything else in monthly terms. So when I’m putting in the interest rate even though I might say have an interest rate of 4% per annum what I really want to do is to specify an interest rater per month. So the value that I enter here I might need to divide by 12 to get a rate per month, but more of that in a moment. I’ll come back to that point in just a moment. Let’s look at these other arguments.

Nper is the total number of payments, which is the same as the total number of periods, Nper. Pv is the Present Value, the total amount that a series of future payments is worth now. And Fv, the Future Value or a cash balance you want to obtain after the last payment is made. Now Fv is the first one of the optional arguments and if we leave Fv out then it’s assumed that it’s zero. So the assumption is that we’re going to pay off the total amount of this loan of the Pv. One of the things to note here within the Function Arguments Dialogue is that the arguments, the mandatory ones are bold, the optional ones are not bold.

So now let’s look at the last of these arguments, that’s Type, and Type is a logical value. Payment at the beginning of the period is one, payment at the end of the period is zero or omitted. The significance of this is that if you’re making a payment at the beginning of a period, say at the beginning of a month because you’re making the payment early in the period you won’t be liable for the interest on that period. Whereas if you make the payment at the end of the period, at the end of the month for example, then you will be liable for interest on the period to which the payment refers.

Need to Learn Excel? Get a Free Excel 2013 Course – Click Here

So that’s our five arguments. The last two of them are optional ones. And bear in mind that if you’ve got two optional arguments as you have here if you need to specify say the last one, the fifth one, Type in our case, and not the fourth one Fv, then the way you would actually indicate that when using the function is you’d have to successive commas. So you’d have two commas with nothing between them. But what we’re going to do is to pick up the values for the arguments for our use of the function from the worksheet. So what I’m going to do now is to basically fill out the names in column B where I put things like Rate and Nper and Pv and so on I’m going to type in the correct names and it’ll make it a lot more easy for somebody to understand what we’re doing with this worksheet. So I’ll do that and join me again in a moment.

So what I’ve done here is to lay this out a little bit more neatly and what I’m going to do is to now put in that payment function. So it’s Equals, PMT, click on Function. Now notice that apart from setting up the five cells that I’m putting the arguments in here, C2 to C6, I’ve put a little explanation in the D column for each of those five items. So somebody using my little worksheet is going to understand what’s got to go in, in each case. Now the Rate now is the C2. So while I’ve got Rate selected in function arguments I click on C2. The Number of Payments, that’s that one. That’s going to be the contents of C3. Pv, Present Value, that’s that one. Fv, Future Value, that’s that one. And then Type, that’s C6. Now the only one of those that needs any separate treatment is the Rate one because the rate here to be consistent in terms of dealing with months we need to divide the annual interest rate by 12. So we literally just type in Divided by 12. And there we are. We have all of our function arguments setup correctly. I haven’t put in any numeric values in there yet. I’m going to come to that in just a moment. But if we click on OK although we get obviously no sensible answer you can see how the PMT function looks in C8. It’s Equals PMT, C2 divided by 12, C3, C4, C5, C6. So let’s now see what happens when we put some numbers in.

Okay. My loan is going to be $5,000 over five years at 5% interest rate. I’m going to pay off the full amount of the loan. So the rate goes in as 5%. The number of payments in months, five years so it’s 60 months. The principle of the loan is going to be $5,000. And as soon as I get that far I get an answer basically. Now the answer is in red in brackets. That means it’s minus. That indicates that it’s a payment. It’s a negative amount of money. So if I just under $95 a month I can borrow $5,000 over five years at 5% interest.

Now one of the things to be very careful of when you’re dealing with financial functions is the use of plus and minus amounts, credits and debits. At the moment with this particular loan we’re paying off the loan principle of $5,000, coming up with a cash balance of zero. If I wasn’t required to pay off the full loan, so I was only going to pay off say $4,000 out of $5,000 for some reason, the future value would not be $1,000. It would not be amount that I was in credit as such. It would be -$1,000 because basically I would still be $1,000 in debits, still owing $1,000. So if I put in as my future value -$1,000, let me just enter that, then my monthly payment to achieve a situation where I still owe $1,000 would be less than it is now. So if I enter that it’s a payment of $79.65 but then at the end of that I still owe $1,000. If on the other hand as part of this loan I also wanted to finish up with cash in hand at the end then I would need to put that amount as +$1,000. Again enter that and the monthly payment in order to finish up with cash in hand would be more than the original amount, $109.06. But for the moment I’m going to assume it’s a straightforward loan, that the cash balance is zero, go back to the figure that I started with, and now let’s look finally at Type.

Now as I said before the Type really reflects whether you need to pay interest on the payment period to which the payment refers. So if I want to change from the default zero or omitted or payment at the end of the month, if I specify payment at the start of the month would the payments per month go up or down? Now you should realize that the payments will actually go down if I specify a one there because I’m paying at the start of the period and therefore I won’t be liable to the interest. So I’ll tick that and the payment goes down by about 40 cents. It goes down to $93.96.

So there we are. That’s an example of one of the financial functions in Excel 2013 that can be used for personal financial calculations. In the next section we’re going to look at a couple of other examples of those types of functions so please join me for that.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!