Microsoft Office Tutorials and References

In Depth Information

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 annu-

ities, but you need to use the perspective of a lender, not a borrower. A simple example of this type of invest-

ment 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 depos-

its:

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

The workbook shown in Figure 11-11 is available at this book's website. The file is

named annuity calculator.xlsx.

Table 11-4: The Annuity Calculator Worksheet

Cell

Formula

Description

B4

None (input cell)

Initial investment (can be 0)

B5

None (input cell)

The amount deposited on a regular basis

B6

None (input cell)

The number of deposits made in 12 months

TRUE if you make deposits at the beginning of period;

otherwise, FALSE

B7

None (input cell)

B10
None (input cell)

The length of the investment, in years (can be fractional)

B13
None (input cell)

The annual interest rate