Jan 24 2009
06: Financial Functions
Most people won’t use most of the financial functions, but there are five I’ll cover here, because they have direct applications to any American household. They are:
- =PMT()
- =IPMT()
- =PPMT()
- =CUMPRINC()
- =CUMIPMT()
With Excel, you can shop around for the best loan before you sign. The PMT() function takes the interest rate, term of the loan, and the principal, and returns the amount of the payment.
There are two things to remember with these financial functions: They are designed for businesses to use, so they will return negative values. If you want a positive value, as you most likely will, you put a minus in front of the function, right after the equals sign. Also, given interest rates are annual, and the term is given in years. So we have to divide the interest by 12 and multiply the term by 12 to get the monthly payment (4 and 4 for the quarterly payment). Having said that, the function looks like this:
=-PMT(RATE/12,TERM*12,PRINCIPAL)
As our first illustration, say we’re taking out a loan for $25,000 to put an addition on the house. The term of the loan is 20 years, and the interest rate is 6.5%. Our spreadsheet might look something like this:
In the B3 cell for the amount of the payment, we entered:
=-PMT(B3/12,B2*12,B1)
And Excel returns $186.39 as the payment amount.
My last semester (summer session) teaching was right before we came out here to find a house, so I had been looking at the realtor sites online. There were a couple of houses I picked, one closer to campus than the other, but the further house was cheaper. When we covered this function in class, we calculated the amortization tables for both, then added in the cost of gas for both. It turned out that the house further from campus would have ultimately been more expensive.
Amortization tables
Building one of these is just a little tricky, and it requires that we learn two functions similar to the PMT function, IPMT() and PPMT(). The IPMT() function returns the amount paid toward the interest for a specific payment, and the PPMT(), the amount paid toward the pricipal, again for a specific payment.
First, we’ll put in the labels (Payment Number, Beginning Balance, Principal Payment, Interest Payment, Ending Balance), then under Payment Number, we’ll put in the payment numbers. Since payments are monthly, and the term is 20 years, we need 1-240 (use the autofill handle). I hid the rows from 15-244 so you wouldn’t have to scroll down and down and down).
The beginning balance for the first payment is the principal of the loan, so in that cell, we will enter:
=B1
The PPMT() and IPMT() functions are almost exactly the same as the PMT() function, except that they return values for a specified payment, so the payment number has to be one of the arguments. The syntax is:
=-PPMT(RATE/12,PAY_NUM,TERM*12,PRINCIPAL)
Also, we’re building a table, and we’re going to copy these functions down, so we have to use some of that absolute cell addressing. Into C7, we enter:
=-PPMT($B$3/12,A7,$B$2*12,$B$1)
The interest rate, the term, and the principal all have to be absolute cell addresses; the payment number has to change when we copy it down, so leave it alone.
In cell D7, calculate the payment toward the interest:
=-IPMT($B$3/12,A7,$B$2*12,$B$1)
What, shocked that most of that payment is going to pay off the interest and not the principal? If that shocked you, then this may, too. We calculate the ending balance by subtracting the amount paid toward the principal from the beginning balance:
=B7-C7
Now, the beginning balance for the second payment is the ending balace for the first (and will be until we pay off the loan), so in the B8 cell:
=E7
Now, select C7:E7, and use the autofill handle to pull the calculations down to the second payment row. Select B8:E8, and autofill it down to the last payment:
Note that if your final ending balance isn’t 0, there’s a serious problem!
The CUMPRINC() and CUMIPMT() functions return how much you have paid toward the pricipal and interest, respectively, over a specified period of time. The syntax looks like this (identical for both, except for the function name):
=-CUMPRINC(RATE/12,TERM*12,PRINCIPAL,BEGIN_NUM,END_NUM,TYPE)
TYPE is either 0 (payment at the end of the period) or 1 (payment at the beginning of the period). To find out how much we paid toward the principal in the third year, we enter:
=-CUMPRINC(B3/12,B2*12,B1,25,36,0)
And we see we have paid $717.53 toward the principal in the third year, out of the $2,236.72 in payments for that year.
How much did we pay toward the interest out of that same $2,236.72 in the same year? The syntax is the same; only the function name is different:
=-CUMIPMT(B3/12,B2*12,B1,25,36,0)
So we paid $1,519.19 toward the interest in the third year. See? Practical applications for just about any household.
