Worksheet functions for calculating loan information

This section describes six commonly used financial functions: PMT, PPMT, IPMT, RATE, NPER, and PV. For information about the arguments used in these functions, see Table 11.1. In the sections that follow, you’ll notice that many of the functions also serve as arguments of other functions. This is quite logical, as all of these terms are interconnected with each other in a variety of ways.

PMT(rate,nper,pv,fv,type)

=PMT(6&/12,48,5000)

PPMT(rate,per,nper,pv,fv,type)

=PPMT(6&/12,1,48,5000)

IPMT(rate,per,nper,pv,fv,type)

=IPMT(6&/12,1,48,5000)

RATE(nper,pmt,pv,fv,type,guess)

=RATE(48,-117.43,5000)*12

NPER(rate,pmt,pv,fv,type)

=NPER(6&/12,-117.43,5000)

PV(rate,nper,pmt,fv,type)

=PV(6&/12,48,-117.43)

A loan calculation example

Figure 11.1 shows a worksheet set up to calculate the periodic payment amount for a loan.

The loan amount is in cell B1, and the annual interest rate is in cell B2. Cell B3 contains the payment period expressed in months. For example, if cell B3 is 1, the payment is due monthly. If cell B3 is 3, the payment is due every three months, or quarterly. Cell B4 contains the number of periods of the loan. The example shown in this figure calculates the payment for a $25,000 loan at 6.25% annual interest with monthly payments for 36 months. The formula in cell B6 follows:

=PMT(B2*(B3/12),B4,-B1)

Notice that the first argument is an expression that calculates the periodic interest rate by using the annual interest rate and the payment period. Therefore, if payments are made quarterly on a three-year loan, the payment period is 3, the number of periods is 12, and the periodic interest rate is calculated as the annual interest rate multiplied by three-twelfths.

The worksheet in Figure 11.1 is set up to calculate the principal and interest amount for a particular payment period. Cell B9 contains the payment period used by the formulas in B10:B11. (The payment period must be less than or equal to the value in cell B4.)

The formula in cell B10, shown here, calculates the amount of the payment that goes toward principal for the payment period in cell B9:

=PPMT(B2*(B3/12),B9,B4,-B1)

The following formula, in cell B11, calculates the amount of the payment that goes toward interest for the payment period in cell B9:

=IPMT(B2*(B3/12),B9,B4,-B1)

The sum of B10 and B11 is equal to the total loan payment calculated in cell B6. However, the relative proportion of principal and interest amounts varies with the payment period. (An increasingly larger proportion of the payment is applied toward principal as the loan progresses.) Figure 11.2 shows the principal and interest portions graphically.

Credit card payments

Do you ever wonder how long it takes to pay off a credit card balance if you make the minimum payment amount each month? Figure 11.3 shows a worksheet set up to make this type of calculation.

Range B1:B5 stores input values. In this example, the credit card has a balance of $1,000, and the lender charges a 21.25% annual percentage rate (APR). The minimum payment is 2.00% (typical of many credit card lenders). Therefore, the minimum payment amount for this example is $20. You can enter a different payment amount in cell B5, but it must be large enough to pay off the loan. For example, you may choose to pay $50 per month to pay off the balance more quickly. However, paying $10 per month isn’t sufficient, and the formulas return an error.

Range B7:B9 holds formulas that perform various calculations. The formula in cell B7, which follows, calculates the number of months required to pay off the balance:

=NPER(B2/12,B5,-B1,0)

The formula in B8 calculates the total amount you will pay. This formula follows:

=B7*B5

The formula in cell B9 calculates the total interest paid:

=B8-B1

In this example, it would take about 123 months (more than 10 years) to pay off the credit card balance if the borrower made only the minimum monthly payment. The total interest paid on the $1,000 loan would be $1,468.42. This calculation assumes, of course, that no additional charges are made on the account. This example may help explain why you receive so many credit card solicitations in the mail.

Figure 11.4 shows some additional calculations for the credit card example. For example, if you want to pay off the credit card in 12 months, you need to make monthly payments of $93.23. (This amount results in total payments of $1,118.81 with total interest of $118.81.) The formula in B13 follows:

=PMT($B$2/12,A13,-$B$1)

Creating a loan amortization schedule

A loan amortization schedule is a table of values that shows various types of information for each payment period of a loan. Figure 11.5 shows a worksheet that uses formulas to calculate an amortization schedule.

The loan parameters are entered into C1:C4, and the formulas beginning in row 9 use these values for the calculations. Table 11.2 shows the formulas in row 9 of the schedule. These formulas were copied down to row 488. Therefore, the worksheet can calculate amortization schedules for a loan with as many as 480 payment periods (40 years of monthly payments).

Calculating a loan with irregular payments

So far, the loan calculation examples in this chapter have involved loans with regular periodic payments. In some cases, loan payback is irregular. For example, you may loan some money to a friend without a formal agreement as to how he’ll pay the money back. You still collect interest on the loan, so you need a way to perform the calculations based on the actual payment dates.

Figure 11.6 shows a worksheet set up to keep track of such a loan. The annual interest rate for the loan is stored in cell B1 (named APR). The original loan amount and loan date are stored in row 5. Notice that the loan amount is entered as a negative value in cell B5. Formulas, beginning in row 6, track the irregular loan payments and perform calculations.

Column B stores the payment amount made on the date in column C. Notice that the payments are not made on a regular basis. Also, notice that in two cases (row 11 and row 24), the payment amount is negative. These entries represent additional borrowed money added to the loan balance. Formulas in columns D and E calculate the amount of the payment credited toward interest and principal. Columns F and G keep a running tally of the cumulative payments and interest amounts. Formulas in column H compute the new loan balance after each payment.

Table 11.3 lists and describes the formulas in row 6. Note that each formula uses an IF function to determine whether the payment date in column C is missing. If so, the formula returns an empty string, so no data appears in the cell.

Note that the formula in cell D6 assumes that the year has 365 days. A more precise formula uses the YEARFRAC function, which calculates the fraction of a year when a leap year is involved:

=IF(C6<>"",YEARFRAC(C6,C5,1)*H5*APR,"")

Future value of a single deposit

Many investments consist of a single deposit that earns interest over the term of the investment. This section describes calculations for simple interest and compound interest.

Calculating simple interest

Simple interest refers to the fact that interest payments are not compounded. The basic formula for computing interest is this:

Interest=Principal*Rate*Term

For example, suppose that you deposit $1,000 into a bank CD that pays a 3% simple annual interest rate. After one year, the CD matures, and you withdraw your money. The bank adds $30, and you walk away with $1,030. In this case, the interest earned is calculated by multiplying the principal ($1,000) by the interest rate (0.03) by the term (one year).

If the investment term is less than one year, the simple interest rate is adjusted accordingly, based on the term. For example, $1,000 invested in a six-month CD that pays 3% simple annual interest earns $15.00 when the CD matures. In this case, the annual interest rate multiplies by six-twelfths.

Figure 11.7 shows a worksheet set up to make simple interest calculations. The formula in cell B7, shown here, calculates the interest due at the end of the term:

=B3*B4*B5

The formula in B8 simply adds the interest to the original investment amount.

Calculating compound interest

Most fixed-term investments pay interest by using some type of compound interest calculation. Compound interest refers to interest credited to the investment balance, and the investment then earns interest on the interest.

For example, suppose that you deposit $1,000 into a bank CD that pays 3% annual interest rate, compounded monthly. Each month, the interest is calculated on the balance, and that amount is credited to your account. The next month’s interest calculation is based on a higher amount because it also includes the previous month’s interest payment. One way to calculate the final investment amount involves a series of formulas (see Figure 11.8).

Column B contains formulas to calculate the interest for one month. For example, the formula in B10 is

=C9*($B$5*(1/12))

The formulas in column C simply add the monthly interest amount to the balance. For example, the formula in C10 is

=C9+B10

At the end of the 12-month term, the CD balance is $1,030.42. In other words, monthly compounding results in an additional $0.42 (compared with simple interest).

You can use the FV (future value) function to calculate the final investment amount without using a series of formulas. Figure 11.9 shows a worksheet set up to calculate compound interest. Cell B6 is an input cell that holds the number of compounding periods per year. For monthly compounding, the value in B6 would be 12. For quarterly compounding, the value would be 4. For daily compounding, the value would be 365. Cell B7 holds the term of the investment expressed in years.

Cell B9 contains the following formula that calculates the periodic interest rate. This value is the interest rate used for each compounding period:

=B5*(1/B6)

The formula in cell B10 uses the FV function to calculate the value of the investment at the end of the term. The formula follows:

=FV(B9,B6*B7,,-B4)

The first argument for the FV function is the periodic interest rate, which is calculated in cell B9. The second argument represents the total number of compounding periods. The third argument (pmt) is omitted, and the fourth argument is the original investment amount (expressed as a negative value).

The total interest is calculated with a simple formula in cell B11:

=B10-B4

Another formula, in cell B13, calculates the annual yield on the investment:

=(B11/B4)/B7

For example, suppose that you deposit $5,000 into a three-year CD with a 4.25% annual interest rate compounded quarterly. In this case, the investment has four compounding periods per year, so you enter 4 into cell B6. The term is three years, so you enter 3 into cell B7. The formula in B10 returns $5,676.11.

Perhaps you want to see how this rate stacks up against another account that offers daily compounding. Figure 11.10 shows a calculation with daily compounding using a $5,000 investment (compare this with Figure 11.9). As you can see, the difference is small ($679.88 versus $676.11). Over a period of three years, the account with daily compounding earns a total of $3.77 more interest. In terms of annual yield, quarterly compounding earns 4.51%, and daily compounding earns 4.53%.

=5000*EXP(4.25&*3)

Present value of a series of payments

The example in this section computes the present value of a series of future receipts, sometimes called an annuity.

A man gets lucky and wins the $1,000,000 jackpot in the state lottery. Lottery officials offer a choice:

• Receive the $1 million as 20 annual payments of $50,000
• In lieu of the $1 million, receive an immediate lump sum of $500,000

Ignoring tax implications, which is the better offer? In other words, what’s the present value of 20 years of annual $50,000 payments? And is the present value greater than the single lump sum payment of $500,000 (which has a present value of $500,000)?

The answer depends on making a prediction: if Mr. Lucky invests the money, what interest rate can be expected?

Assuming an expected 6% return on investment, calculate the present value of 20 annual $50,000 payments using this formula:

=PV(6&,20,50000,0,1)

The result is –$573,496.

What’s the decision? The lump sum amount would need to exceed $573,496 to make it a better deal for the lottery winner. The lump sum payout probably isn’t negotiable, so (based on an interest rate assumption of 6%) the 20 annual payments is a better deal for the lottery winner.

The PV calculation is sensitive to the interest rate assumption, which is often unknown. For example, if the lottery winner assumed an interest rate of 8% in the calculation, the present value of the 20 payments would be $490,907. Under this scenario, the lump sum would be a better choice.

In the extreme case, an interest rate of 0%, the PV calculates to $1,000,000. The lottery organization would have to pay the winner a lump sum of more than $1 million to make it more valuable than the payments spread over 20 years—not likely.

Future value of a series of deposits

Now consider another type of investment, one in which you make a regular series of deposits into an account. This type of investment is an annuity.

The worksheet functions discussed in the “Loan Calculations” section earlier in this chapter also apply to annuities, but you need to use the perspective of a lender, not a borrower. A simple example of this type of investment is a holiday club savings program offered by some banking institutions. A fixed amount is deducted from each of your paychecks and deposited into an interest-earning account. At the end of the year, you withdraw the money (with accumulated interest) to use for holiday expenses.

Suppose that you deposit $200 at the beginning of each month (for 12 months) into an account that pays 2.5% annual interest compounded monthly. The following formula calculates the future value of your series of deposits:

=FV(2.5&/12,12,-200,,1)

This formula returns $2,432.75, which represents the total of your deposits ($2,400.00) plus the interest ($32.75). The last argument for the FV function is 1, which means that you make payments at the beginning of the month. Figure 11.11 shows a worksheet set up to calculate annuities. Table 11.4 describes the contents of this sheet.