Google Sheets has several built in functions for working with annuities. To use these functions, we’ll start with a standard Sheets worksheet.
This worksheet contains the variables used throughout Chapter 5. Values given in a problem will be entered in column B. Values calculated by Sheets will be entered in column C. We will also assume that amounts paid out are negative and amounts received are positive.
We’ll modify the worksheet shown above. This will allow us to use Sheets to calculate the different amounts in the annuity formula,
This is done using two functions in Sheets, the FV (future value) function and the PV (present value) function. Annuities have a regular payment into or out of the account. If the payment is made at the end of the compounding period, the annuity is called an ordinary annuity. Payments are made at the beginning of the compounding period for an annuity due. In Sheets, amounts that you pay out are considered negative numbers and amount you receive are positive amounts.
Find the Future Value of the Annuity
An investor deposits $500 in a simple annuity at the end of each six-month payment period. This annuity earns 10% per year, compounded semiannually.
a. Find the future value if payments are made for three years.
Solution Since the investor is paying $500 into the annuity, the payment must be entered as a negative number in Sheets.
1. Start from the basic worksheet and enter the values shown below.
2. Click in cell C6. Type =FV( in the cell. As soon as you type the parentheses, Sheets recognizes what you are trying to do.
3. Notice that the word rate is highlighted. This indicates that you need to either type in the rate or the cell reference where the rate is located. The rate for the command is actually the interest rate per period. The annual interest rate is in cell B3 and the number of periods per year is in cell B7. We need to get the interest rate per period by typing B3/B7. You can also click in cell B3, type a /, and then click the cursor in cell B7. Now type a comma.
4. The number of periods is in cell B2. Type B2 or click the cursor in that cell. To continue to the next input, type a comma.
5. The payment is located in cell B4. The value is negative since the money is paid out. Type B4 or choose cell B4 followed by a comma.
6. Since we deposit nothing into the account initially, the present value is zero. Enter B5 or select cell B5 followed by a comma and a parentheses. Press enter on the keyboard to calculate the future value.
The future value of the annuity is $3400.96. There is an optional argument for the FV function. This argument indicates whether the annuity is an ordinary annuity (use 0 for the argument) or an annuity due (use 1 for the argument).
Ordinary Annuity: FV(B3/B7,B2,B4,B5,0)
Annuity Due: FV(B3/B7,B2,B4,B5,1)
If the last argument is not supplied, the annuity is assumed to be an ordinary annuity.
b. Find the future value if payments are made for 30 years.
Solution Thirty years of semiannual payments corresponds to n = 60. We need to make this change in the FV function, and solve for future value again. Change the value in cell B2 to 60.
The future value is now $176,791.86.