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.)

Question

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.)

Expert Answer

Want to see the step-by-step answer?

Check out a sample Q&A here.

Want to see this answer and more?

Experts are waiting 24/7 to provide step-by-step solutions in as fast as 30 minutes!*

*Response times may vary by subject and question complexity. Median response time is 34 minutes for paid subscribers and may be longer for promotional offers.
Tagged in
Business
Finance

Related Finance Q&A

Find answers to questions asked by students like you.

Q: Heidi owns 680 shares of Boyd Enterprises, which is priced at $65.44 per share. The company plans a ...

A: Number of stocks prior to stock split, Npre = 680Proposed stock split: 6 for 5 that is a shareholder...

Q: What is the estimate of the marginal cost of the Phase 4 hospital servicesassuming, as given in the ...

A: Marginal costs are the incremental costs that will be incurred on hospital 4 services. The cost data...

Q: How do you call a strategy that separately examines capital market conditions and the investor's obj...

A: The strategy is called as “Integrated Asset Allocation Strategy”.

Q: Suppose you observed the following situation:   Security Beta Expected Return   Cooley, Inc. 1.6 19%...

A: Calculation of Risk Free Rate:The risk free rate can be calculated using the CAPM. Follow the below ...

Q: a. Find the required down payment.   b. Find the amount of the mortgage.   c. How much must be pai...

A: Hi, I’m answering the first 3 parts of the question as it consists of multiple questions. If you nee...

Q: A bond with annual coupon rate of 5.10% and price of $1,090 just yesterday paid a coupon. A total of...

A: Calculation of Yield to Maturity of the Bond:

Q: Grill Works and More has 8 percent preferred stock outstanding that is currently selling for $49 a s...

A: Calculation of Cost of Preferred Stock:

Q: Until August 1971, industrialized countries around the world maintained a fixed exchange rate of the...

A: Answer 1:The correct answer is “Forward exchange rate”.Justification:  A forward exchange rate is wh...

Q: A one-year call option has a strike price of 70, expires in three months, and has a price of $7.34. ...

A: Recall the call pur parity equation in case of a European option:C + PV (K) = S + PWhere C = Price o...

Q: Let C be the price of a call option to purchase a security whose present price is S.  Explain why C ...

A: Options are derivative contracts and the amount that we pay to buy an option is called premium.

Q: Mexican Motor's market cap is 200 billion pesos. Next year's cash flow is 8.6 billion pesos. Securit...

A: Mexican Motor's market cap, E = 200 billion pesos.Next year's cash flow, C =  8.6 billion pesos.Secu...

Q: The stock of Red's Hardware closed at $56.35 per share today. Tomorrow morning, the stock goes ex-di...

A: Cum dividend price, P0 = $ 56.35Dividend, D = $ 1.55Tax rate on dividend, T = 10%Hence, dividend pai...

Q: can I have this explained? After 20 years, 100 shares of stock originally purchased for $1000 was so...

A: The formula to calculate the yield on investment:Yield on investment can be determined using the CAG...

Q: Here is the problem: Famas's LLamas has a weighted average cost of capital of  7.9%. The company's c...

A: Calculation of Weight of Debt:The weight of debt is calculated using the WACC formula, where the wei...

Q: I asked this question before, and got an answer, but I have a question about the response that was g...

A: The formula that you shared from the textbook is used for time-value-of-money calculations. Such a f...

Q: This project must fund the construction of an on/off-interchange from an adjacent highway, a 2-mile ...

A: Such a problem is beteer solved in an excel. Hence, I have solved it in excel and provided a snapsho...

Q: Consider a $5,000 deposit earning 10 percent interest per year for ten years. What is the future val...

A: Future Value (FV)Future value is the specific value or amount of an investment at a specific date in...

Q: Here are some historical data on the risk characteristics of Ford and Harley Davidson. Harley Davids...

A: Since you have asked a question with five sub parts, I will address the first three sub parts. Pleas...

Q: You have your choice of two investment accounts. Investment A is a 9-year annuity that features end-...

A: All financials below are in $.Characterisitics of Investment A:Monthly annuity, M = 1,780Frequency i...

Q: Decide whether the following statement makes sense​ (or is clearly​ true) or does not make sense​ (o...

A: The correct answer is “Statement D”.

Q: Block, S., Hirt, G., & Danielsen, B. (2017). Foundations of Financial Management. New York, NY: ...

A: Unit sales per month can be compared to production per month and the difference will give us the cha...

Q: Mobray Corp. is experiencing rapid growth. Dividends are expected to grow at 28 percent per year dur...

A: Dividends: The term dividends allude to that portion of proceeds of an organization which is circula...

Q: Finding the Interest Rate: Concept Connection Example 6-3 (page 237) 18. What interest rates are imp...

A: Interest rate:An interest rate is a percentage on the principal amount at which a lender gives money...

Q: Participation #6:   Why is it desirable to construct capital budgeting rules so that higher-risk pro...

A: A risk is a probability of unfavorable outcome. All investments are subjected to risks.  So, a high ...

Q: A ten-year bond, with par value equals $1000, pays 10% annually. If similar bonds are currently yiel...

A: Calculation of market value of bonds: Excel spreadsheet:

Q: A $2,000 furniture can be financed by paying $50 per month for 4 years. What is the annual nominal c...

A: Nominal Interest Rate:It is the rate of interest which calculates before taking inflation. It exclud...

Q: One of the important components of multinational capital budgeting is to analyze the cash flows gene...

A: First question - Multiple choiceThe correct answer is first option: The Parent company at a high cos...

Q: ackis planning for his son's college education to begin eightyears from today. He estimates the year...

A: First we need to calculate the present value of an ordinary annuity in year 7 as the withdrawal star...

Q: The common stock of Company XLT and its derivative securities currently trade in the market at the f...

A: Part (a)Let's denote current stock price as S0, call premium as C, put premium as P, strike price as...

Q: National governments issue debt securities known as sovereign bonds, which can be denominated in eit...

A: Note:Since, we are entitled to answer up to 3 sub-parts, we shall answer the first 3 as you have not...

Q: 3. Bonds: The three parts of this problem are related. By studying prices of zero coupon STRIPS, you...

A: If rn andrSn+1 are the spot rates for the year n and n+1 respectively, then one year forward rate be...

Q: In Porter's five-factor model why does Porter not include market size and growth rate when determini...

A: Porter's five forces analysis is a structural framework to evaluate the attractiveness of an industr...

Q: Suppose that, in each period, the cost of a security either goes up by a factor of 2 or goes down by...

A: We have two options here, first is doubling and second is moving down by half. We assume that the pr...

Q: Let C be the price of a call option that enables its holder to buy one share of a stock at an exerci...

A: Given that, K is the exercise price, S is stock price at time 0.

Q: A devoted Indiana basketball fan needs $11,200.00 to attend the Final Four exactly one year from tod...

A: As a first step let's assign a symbol to each of the information we have:Future value, FV = $ 11,200...

Q: What finances do you need to have to achieve getting somewhere with Cosmetology?  For instance to bu...

A: Some essential costs that are needed to be assessed before building a salon are:Cosmetics: A salon h...

Q: For example, assume Ethan wants to earn a return of 7.00% and is offered the opportunity to purchase...

A: Calculating the intrinsic value of the bond. We have,Intrinsic value = A / (1+C)1 + A / (1+C)2 + A /...

Q: Hi - could you please send the CAPM model and explain what it is and when to use it please?  Thank y...

A: Capital Asset Pricing Model (CAPM):It defines the relationship of an asset’s expected return with th...

Q: Participation #7:   Why are ROE and EPS such important measures of performance to investors?

A: Let's recall that an (equity) investor is the last one in the pecking order to get the returns. They...

Q: Suppose you wish to invest in an annuity so that you will have $120,000 at some future date for your...

A: We invest $350 monthly at a interest rate of 5.85% per month in order to give the child $120,000 at ...

Q: The prices of a certain security follow a geometric Brownian motion with parameters mu=.12 and sigma...

A: Let's pull together all the variables of Black Scholes Model:S = Current stock price = 40K = Strike ...

Q: At the end of each quarter-year, for 6 years, $1,200 is deposited into an investment paying 3.4% int...

A: At the end of each quarter-year, for 6 years, $1,200 is deposited into an investment paying 3.4% int...

Q: Is there an easily identifiable debt-equity ratio that will maximize the value of a firm? Why or why...

A: No, there is no such ratios.

Q: Consider the following two mutually exclusive projects: Year   Cash Flow  Cash Flow B 0          -$3...

A: NPV (Net Present Value)NPV is the method of finding the worth of an investment or project by deducti...

Q: Six potential services a bank provides?

A: A bank is a financial institution that accepts deposits from the public and makes loans from these d...

Q: Please help do this calculation step by step I have problems getting the final answer.

A: Calculating the value of weighted-average cost of capital (WACC). We have,WACC = (Weight of Equity x...

Q: Given relatively stable financial market conditions, determine which one of the followingbonds would...

A: The bonds that would exhibit the least price volatility is “5-year, 10% coupon bond”.

Q: Bank  M offers the following terms for a $10 million loan: interest rate: 8 percent for one year on...

A: If the firm intends to borrow the $10 million for the entire year.For Bank M:Interest rate is 8 %. S...

Q: Assume a security follows a geometric Brownian motion with volatility parameter sigma=0.2.  Assume t...

A: Given that: The initial price=$25Interest rate is 0%Strike price is $22. volatility parameter sigma ...

Q: If the volatility of a stock is .33, find the standard deviation of: (see the attachment for the rem...

A: Recall the Black Scholes model derivation.Log of the stock price is normally distributed.The expecte...