To find the rate for a sinking fund, you needed to use a graph or the TVM Solver on a TI graphing calculator to find the interest rate on a sinking fund. Let’s look at the most basic way of doing this by graphing.
Problem Find the interest rate needed for a sinking fund to accumulate 25,600 dollars in five years with monthly payments (made at the end of the month) of 400 dollars.
Solution Since this problem indicates that payments are being made, we need to use the ordinary annuity formula. For a sinking fund, the future value is known, 25600. We also know the payment, 400, and the number of periods, 60:
This is incredibly difficult to solve for i algebraically. Instead, graph each side of the equation and look for a point of intersection. On a graphing calculator we would type y1 = 400*((1+x)^60-1)/x and y2 = 25600 in the window [0, 0.004] by [23500, 27500].
The toughest part is coming up with the window on the graph. Since the x on your calculator represents the interest rate per period, it needs to small like 0 to 0.01 initially. The vertical window is much easier to determine since the second equation is y = 25600, a horizontal line. Simply pick a horizontal window that spans this value.
The annual interest rate is 12 · 0.00217 ≈ 0.02604 or approximately 2.6%.
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).