Explore BrainMass
Share

Explore BrainMass

    Retirement and Savings

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    Retirement and Savings
    1. 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.00)" Note: You don't need Excel to answer this question.

    a. How much money is being invested each month?
    b. What is the APR?
    c. For how many years will monthly payments be made?
    d. How much money total will have been deposited into the account at the end of the deposits period?

    2. John makes $2,800 per month and has an opportunity to invest $150 per month at an APR of 4.5% in a 401K plan through work. He plans to retire in 30 years. Find the following:

    a. Use Excel to determine how much his investment will be worth in 30 years.
    i. Write the Excel formula that gives its future value.
    ii. Using excel, what is the future value?
    b. How much money will he deposit into the 401K over the 30 years?
    c. How much total interest will he earn on his 401K over the 30 years?

    3. Susie makes $1,900 per month and has an opportunity to save $100 per month at an APR of 3.25% in a 401K plan through work. She plans to retire in 40 years.
    a. If Susie's company offered a 50% matching, use Excel to determine how much her investment be worth in 40 years.
    i. Write the Excel formula that gives its future value
    ii. Write the future value the formula gives.
    b. How much would the company contribute to Susie's 401K over the 40 years?
    c. How much more is in the account when Susie retires than how much she deposited?

    4. Pearl has an opportunity to save through her company's 401K retirement plan. The APR is 4.75%. Develop a strategy for Pearl by determining how much she should contribute monthly to the 401K so she will have at least 700,000 but not more than $725,000 after 32 years of monthly saving. (Hint: You will need to use excel by testing out different numbers to make this true. Which monthly rate is needed to get the value of her retirement within range?)

    © BrainMass Inc. brainmass.com October 10, 2019, 8:33 am ad1c9bdddf
    https://brainmass.com/business/the-time-value-of-money/retirement-savings-642149

    Attachments

    Solution Preview

    1a
    $100 is invested each month. This is the last number in the formula provided.

    b
    The APR is 6%. This is 0.06 converted to percent.

    c
    The payments will be made for 6 years. This is the number 6 in 12*6.

    d
    The total deposited is $8,640.89. Please see the Excel spreadsheet for the computation.

    2a
    The investment will be ...

    Solution Summary

    Computations for the investment examples are shown in the Excel file. The Word file contains the final answers.

    $2.19