Solve step by step using excel: Upon starting your new job after college, you've been confronted with selectingthe investments for your 401(k) retirement plan. You have four choices for investing your money: A moneymarket fund that has historically returned about 1% per year. A long-term bond fund that has earned anaverage annual return of 4.5%. A conservative common-stock fund that has earned 6.5% per year. Anaggressive common-stock fund that has earned 9.0% per year. If you were to contribute $5,500 per year for thenext 35 years, how much would you accumulate in each of the above funds? Now, change your worksheet sothat it allows for less than annual investments (monthly, biweekly, etc.). The annual investment will be the same,but it will be made in smaller, more frequent, amounts. Set up a scenario analysis that shows your accumulatedvalue in each fund if you were to invest quarterly, monthly, biweekly, and weekly. Create a scenario summary ofyour results. What relationship do you notice between the frequency of investment and the future value? Createa column chart of the results that more clearly shows the outcome from more frequently investing.

Step-by-Step using Excel to Analyze 401(k) Investment Options1. Setting Up the Spreadsheet:Create a…

Answered: Solve step by step using excel: Upon…

Entrepreneurial Finance

6th Edition

ISBN:9781337635653

Author:Leach

Publisher:Leach

Chapter1: Introduction To Finance For Entrepreneurs

Section: Chapter Questions

Problem 4EP

See similar textbooks

Similar questions

Upon starting you new job after graduation, you’ve been confronted with selecting investment for your 401K retirement plan. You have four choices for investing your money: • A money market fund which has historically returned about 5% per year. • A long-term bond which has earned an average annual rate of return of 8% • A conservative common stock fund which has earned 10% per year. • An aggressive common stock fund which has historically earned 14% per year. a. If you were to contribute $3,600 per year (at the end of each year) for the next 35 years, how much would you accumulate in each of the above funds? (Use FV function) b. Recalculate part a in a separate excel worksheet (same excel file), but now change your worksheet appropriately so that it allows for non-annual investments (monthly, weekly, quarterly etc.). The annual investment is still $3,600. For example, when you invest quarterly, you invest $900 (=$3,600/4) four times a year. What is the FV in each fund? c. In the…
Suppose you have recently accepted a new job and are setting up your retirement allocations within the firm's 401k plan. The plan options contain two risky mutual funds, A and B. You may assume that all fees are the same across the two funds. Fund A has an expected return of 6% and the standard deviation of returns is 12%. Fund B has an expected return of 7% and the standard deviation of returns is 15%. The correlation between returns on the two funds is 0. The plan also contains a Treasury fund which provides a risk-free return of 3%. A Suppose you can only invest in one of the two risky mutual funds (A or B), but can combine it with the Treasury fund in any proportions you wish. Which of the two mutual funds would you choose and why B Maintaining the restrictions on your investment choices from the last question, what is the best expected return you could achieve if you set up a portfolio with a standard deviation of returns equal to 10%? (Please express your answer in percentage…
Michey Lawson is considering investing some money that he inherited. The following payoff table gives the profits that would be realized during the next year for each of the three investment alternatives Mickey is considering: STATE OF NATURE DECISION ALTERNATIVE GOOD ECONOMY POOR ECONOMY Stock market 75,000 -20,000 Bonds 55,000 30,000 CDs 45,000 20,000 Probability 0.3 0.7 Show complete work in your answers to parts a-e: a) What kind of decision-making environment is this?
Monique Gonzales just graduated and was hired by a new cybersecurity firm in Colorado. She needs to set up her retirement plan portfolio. Monique has completed the following payoff table for different investment options and estimated the potential profits that could be realized in one month. Monique can use the Hurwicz Criterion strategy to make her decision.Payoff Table State of Nature Alternatives Good Economy Fair Economy Poor Economy Mutual Fund 800800 650650 320320 Stock Market 5,5005,500 4,7004,700 3,1003,100 CDs 1,7001,700 870870 670670 Bonds 550550 320320 185185 Step 2 of 2 : What is Monique’s potential payoff based on the the Hurwicz Criterion strategy and an α=0.45α=0.45?
Monique Gonzales just graduated and was hired by a new cybersecurity firm in Colorado. She needs to set up her retirement plan portfolio, Monique has completed the following payoff table for different investment options and estimated the potential profits that could be realized in one month. Monique can use the Hurwicz Criterion strategy to make her decision. Alternatives Answer Mutual Fund Stock Market CDs ➤ Bonds Payoff Table Good Economy 900 5,500 1,800 550 Step 1 of 2: What should Monique's decision be using the Hurwicz Criterion strategy and an à = 0.55? O Mutual Fund OOO State of Nature Fair Economy Poor Economy O Stock Market O CDs 650 4,900 880 440 O Bonds 440 3,100 630 165 Tables Keypad Keyboard Shortcuts
Use Excel to solve the following problem. Assume that you are 39 years old planning for your future retirement at age 65. You think that you will be comfortable living on the proceeds from a $1,000,000 401K Retirement Account.a. If your investments grow at an average rate of 6% annually how much must you invest monthly to achieve your projected retirement fund total by the time you retire in 26 years?b. Assuming that when you do retire, you will re-direct your $1,000,000 investment portfolio into less volatile and more secure mutual funds. You expect that, invested in these sources, your portfolio will securely earn 4.5% annually. Based on your assumptions and the normal life expectancy of an American male or female (I determined this to be 81 years old), without consuming any of your principal, how much money will you have on a monthly basis to support your life?(I have worked this problem out on my own, I just want to be sure I applied the formulas and concepts correctly.)
Your financial advisor from Bluerock recommends you to invest in one of the plan. You want to compare and verify which of the following is the best investment option. Investment Plan A: Deposit $125,000 at the beginning and obtain $175,318.97 after 5 years.Investment Plan B: Deposit $243,000 at the beginning and obtain $319,771.42 after 7 years.Investment Plan C: Deposit $314,000 at the beginning and obtain $530,496.39 after 9 years. 1. Compute interest rate for all 3 plans. Which investment plan provides you the highest rate? 2. Your advisor updated the investment plan information yesterday. While the interest rate did not change for all 3 plans, future values of each investment are 210382.76, 415702.85, 742694.95, respectively. What would be the investment period for each plan? 3. Your advisor thinks it would be a good investment if you make an investment portfolio using all 3 investment plans suggested. He recommends investing in all 3 plans at year 0 and reinvest the money to…
What is the excel function and formula for this question? Off-The-Books Investment Firm, LLC, has offered you an investment it says will return to you $20,000 in 2 years. To get in, you'll need to make a $10,000 deposit to their receivables account and promise not to tell anyone about it. What is the annual return on this investment?
Now consider your financial objective is to save $500,000 for preparing your retirement, assuming 30 years from now. If you invest your RRSP savings in a mutual fund which can realize an average return of 10% per year. To achieve your goal, how much do you need to save at the end of each year over the 30-year period? a. 4,039.26 b. 3,039.62 c. 2,985.54 d. 10,988.32 What is the FV of $100 deposited today into an account with an APR 12.6%, compounded semiannually for 10 years? a. 1478.96 b. 3460.06 c. 327.63 d. 339.36 A car dealer offers payment of $525.32 per months for 60 months on a $30,000 car after making a $5000 down payment. What's the loan's APR? a. 10.4798% b. 9.5224% c. 1.9609% d. 0.7935%
You open an account where you deposit $500 today. Further, you deposit $800 at the beginning of next year, withdraw $250 at the beginning of year two and deposit $450 at the beginning of year 3. The return for year 1 is 6%, for year 2 it is -8%, for year 3 it is 4.5% and for year 4 it is -2%. What is your dollar-weighted or money-weighted return (in percent) for the four-year period? Answer to two decimals. O -1.45 -6.95 -11.92
Assume that you are 39 years old planning for your future retirement at age 65. You think that you will be comfortable living on the proceeds from a $1,000,000 401K Retirement Account.Assuming that when you do retire, you will re-direct your $1,000,000 investment portfolio into less volatile and more secure mutual funds. You expect that, invested in these sources, your portfolio will securely earn 4.5% annually. Based on your assumptions and the normal life expectancy of an American male or female (respective to yourself), without consuming any of your principal, how much money will you have on a monthly basis to support your life?
You unexpectedly earn $1,200. Instead of blowing through the money, you decide to invest it in a mutual fund which earns 4.0% compounded annually. You would like to know how long you will need to keep the money invested for your investment to grow to $66,000. What would you need to enter in Excel to help you answer this question? =NPER(4.0%, -1200, 66000) =NPER(0.04, 0, -1200, 66000) =NPV(0.04, -1200, 66000) =NPER(4.0%, 0, 1200, 66000)

SEE MORE QUESTIONS

Recommended textbooks for you

Entrepreneurial Finance

Finance

ISBN:

9781337635653

Author:

Leach

Publisher:

Cengage

Entrepreneurial Finance

Finance

ISBN:

9781337635653

Author:

Leach

Publisher:

Cengage

SEE MORE TEXTBOOKS