Joan and Jane

Original Contributor - Jay Bennet

Problem: Jane and Joan were twins. They both went to work at age 22 with identical jobs, and at the end of each year they recieved identical bonuses of $2,000.00. But there was one difference:

  • Joan: As a young woman, Joan was conservative and was concerned about her future. Each year she invested her $2000 bonuses in a savings program earning 8% interest compounded annually. Joan decided at age 32 to have some fun in life and she began spending her $2000 bonuses on vacations in the Bahammas. This continued until she was 65

  • Jane: Jane, on the other hand, believed that as a young woman she should take the opportunities to enjoy life and not be too concerned about saving for the future -- she had many years to put money into savings. For the first 10 years she spent her $2000 bonuses on vacations in the Bahamas. At age 32, she decided she should start saving for her future and from that time on she invested her $2000 bonuses in a savings program paying 8% compounded annually. This continued until she was 65.

    At their retirement party (when they were both 65), the conversation got around to retirement plans and savings programs. Each sister was proud of her savings activities, terms and accumulations.

    Part 1: Building a Spreadsheet

    Using the following format, build a spreadsheet to keep track of the twins' age, Joan's Annual Deposit, Joan's Total, Jane's Annual Deposit and Jane's Total, so that we can compare their savings each year.
      A B C D E F
    1 Age Joan's Annual Deposit Joan's Total Savings Jane's Annual Deposit Jane's Total Savings Rate
    2  22          

    Fill in the age and deposit columns. Remember that Joan's deposit ends after she has put money in for 10 years, and Jane's just starts then.

  • How do we fill in the age column without typing in all ages between 22 and 65?

    To build the formula for Joan's total and Jane's total, consider how simple annual interest works:
    total new money = old money*(1 + rate) + new money deposited

  • What are the formulas you use in Excel for C3 and E3?

  • How do we define the "rate" term?

  • Show me your final figures before moving on to Part 2.

    Part 2: Experimenting with the interest rate

    The 8% interest rate should be considered an average over time that Joan and Jane are saving their money. What if this is not a good estimate? Even in recent past, interest rates have gone as high as 15% and as low as 4% for certain kinds of stocks, annuities and savings plans.

    Experiment with the interest rate (if you build your formulas right, this should be a matter of changing one number!) to see what happens at different rates. Does the interest rate make a difference in your answer?

  • Fill in the following table:

    Rate Joan's Earnings Jane's Earnings Winner







    Part 3: Writing up your results

    Write up a lab report (be sure to follow the writing checklist to 22 year old twins Jeff and Jeremy, who are deciding whether to start saving for retirement now, or to use their bonuses to travel. Include one of your excel sheets, this sheet containing the answers to the questions in Part 1, and Part 2 filled in with numbers.

    Extra Credit: What rate yields the same earnings for Joan and Jane? Explain how you obtained your answer.