Portfolio optimization

BF4221 – Investments

WE WRITE ESSAYS FOR STUDENTS

Tell us about your assignment and we will find the best writer for your paper.

Write My Essay For Me

Group project: Portfolio optimization

Objective: To engage you in an asset allocation exercise. The project will require you to applyfundamental concepts of portfolio theory to come up with an efficient portfolio allocation to meetcertain objectives.

It is late in the evening on December 31. Instead of celebrating New Year’s Eve, you are puttingthe finishing touches on a portfolio recommendation for The Incubator Fund (“The Fund”), a non-profitthat offers capital to new start-upventures on a competitive basis. The Board of Trustees at The Fund have selected you and your asset management firmto handle the investment strategy for its $1B endowment fund. You are seeking final approval of yourinvestment recommendations at an 8am meeting with the board of directors on New Year’s Day. If you receiveapproval, you will begin management of the endowment fund and begin employing your strategy whenthe markets reopen on January 2.

The Incubator Fund has long had a fixed investment policy of 50% stocks (spread equally amongst small, mediumand large cap portfolios labeled SmallCap, MidCap and LargeCap, respectively), 20% Intermediate‐TermGovernment Bonds (IntGovBnd), 20% Intermediate‐Term Corporate Bonds (IntCorpBnd) and 10%Treasury bills (Tbill) which has yielded a solid record of performance over the years. The monthlyreturns on these asset classes are contained on the Excel spreadsheet titled “4221ProjectData” (availableon Carmen).

Tanya Weaver is the chairman of Board of Trustees of The Fund. At tomorrow’s meeting, Weaverwould like you to 1) describe the performance of The Fund’s endowment over the past 5 years and 2)decide whether The Fund should consider a change in its long‐standing investment policy.Weaver is also concerned with how risks should be defined in the context of The Fund’s investments. She felt strongly that a return of 0.5% per month (0.005 in decimal form) representeda “floor” below which the portfolio return should not drop. She wanted you to suggest an efficient asset

allocation to achieve this goal.

Based upon your consultations with Weaver, you have drawn up a list of issues that need to beaddressed:

1. How well did The Fund’s portfolio perform over the five‐year period in terms of averagemonthly return and average monthly standard deviation? How well did the risky part of the portfolio perform? (To calculate this for each month, take out the 10% of the return that represents the T-bill return, which will leave the remaining 90% that consists of the risky assets. Then, divide that number by 0.9)

2. Calculate the investment proportions required to achieve the optimal (tangency) risky portfolio andthe minimumvariance portfolio. (Use Solver in Excel to achieve this.) What is the Sharpe Ratio of each?

(When solving for the variance‐covariance matrix, use “var.s” and “covariance.s” as your variance and covariance  formulas. The “s” in the formula refers to using a sample of the data, not the entire universe of data. In the example video I put together on Carmen, I used “var.p” and “covar.p”, but I decided to change it.)

Tip: Set up Solver to spin through all the possible weights of the asset classes to find the set of weights that maximize the value in the cell containing your Sharpe ratio formula to find the tangency portfolio. Do the same thing to find the MVP, except you are trying to find the set of weights that minimize the portfolio variance.

I have posted the 3‐asset example spreadsheet that I used in class that you may use as a template tobuild your spreadsheet. The videos on Carmen walk you how to use Solver to complete this.

Note: whenever you change a matrix formula in Excel, such as in the formulas forstandard deviation and expected return, instead of just hitting <ENTER>, you must hit<CTRL> (hold it down, then hit) <SHIFT> (hold it down also, then hit) <ENTER>. If you don’t do this, Excel will give you an error. You can check to see if you have done this correctlyby looking at the formula—if there are curly brackets around the formula, you have

entered it correctly. Here is an example:

{=MMULT(TRANSPOSE(B14:B16),MMULT(B3:D5,B14:B16))}

NOT

=MMULT(TRANSPOSE(B14:B16),MMULT(B3:D5,B14:B16))

3. Plot the portfolio frontier given the five risky assets in which The Fund is investing.

Tip: look at the return of the MVP. You want your frontier to havepoints both above and below the MVP. So choose returns both above and below the return on yourMVP and use Solver to plot those points. See how the target expected return in G30 of the Examplespreadsheet relates to the other target expected returns. Remember, here is where you add theextra constraint in Solver: expected return = target expected return.

4. Calculate the investment proportions required to construct a complete portfolio (i.e. one whichmixes the optimal risky portfolio from question #2 above with T‐bills, discussed in Chapter 5) that has an expected return equal to the present(complete) portfolio’s expected return. What is the expected standard deviation of return on such aportfolio?

(Note: This simply requires algebra to solve for the two weights. Similar to problem 13a. in Chapter 5.)

5. Suggest a complete portfolio allocation between the optimal risky portfolio and T‐bills to achieve thecollege’s objective of a ‘floor’ rate of return equal to 0.005 (0.5%) per month. What is the standarddeviation of the portfolio?

6. Weaver is also interested in knowing if the college should include a sixth asset class, cryptocurrency, inits portfolio. Make a case for or against the inclusion of cryptocurrency in the college’s overallportfolio. Justify your decision by solving for the new tangency portfolio after including cryptocurrency. What is the Sharperatio of the new tangency portfolio? Then repeat what you did in question #5 by calculating the complete portfolio allocation required to achieve the floor rate of return (0.5%/month) mandated by the college with this expanded universe of assets.What is the expected standard deviation of the new complete portfolio?

7. The college is concerned about the use of short positions in the construction of the risky portfolio.Recompute the 6‐asset tangency portfolio after adding a constraint to prevent shortselling/borrowing. What is the Sharpe Ratio under this constraint? (This involves what you have done in questions #2 and #6, but check the box in Solver that reads “Make Unconstrained Variables Non-Negative”.)

8. Finally, after all this analysis, what is your recommendation? You can stay with The Fund’s current allocation, choose a set of weights based on your 5-asset, 6-asset, short/no-short optimizations, a blend of all the above, or none of the above. What do you tell your client?

Please compute ALL data and answers to six decimal places. Email me your Excel file atwellman.67@osu.eduin thefollowing manner: 1 Excel file containing 6 worksheets (Group members names, answers to thequestions, 5‐asset solution, 6‐asset solution, no short solution, solution to #8) insideone Excel file. Make the sheets formula‐driven, so that if your answer doesn’t match mine, I can click onthe cell and know exactly where the number came from. Please put the names of your group me