Spreadsheets have several built in functions for working with compound interest and annuities. To use these functions, we’ll start with a standard sheet.
This worksheet contains the variables used throughout Chapter 8. These variables correspond to these letter used in the text.
- Number of periods is n
- Annual interest rate is r
- Payment is R
- Present value is P
- Future value is A
- Periods per year is m
Values given in a problem will be entered in column B. Values calculated by the spreadsheet will be entered in column C. We will also assume that amounts paid out are negative and amounts received are positive.
In the different sections of Chapter 8, we’ll modify the worksheet shown above. This will allow us to use Sheets to calculate the different amounts in the compound interest formula,
A = P (1 + r/m) n
This is done using two functions in the spreadsheet, the FV (future value) function and the PV (present value) function. These functions are very powerful and allow you to compute amounts involving compound interest as well as amounts involving annuities. In an annuity, regular payments are made into or out of an account. In compound interest problems, no regular payments other than interest are made into the account. For this reason, our worksheet above contains an option for including a payment. In Section 8.4, we’ll set this amount equal to zero. In later sections, we will consider problems that include payments.
A customer deposits $5000 in an account that earns 1% annual interest compounded monthly. If the customer makes no further deposits or withdrawals from the account, how much will be in the account in five years?
Solution In a compound interest problem, no regular payments are made into the account. Since the customer deposits $5000 into the account (pays out), the present value is entered as a negative number.
1. Start by creating the worksheet you see below in the spreadsheet.
2. Enter the values given in the problem. Make sure you enter the present value as -5000. Leave the future value blank. You may also need to format B3 as a percent. We will use the spreadsheet to calculate the future value in cell C6.
3. Click in cell C6. Type =FV( in the cell. As soon as you type the parentheses, Sheets recognizes what you are trying to do.
4. 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.
5. 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.
6. For this compound interest problem, no payments are being made into or out of the account (other than interest). Type B4 or choose cell B4 followed by a comma.
7. Since we deposit 5000 into the account initially, this value is the present value. 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 is $5256.25. Note the FV command and its arguments in the function bar above the spreadsheet.