In many investment problems, you need to combine annuities and compound interest in the same problem. Here are two examples.
- Handout: Annuity Combination Example
- Handout: Annuity Combination Example 2
Category: Chapter 5
How Do You Find the Annual Interest Rate From the Compound Interest Formula?
Suppose 5000 dollars is deposited in an account that earns compound interest that is done annually. If there is 7000 dollars in the account after 2 years, what is the annual interest rate?
The easiest way to approach this problem is to use the compound interest formula,
This formula applies when interest is earned on an annual basis and the interest is earned once a year.
Let’s look at the quantities in the problem statement:
5000 dollars is deposited in an account > P = 5000
If there is 7000 dollars in the account after 2 years > A = 7000 and n = 2
Putting these values into the formula above gives us
We need to find the annual interest rate r. Since the r is hidden in the parentheses, we start by isolating the parentheses.
To get at the r, we need to remove the square on the parentheses.
Using a calculator to do the square root, we get r ≈ 0.183 or 18.3%.
Note that instead of taking the square root to get rid of the square, we could raise both sides to the 1/2 power:
Then we would be able to solve for r,
and get the same value for r since square rooting and raising to the 1.2 power yield the same result.
Now what if the interest is earned over six years instead of two years? Instead of a square on the parentheses we now have a sixth power. To solve for r in this equation, we follow similar steps as before.
The root can be computed in a graphing calculator using the MATH button, by raising to the 1/6 power (^(1/6)), or put into WolframAlpha:
Either method gives r ≈ 0.577 or 5.77%. Notice that the annual interest is lower when it is earned over a longer period of time.
How Do You Find The Future Value Of An Annuity in Google Sheets?
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.
How Do You Make An Amortization Table?
Let’s look at a loan problem to see where the payment and amortization table comes from.
Suppose you want to borrow $10,000 for an automobile. Navy Federal Credit Union offers a loan at an annual rate of 1.79% amortized over 12 months.
a. What are the payments?
To answer this, we need to put the numbers into the appropriate formula and solve for R:
b. Find an amortization table.
With the payment in hand, we can start filling out the table. Keep in mind that a payment for a credit card is calculated differently. In that case, the minimum payment is the interest plus some percentage of the new balance. In the screens below, you may click on the image for a larger version.
The first few steps consist of filling out Payment 0 and 1:
The interest is calculated with the monthly interest rate of the outstanding balance from the previous period. The amount applied to the balance is the difference between the payment and the interest.
Subsequent rows of the table are calculated in the same way until the final payment.
In the last payment, the principal applied to the balance must be equal to the outstanding balance in Payment 11.
Once that amount applied to the balance is in the 12th payment, then the interest may be calculated, The last payment is then found by adding the interest and the amount applied to the balance.
How Do You Find Compound Interest Future Value In Google Sheets?
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.
Continue reading “How Do You Find Compound Interest Future Value In Google Sheets?”