Question 1
Part 1
"Today is your 18th birthday. Your grandmother has just announced that she has opened a savings account for you with a deposit of $15,000. Moreover, she intends to make a further 9 gifts of a similar amount, each spaced a year apart. That is, the first of these 9 gifts will be deposited at the end of this year, the second at the end of next year, etc. The interest rate is 8% per annum, compounded annually, for all parts of this questions.
a. How much will you have accumulated 10 years later (1 year after the last gift)?
b. How much can you withdraw per year for the following 5 years after your grandmother has made the remaining gifts (the first withdraw paid out at the end of year 11, the second withdraw at the end of year 12, etc.)?
c. If you would like to withdraw $26,500 per year for your living expenses, how long you can live before the money runs out? Again, assume that the first withdrawal occurs 1 year after the last gift."
Part 2
"Your uncle has just announced that he is going to give you $15,000 per year at the end of each of the next 4 years.
a. If the relevant interest rate is 7%, what is the value today of this promise?
b. If the interest rate changes to 8%, what is the value today of this promise?
c. Explain how interest rates influence the value of the promise in parts (a) and (b). "
Part 3
Ten years ago Daniel and Amanda took out a $200,000 mortgage to buy their new house. The mortgage was a 30-year, 10% mortgage with monthly payments. Today their bank offers similar mortgages at 8% interest; however, when Daniel and Amanda went to the bank to inquire about the possibility of refinancing the remainder of their mortgage with a 20-year, 8% mortgage, they were informed that the bank has a $15,000 "exit fee" for the refinancing. The exit fee has an effect of increasing the monthly payments of the mortgage. Calculate the monthly payments if Daniel and Amanda switched to the new mortgage, and advise them if they should refinance the mortgage?
Part 4
"The ""Less Is More"" company is considering expanding to the bathrobe market. The proposed investment plan includes the following:
- Purchase of a new machine: the cost of the machine is $150,000 and its expected life span is 5 years. At the end of year 5, the salvage value (for depreciation purposes) of the machine is 0 but the chief economist of the company estimates that it can be sold for $10,000.
- Depreciation of the new machine: use straight line (prime cost) method.
- Advertising compaign: the head of the marketing department estimates that the campaign will cost $80,000 annually.
- Annual production is 6,800 bathrobes.
- The fixed cost of the new department will be $40,000 annually.
- Variable costs are estimated at $30 per bathrobe but because of the expected rise in labor costs they are expected to rise at 5% per year.
- Each of the bathrobes will be sold at a price of $45 the first year. The company estimates that it can raise the price of the bathrobes by 10% in each of the following years.
- The ""Less Is More"" discount rate (WACC) is 10% and corporate tax rate is 36%.
Using the inputs suggested in the proposed investment, design an Excel financial model that can help to evaluate the replacement project. The model should enable ""Less Is More"" to consider the proposed investment and forecast the net cash flows. (4 marks will be allocated to the presentation of your financial model - e.g., use of clear headings, clear arrangement for input cells, colors, flexibility, warnings and error messages, etc.)
Answer the following questions using your Excel financial model:
a. Estimate the free cash flows of the proposed investment.
b. What is the net present value (NPV) of the proposed investment? should ""Less Is More"" undertake the proposed investment?
Assume tax credits on losses from the operation can be used to offset taxes from the company's other operations in the same financial year."
Question 2
You are planning to invest in the following stocks:
Rows 8 to 78 show the monthly closing prices of the stocks between 2010 and 2015.
| Date |
Rio Tinto Ltd (RIO.AX) |
Apple Inc. (AAPL) |
Cathay Pacific Airlines (0293.HK) |
| Jan-2010 |
68.00 |
27.44 |
12.80 |
| Nov-2015 |
45.91 |
118.30 |
13.68 |
"a. Design an Excel financial model that can help to evaluate this investment plan. Marks will be allocated for the presentation and clarity of your model (e.g., if there are clear headings, clear arrangement for input cells, appropriate use of colors, have some degree of flexibility, generate warning messages for wrong user inputs, etc.). The Excel financial model should enable you to consider the investment plan and answer the following questions:
b. For each stock, show the monthly stock returns.
c. Based on the responses in part (b), calculate the average monthly stock returns and standard deviation for each stock.
d. Calculate the correlation of monthly stock returns between each pair of stocks.
e. Suppose that you invest 20% in Apple Inc., 50% in Rio Tinto Ltd and 30% in Cathay Pacific Airlines. Calculate the portfolio expected return, variance and standard deviation.
f. How does the expected return and standard deviation change if you decide to invest 60% in Rio Tinto Ltd, 30% in Apple Inc. and 10% in Cathay Pacific Airlines? Explain your answers.
g. Find the weights of Apple Inc., Rio Tinto Ltd and Cathay Pacific Airlines that will provide a monthly return of 0.5% with the lowest level of risk.
h. Plot a scatter diagram showing the portfolios from parts (e), (f) and (g), with the X-axis displaying standard deviation and the Y-axis displaying monthly returns.
i. If the monthly risk-free rate is 0.1%, estimate the Sharpe ratio for the portfolios mentioned in parts (e), (f) and (g) and highlight the highest Sharpe ratio using conditional formatting."
Number of Words : 500 Words/ 2 Pages
Referencing Style : Chicago
References : 1
Comments : The assignment must be submitted as a MS Excel document showing formulas where required