Chapter 15: Creating Formulas for Financial Applications

On the CD

This workbook is available on the companion CD-ROM. The file name is irregular payments.xlsx.

Investment Calculations

Investment calculations involve calculating interest on fixed-rate investments, such as bank savings accounts, CDs, and annuities. You can make these interest calculations for investments that consist of a single deposit or multiple deposits.

On the CD

The companion CD-ROM contains a workbook with all the interest calculation examples in this section. The file is named investment calculations.xlsx.

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

Interest = Principal * Rate * Term

For example, suppose that you deposit $1,000 into a bank CD that pays a 3 percent 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 (.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 percent simple annual interest earns $15.00 when the CD matures. In this case, the annual interest rate multiplies by 6/12.

343

Part II: Working with Formulas and Functions

Figure 15.10 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.

FIGURE 15.10

This worksheet calculates simple interest payments.

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 percent 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 will be 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 15.11).

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).

344

Chapter 15: Creating Formulas for Financial Applications

FIGURE 15.11

Using a series of formulas to calculate compound interest.

You can use the FV (Future Value) function to calculate the final investment amount without using a series of formulas. Figure 15.12 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.

FIGURE 15.12

Using a single formula to calculate compound interest.

345

Part II: Working with Formulas and Functions

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 is

=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 percent 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 a competitor’s account that offers daily compounding. Figure 15.13 shows a calculation with daily compounding, using a $5,000 investment (compare this with Figure 15.12). As you can see, the difference is very 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%.

Calculating interest with continuous compounding

The term continuous compounding refers to interest that is accumulated continuously. In other words, the investment has an infinite number of compounding periods per year. The following formula calculates the future value of a $5,000 investment at 4.25 percent compounded continuously for three years:

=5000*EXP(4.25%*3)

The formula returns $5,679.92, which is an additional $0.04 compared with daily compounding.

346

Chapter 15: Creating Formulas for Financial Applications

FIGURE 15.13

Calculating interest by using daily compounding.

Note

You can calculate compound interest without using the FV function. The general formula to calculate compound interest is

Principal * (1 + periodic rate) ^ number of periods

For example, consider a five-year, $5,000 investment that earns an annual interest rate of 4 percent, compounded monthly. The formula to calculate the future value of this investment is

=5000*(1+4%/12)^(12*5)

The Rule of 72

Need to make an investment decision, but don’t have a computer handy? You can use the Rule of 72 to determine the number of years required to double your money at a particular interest rate, using annual compounding. Just divide 72 by the interest rate. For example, consider a $10,000 investment at 4 percent interest. How many years will it take to turn that 10 grand into 20 grand? Take 72, divide it by 4, and you get 18 years. What if you can get a 5 percent interest rate? If so, you can double your money in a little over 14 years.

How accurate is the Rule of 72? The table that follows shows Rule of 72 estimated values versus the actual values for various interest rates. As you can see, this simple rule is remarkably accurate. However, for interest rates that exceed 30 percent, the accuracy drops off considerably.

continued

347

Part II: Working with Formulas and Functions

continued

The Rule of 72 also works in reverse. For example, if you want to double your money in six years, divide 6 into 72; you’ll discover that you need to find an investment that pays an annual interest rate of about 12 percent. Good luck.

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 known as 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 percent annual interest compounded monthly. The following formula calculates the future value of your series of deposits:

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

348

Chapter 15: Creating Formulas for Financial Applications

This formula returns $2,432.75, which represents the total of your deposits ($2,400) 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 15.14 shows a worksheet set up to calculate annuities. Table 15.4 describes the contents of this sheet.

FIGURE 15.14

This worksheet contains formulas to calculate annuities.

On the CD

The workbook shown in Figure 15.14 is available on the companion CD-ROM. The file is named annuity calculator.xlsx.

TABLE 15.4

continued

349