Investing money
An understanding of the difference between simple and compound interest is vital when making investment decisions. We must help MT Pockets choose wisely, my young apprentice!
Simple Interest (or Flat Rate Interest) is given by I = Prn, where P is the principal, r is the interest rate expressed as a decimal, and n is the number of periods in the term.
The compounded value of any investment can be calculated by using:
· The compound interest formula A = P(1 + r)n, where A is the final amount (compounded value), P is the principal, r is the interest rate per compounding period expressed as a decimal, and n is the number of compounding periods in the term
· A table of compounded values and the formula CV = PV x CVIF, where CV is the compounded value, PV is present value, CVIF is the compound value interest factor
In the activities that follow you will calculate the compounded value of an investment. You will also help MT Pockets to decide which of the three investments will give her the best return.
Go to investing.xls (Excel 98Kb)
Compound interest table: Questions
Prior to the widespread use of calculators, financial tables such as this partially completed table were used to calculate compounded values. This table lists the value of (1+r)n to 3 decimal places for different values of r and n and can be used to calculate the compounded value of any investment. (The blank cells relate to questions 6 and 10)
1. What does $1 amount to if it is invested at 5% pa for 6 years, with the interest compounded annually?
2. What is the minimum rate of interest that I would need to earn if I wish to double my investment of $1 in 10 years?
3. Use the table to calculate the compounded value of $450 invested at 8% pa for 4 years, with the interest compounded annually.
4. Study the formula for cell H10. Show by mathematical calculation how this entry is found.
5. Study the formulae for cells C6 to K6. Using the same format, enter a formula in cell L6 to calculate the entry for this cell. What does $1 amount to if it is invested at 10% pa for 1 year, with the interest compounded annually?
6. Click on cell L6 and hold the mouse button down. Drag the mouse pointer down until the cells L6 to L15 are highlighted. Now go to the Edit menu and click on the Fill Down command. [If you have opened this spreadsheet with Excel 2007, click on cell L6 then drag the fill handle down until the cells L6 to L15 are highlighted.] What does $1 amount to if it is invested at 10% pa for 9 years, with the interest compounded annually?
7. What does $100 amount to if it is invested at 8% pa for 3 years, with interest compounded half-yearly?
8. Calculate the amount of money that must be invested in an account earning 4% pa, with interest compounded quarterly, if I wish to have $2000 in 5 years.
9. How long would it take $2000 to grow to $2500 when invested at 6%pa, with interest compounded half-yearly?
10. Highlight cells C6 to L6. Hold the mouse button down then drag the pointer down to cell L25. The remaining white cells should now be highlighted. Now go to the Edit menu and click on the Fill Down command. [If you have opened this spreadsheet with Excel 2007, highlight the cells C6 to L6 then drag the fill handle down to cell L25.] What does $1 amount to if it is invested at 9% pa for 16 years, with the interest compounded annually?
Compound interest table: Answers
1. $1.34
2. 8% per annum
3. 1.36x$450=$612
4. (1+r)n=(1+0.06)5=1.338 (to 3 decimal places)
5. ‘=(1+$L$5)^B6’; $1.10
6. $2.36 (to the nearest cent).
7. $126.50
8. Interest rate per period = 1%
Interest periods = 5 x 4 = 20
CVIF = 1.220
PV = $2000 ÷ 1.220
= $1639.34
9. Interest rate per period = 3%
PV = $2000
CV = $2500
CVIF = $2500 ÷ $2000
= 1.25
The first entry in the 3% column of the CVIF table that is greater than 1.25 is 1.267. Therefore, the minimum number of interest periods is eight.
\ It will take 4 years.
10. $3.97
Which investment?: Questions
Go to the ‘Which investment’ spreadsheet. MT Pockets has $1000 to invest. Our task is to help MT to decide which of three investments offers the greatest return.
1. Consider two investments with identical annual interest rates, one paying simple interest and the other paying compound interest with the interest compounded annually. Which investment will always pay the most interest? Explain your answer.
2. For an investment where compound interest is paid, what affect does increasing the number of compounding periods have on the total interest earned?
3. Study the formula for cell E22. With reference to the compound interest formula (given on the introduction page of this unit), explain the meaning of the variables ‘C2’, ‘E8’ and ‘B22*D8’.
4. MT Pockets has $1000 to invest for 10 years. Three investment options are: (1) 6.5% pa simple interest, (2) 5.9% pa compounded annually or (3) 5.85% pa compounded quarterly. Enter this data into the spreadsheet and decide which of the three investments offers the greatest return.
a. How much interest is earned in the first year of the investment? [Key Question]
b. What will the investment be worth at the end of ten years? (Assume that MT does not make any withdrawals).
5. Using the same initial data as in question (4), what is the minimum simple interest rate that would need to be earned from Investment (1) so that it was the most attractive investment option? Give your answer to the nearest whole number.
6. Using the same initial data as in question (4), what is the minimum number of compounding periods per year that would need to be offered in Investment (2) so that it was the most attractive investment option?
7. $500 is invested for 10 years at 12% pa, compounded annually. In what year will the initial investment double in size?
8. If an account pays 6% pa compounded half-yearly, what is the minimum amount that needs to be invested to be worth $600 at the end of 5 years? Give your answer to the nearest $10.
9. Determine in what year an investment of $5000 earning 4% pa simple interest is worth less than an investment of $5000 earning 3.75% pa compounded monthly.
10. Assuming an average inflation rate of 4% pa, what would MT Pockets expect to pay in 10 years time for a house that now costs $350000? MT plans to purchase such a property in 10 years time and will require a loan to make the purchase. If the bank requires a deposit of 10% of the purchase price, how much will MT need to save for the deposit?
Which investment?: Answers
1 The investment paying the compound interest will always pay more interest. This is so because with compound interest, the balance plus the interest becomes the new balance on which the interest is calculated in the next period.
2 The interest earned increases since you earn ‘interest on your interest’.
3 ‘C2’ represents the principal (or present value), ‘E8’ represents interest rate per compounding period and ‘B22*D8’ represents the number of compounding periods.
4 Investment 3 offers the best return.
a. In the first year the interest earned will be $59.80
b. After 10 years the initial investment will grow to $1787.40
5 The minimum interest rate would need to be 8% pa.
6 Two compounding periods. That is, the interest would need to be compounded every six months.
7 In the 7th year.
8 $450.
9 After 4 years.
10 MT Pockets would expect to pay $518000 (to the nearest thousand dollars). MT would require a deposit of $51800.