Questions 1-5. If a cell in an Excel spreadsheet uses the following formula to determine the future value of an investment: "=FV(0.06/12,12*6,-100)"
1. How much money is being invested each month?
2. What is the APR? (enter a percentage)
3. For how many years will monthly payments be made?
4. How much money will have been deposited into the account overall?
5. How much money will be in the account after the investment period? (round to the hundredths place)
Questions 6-9. John has an opportunity to save $150 per month at an APR of 4.5% in a 401K plan through work. He plans to retire in 30 years.
6. Use Excel's FV function to determine how much his investment will be worth in 30 years. (round to the hundredths place)
7. What formula did you type into Excel?
8. How much money will he deposit into the 401K over the 30 years?
9. How much total interest will he earn on his 401K over the 30 years? (round to the hundredths place)
Quantitative Reasoning Retirement and Savings Plan
Questions 10-13. Julie is able to invest $200 per month in a 401(k) with a predicted growth rate of 2.75%. Julie's company offers a 50% match.
10. How much does Julie's company contribute every month?
11. Use Excel's FV function to find the account balance after 40 years. (round to the hundredths place)
12. How much was deposited over 40 years by Julie? By the company?
13. How much did Julie earn in interest? (round to the hundredths place)
Questions 14-16. Samantha plans to save $100 per month for the next 30 years to have a nest egg for retirement. Her sister, Sally, has decided to wait 15 years and start saving $200 per month for half the time figuring she will do at least as well as her sister with this strategy. They both invest in an account with an APR of 2.25%.
14. Use Excel's FV function to determine the balance of Samantha's account at the end of the 30 years. (round to the hundredths place)
15. Use Excel's FV function to determine the balance of Sally's account at the end of 30 years (remember that Sally did not contribute during the first 15 years and she only contributes for 15 years). (round to the hundredths place)
16. How much did Sally "lose" by starting 15 years later? (round to the hundredths place)
$100 is invested each month. This is the last number in the formula provided.
The APR is 6%. This is 0.06 converted to percent.
The payments will be made for 6 years. This is the number 6 in 12*6.
$100*12*6 = $7200
The total deposited is $8,640.89. Please see the Excel spreadsheet for the computation.
Computations for the investment examples are shown in the Excel file. The Word file contains the final answers.