Skip to main content

Worksheet 2.2.2 Investment Lab
A4US

Sal, Cal and Val are triplets. Sal gets a paper route at age 14 and decides to invest $2000 at the beginning of each year in a mutual fund which averages a 10% annual return. Sal only makes deposits for 5 years and then leaves the balance to compound until retirement at age 65. Cal is impressed by Sal’s bank account, gets a job in college at age 19 and dutifully makes deposits into the same mutual fund for 8 years and then leaves the balance to compound until retirement at age 65. Val partied a lot growing up, but then gets a good job at the age of 27 and diligently makes deposits all the way to retirement at the age of 65. Your job in this project is to recreate the picture of the spreadsheet below in your own spreadsheet and answer the following questions:

Spreadsheet Template 8 

1.

(6 points) Before trying to recreate the spreadsheet below let's check that you understand where the numbers came from. Consider the cell C5, which shows $4,620. Write the mathematical equation whose answer is $4620. Then write the same equation replacing numbers with cell names whenever possible.
Hint.
Remember to replace the rate “10%” with I3, or even better, $I$3. The dollar signs will let you copy formulas better.
Solution.

3 points for 4620 = (2000+2200)*(1 + 0.10) or something algebraically equivalent.

3 points for 4620 = (B5+C4)(1+$I$3) or something algebraically equivalent. It's okay if they replace 4620 with C5.

2.

(20 points) Consider the picture above. Recreate the spreadshee which tracks the growth of each person’s investment and extend it all the way down to age 65. You must take into account both the deposit and the growth rate of 10% each year. Remember to use “$I$3” instead of “10%” in your formulas.

If you are new to spreadsheets, feel free to check out this video Google Sheets Formula Tutorial 9 . For this lab you only need the first 4-5 minutes of this video.

Solution.
Formulas should look like this, at least up to algebra. It's okay if they have no formulas when they are calculating interest on zero deposits such as D4-D8.

3.

(12 points) For each person, calculate the total amount they deposited and the total interest they collected.

Solution.
Sal invested $10,000 and gained $1,174,599.83 in interest. Cal invested $16,000.00 and gained $1,019,159.56 in interest. Val invested $78,000.00 and gained $805,185.11 in interest. 2 points per number

4.

(4 points) Change the annual interest rate to different numbers. Determine the interest rate where Sal and Val end with the same amount of money (rounded to the nearest tenth of a percentage point).

Solution.
The answer is 7.6% or 7.7%, which is 0.076 or 0.077 as decimals.

5.

(8 points) Using what you've learned in this lab, write a note to your friend explaining why it is financially wise to begin investing money as early as possible.

Solution.
Answers will vary.

In order to get full credit for Exercise 2 you need to do two things.

  1. Show all your formulas by clicking View -> Show -> Formulas

  2. Turn your spreadsheet into a PDF by clicking File -> Download -> PDF (.pdf)

  3. This PDF that you just downloaded is the file you should submit with this lab, along with this worksheet. You will lose points if you don't follow directions.