Managing Your Money in Microsoft Excel: Basic Financial Calculations
Date: Jul 29, 2005
Sample Chapter is provided courtesy of Que.
In this chapter
Calculating Loan Payments
Calculating Principal Payments
Working with Future Value
Using the Present Value Function
Calculating Interest Rate
As you learned in the previous chapter, Excel has a library of built-in functions for performing a wide variety of calculations. Several dozen of these functions deal with financial matters, and while most of them are highly specialized, there are several that deal with basic calculations the average Excel user might find useful. In this chapter I will explain what these functions do and how to use them. Along the way I will explain some basic financial terms and concepts.
Calculating Loan Payments
Few people can make it though life without taking out one or more loans. By extending payments over a period of time, a loan enables you to buy something now that otherwise you would have to save for over a long time. Big ticket items, such as a car or house, are perhaps the most common uses of loans.
When evaluating a loan, most people ask, "Can I afford the payments?" The overall price is not the issue, some people think, but whether the monthly payment will fit into the budget. The amount of the monthly payment depends on the three parameters of any loan:
The principal, or amount you are borrowing
The interest rate
The term, or length of the loan
Larger principal, higher interest rate, and shorter term all lead to higher payments.
You should be aware that judging a purchase solely on the monthly payments is not a good idea. Lower monthly payments might seem attractive now, but all other things being equal, they mean you will pay more over the long run for whatever you are buying. Keeping this caveat in mind, you can make decisions based on Excel’s loan-related calculations.
You can use Excel’s PMT function to calculate the payment on a loan. This function is applicable only to loans where the payment amount is fixed for the length of the loan. Most mortgages and auto loans fall into this category. It is not applicable for credit card payments.
The PMT function uses the following syntax:
The first three arguments are required. They are
rate is the interest rate for the loan.
nper is the term of the loan expressed as the number of payment periods.
prin is the principal, the amount you are borrowing.
When using PMT in a worksheet, it is essential that rate and nper use the same time units. What does this mean? Because most loans have monthly payments, you would have to express nper in months. For example, a five-year loan would have a term of 5 x 12 or 60 months. However, interest rate is always expressed by banks and loan companies as an annual rate. For the function to work correctly, rate must be per period, in this case the rate per month. This is easily obtained by dividing the annual interest rate by 12.
The last two arguments are optional (as indicated by the brackets in the formula):
fv is the future value of the loan, or the amount still owed when you have completed payments. Because loans are almost always paid off in full, you will use 0 for this argument or omit it, in which case Excel assumes 0.
type indicates when payments are made. Use a value if 1 if payments are made at the start of each period. Use a value of 0, or omit the argument, if the payment is made at the end of each period (the case for most loans).
In most situations you omit both of these optional arguments.
Let’s create a simple loan calculator using the PMT function. Start with a blank worksheet and then follow these steps:
Put the labels Amount of loan, Annual rate, Term in years, and Monthly payment in cells B2 through B5, in order.
Format cells C2 and C5 as currency with two decimal places.
Format cell C3 as percentage with two decimal places.
Enter the following formula in cell C5: =PMT(C3/12,C4*12,C2).
Note how in this formula we have done two things to make the result come out right:
Divided the annual interest rate (in cell C3) by 12 to get the rate per month.
Multiplied the yearly term (in cell C4) by 12 to get the term in months.
When you first enter the formula the cell displays #DIV/0!, which is Excel’s way of telling you an error occurred in the cell. An error occurs because the input cells are blank and the function cannot calculate. Go ahead and enter data in the three input cells and you’ll see a result displayed, as shown in Figure 3.1. Note that the payment amount is a negative value. This is in keeping with the way Excel handles cash flow, as was explained in Chapter 2, "Using Excel to Work with Money." Because a loan payment is money going out, it is expressed as a negative value.
)
Figure 3.1 Using the PMT function to calculate loan payments.
Calculating Principal Payments
When you make a payment on a loan, each payment is divided into two parts:
Part of the payment is for that month’s interest charge.
The remainder of the payment goes toward paying down the principal.
Each month you pay down the loan balance, or principal, by some amount. This means that the next month the interest charge will be less because the charge is calculated as the interest rate multiplied by the balance. The total payment amount is fixed, which means that each succeeding month less of your payment goes toward interest and more toward the principal. To calculate the amount that goes toward principal for a specific payment, use the PPMT function.
To see an example of this, please refer to Figure 3.2. This worksheet presents an amortization table for a $10,000 loan at 5% for 12 months. The three columns of data are
Principal—The amount of each payment that goes toward the loan balance. This is calculated with the PPMT function. You can see that this amount increases for subsequent payments.
Interest—The amount of each payment that goes toward interest. This is calculated with the IPMT function (covered in the next section). You can see that this amount decreases for subsequent payments.
Total—The total monthly payment, the sum of principal and interest. This amount stays constant for the entire term of the loan.
)
Figure 3.2 This amortization table shows how the principal payment increases while the interest payment decreases over the life of a loan.
The PPMT function uses the following syntax; you’ll note that most of the arguments are the same as for the PMT function:
The first four arguments are required. They are
rate is the interest rate for the loan.
per is the period for which you want the principal payment. This argument must be in the range 1 to nper.
nper is the term of the loan expressed as the number of payment periods.
prin is the principal, the amount you are borrowing.
As explained earlier for the PMT function, both rate and nper must use the same time unit (usually months). The last two arguments are optional (as indicated by the brackets in the formula):
fv is the future value of the loan, or the amount still owed when you have completed payments. Because loans are almost always paid off in full, you will use 0 for this argument or omit it, in which case Excel assumes 0.
type indicates when payments are made. Use a value of 1 if payments are made at the start of each period. Use a value of 0, or omit the argument, if the payment is made at the end of each period.
In most situations you omit both of these optional arguments.
To try out the PPMT function, you can add to the worksheet you created earlier for the PMT function (refer to Figure 3.1). Then follow these steps:
Put the labels For payment #, Principal, and Interest in cells B7 through B9, in order.
Put the following formula in cell C8: =PPMT(C3/12,C7,C4*12,C2).
Put the following formula in cell C9: =C5-C8.
Format cells C8 and C9 as currency with two decimal places.
A sample calculation is shown in Figure 3.3. You can see that for the specified loan, the first payment consists of $232.29 going toward principal and $73.33 going toward interest. Change the payment number to 60—the last payment for the loan— and you’ll see the amounts change to $304.22 and $1.39 respectively.
)
Figure 3.3 Using the PPMT function to calculate the principal component of loan payments.
Working with Future Value
The concept of future value is quite simple and is based on the fact that a given amount of money received today will be worth more at some time in the future. It’s easy to understand why this is true—money you have now can be invested and earn interest, hence its value increases.
Future value calculations are useful in a variety of situations. For example, you plan to invest $10,000 in a certificate of deposit at 4% for three years; how much will you have at the end of the three years? Another example is putting $50 a month in your daughter’s college fund. How much will you have when she goes to college in 12 years, assuming the rate of return is 5%? You use the FV function for future value calculations.
The FV function has the following syntax:
The first three arguments are required:
rate is the projected rate of return per period.
nper is the number of periods.
pmt is the payment per period.
The other two arguments are optional:
pv is the present value (the amount you are starting out with). If omitted, the function assumes 0.
type is 1 if the payments are made at the start of each period, 0 or omitted if payments are made at the end of each period.
The FV function is quite flexible. If you have a situation where you are starting with nothing and making regular payments, you will set pv to zero and enter a value for pmt. On the other hand, if you are starting with a lump sum and not making any payments, set pv to the initial value and enter 0 for pmt. You can have both an initial amount and regular payments, too, of course.
To try out the FV function, start with a new worksheet and then follow these steps:
Enter the labels Initial amount, Rate of return, Monthly payment, Number of months, and Future value in cells B2 through B6, in order.
Format cells C2, C4, and C6 as currency with two decimal places.
Format cell C3 as percentage with two decimal places.
Enter the following formula in cell C6: =FV(C3/12,C5,C4,C2).
The resulting worksheet is shown with some data entered in Figure 3.4. You can see that if you put $1,000 in an account paying 5% interest and add $40 every month, you’ll have $1,542.32 at the end of the year.
Please note that in keeping with Excel’s cash flow model, the initial amount and monthly payment are entered as negative values because this is money you are paying out. The future value is correctly calculated as a positive value because this is money you will receive.
)
Figure 3.4 Using the FV function to calculate the future value of an investment.
Using the Present Value Function
Present value is similar to future value in that it represents the value of an investment. However, it calculates the value of money you will receive in the future from the perspective of right now. A dollar today is always worth more than a dollar tomorrow because of the interest you can earn on today’s dollar. This might not make sense right off, so let’s look at a couple of examples.
For instance, suppose you know that you will need $10,000 in five years and you want to put a chunk of money away and let it earn interest to meet that goal. You know you can get a reliable 4% return. How much money do you have to put away now for it to grow to $10,000 in five years?
Here’s another example. Suppose your employer gives you a choice of how to take your annual bonus—either $2,300 in a lump sum now or $200 a month for the next 12 months. You could use a present value calculation to determine the present value of that $200 per month, compare it to the lump sum payment and make your decision accordingly. Simply multiplying $200 per month for a year gives you $2,400, but you cannot say that is better than $2,300 today due to interest you might earn over the next year on today’s $2,300.
As another example, you are looking to buy a new car and have decided that you can afford $290 per month payment for the next five years. Knowing the interest rate available on auto loans, you can use present value to determine the amount you will be able to borrow.
A more intuitive way to look at present value is this. Suppose you invest $X per month at Y% interest. The present value is the lump sum you would have to invest at the same rate to end up with the same total at the end of 12 months.
You use the PV function to calculate present value. The syntax is
The first three arguments are required:
rate is the interest rate per period.
nper is the number of periods.
pmt is the payment per period.
The other two arguments are optional:
fv is the future value, the balance at the end of the term, which is 0 in almost all situations. If omitted, the function assumes 0.
type is 1 if the payments are made at the start of each period, 0 or omitted if payments are made at the end of each period.
To try out the PV function, start with a blank worksheet and follow these steps:
Enter the labels Rate of return, Monthly payment, Number of months, and Present value in cells B2 through B5, in order.
Format cell C2 as percentage with two decimal places.
Format cells C3 and C5 as currency with two decimal places.
Enter the following formula in cell C5: =PV(C2/12,C4,C3).
As before, the monthly payment is entered as a negative value.
The worksheet shown in Figure 3.5 shows an example of evaluating the present value of $200 per month over 12 months assuming a 4% return. Returning to the example presented earlier, where you are offered this $200 per month or a lump sum of $2,300, you can see that the present value of these payments, assuming a 4% return, is $2,348.80. This tells you that the monthly payments are a better deal than the lump sum. If the interest rate were higher, you would see that the PV increases. Of course, if you need the money right away, that is another matter!
)
Figure 3.5 Using the PV function to calculate the present value of a series of payments.
Calculating Interest Rate
In the calculations that have been presented so far in this chapter, the interest rate was a variable that you either know or had to estimate. But what if you know the other parameters of a loan or other transaction but not the interest rate? Then you can calculate it using the RATE function.
Here’s an example. Suppose you want to take out a $10,000 personal loan from a friend or family member. They agree with the condition that you repay the loan at $300 per month for three years. You’d like to determine the effective interest rate for this deal—here’s where the RATE function comes in. Is this a good deal, or would you be better off taking a loan from the bank?
The RATE function has the following syntax:
The first three arguments are required:
nper is the number of payments for the loan.
pmt is the payment amount.
pv is the present value—the amount of the loan.
The other three arguments are optional:
fv is the future value of the loan, the balance when the payments are completed. Usually this will be 0, which is what Excel assumes if the argument is omitted.
type specifies when payments are made. Use 0 (the default if the argument is omitted) if the payments are made at the end of the period, 1 of they are made at the start of the period.
guess is your guess at the answer—your estimate of the interest rate. Because of the way RATE performs its calculations using a trial-and-error iteration, it requires a guess and then works from there to calculate the actual value. If you omit this argument, the value 10% (annual) is used.
As with all Excel financial functions, the period for the rate must match the other arguments. For example, if you enter arguments that include monthly payments, the RATE function’s result will be the monthly interest rate, and you must multiply this by 12 to get an annual rate.
Let’s try out the RATE function. Start with a blank worksheet and then follow these steps:
Enter the labels Principal, Monthly payments, Term in months, and Annual rate in cells B2 though B5, in order.
Format cells C2 and C3 as currency with two decimal places.
Format cell C5 as percentage with two decimal places.
Enter the following formula in cell C5: =12*RATE(C4,C3,C2).
Figure 3.6 shows this worksheet using the sample data from above. You can see that the effective rate on this loan is just a hair over 5%, which is quite reasonable.
)
Figure 3.6 Using the RATE function to calculate the interest rate on a loan.
This chapter has shown you how you can use Excel’s built-in functions to perform commonly needed financial calculations. With this information under your belt, you are ready for the next chapter that covers net worth and presents the first of the book’s Excel templates.