BF4221 Investments

Group project: Portfolio optimization

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

It is late in the evening on December 31. Instead of celebrating New Years Eve, you are putting the finishing touches on a portfolio recommendation for The Incubator Fund (The Fund), a non-profit that offers capital to new start-up ventures on a competitive basis. The Board of Trustees at The Fund have selected you and your asset management firm to handle the investment strategy for its $1B endowment fund. You are seeking final approval of your investment recommendations at an 8am meeting with the board of directors on New Years Day. If you receive approval, you will begin management of the endowment fund and begin employing your strategy when the markets reopen on January 2.

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

Tanya Weaver is the chairman of Board of Trustees of The Fund. At tomorrows meeting, Weaver would like you to 1) describe the performance of The Funds endowment over the past 5 years and 2) decide whether The Fund should consider a change in its longstanding investment policy. Weaver is also concerned with how risks should be defined in the context of The Funds investments. She felt strongly that a return of 0.5% per month (0.005 in decimal form) represented a "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 be addressed:

1. How well did The Funds portfolio perform over the fiveyear period in terms of average monthly 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 and the minimum variance portfolio. (Use Solver in Excel to achieve this.) What is the Sharpe Ratio of each?

(When solving for the variancecovariance 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 3asset example spreadsheet that I used in class that you may use as a template to build 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 for standard deviation and expected return, instead of just hitting , you must hit (hold it down, then hit) (hold it down also, then hit) . If you dont do this, Excel will give you an error. You can check to see if you have done this correctly by looking at the formulaif 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 have points both above and below the MVP. So choose returns both above and below the return on your MVP and use Solver to plot those points. See how the target expected return in G30 of the Example spreadsheet relates to the other target expected returns. Remember, here is where you add the extra constraint in Solver: expected return = target expected return.

4. Calculate the investment proportions required to construct a complete portfolio (i.e. one which mixes the optimal risky portfolio from question #2 above with Tbills, 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 a portfolio?

(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 Tbills to achieve the college's objective of a 'floor' rate of return equal to 0.005 (0.5%) per month. What is the standard deviation of the portfolio?

6. Weaver is also interested in knowing if the college should include a sixth asset class, cryptocurrency, in its portfolio. Make a case for or against the inclusion of cryptocurrency in the college's overall portfolio. Justify your decision by solving for the new tangency portfolio after including cryptocurrency. What is the Sharpe ratio 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 6asset 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 Funds 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 at wellman.67@osu.edu in the following manner: 1 Excel file containing 6 worksheets (Group members names, answers to the questions, 5asset solution, 6asset solution, no short solution, solution to #8) inside one Excel file. Make the sheets formuladriven, so that if your answer doesnt match mine, I can click on the cell and know exactly where the number came from. Please put the names of your group members in both the email and on the first worksheet of your Excel file.

BF4221 Investments

Group project: Portfolio optimization

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

It is late in the evening on December 31. Instead of celebrating New Years Eve, you are putting the finishing touches on a portfolio recommendation for The Incubator Fund (The Fund), a non-profit that offers capital to new start-up ventures on a competitive basis. The Board of Trustees at The Fund have selected you and your asset management firm to handle the investment strategy for its $1B endowment fund. You are seeking final approval of your investment recommendations at an 8am meeting with the board of directors on New Years Day. If you receive approval, you will begin management of the endowment fund and begin employing your strategy when the markets reopen on January 2.

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

Tanya Weaver is the chairman of Board of Trustees of The Fund. At tomorrows meeting, Weaver would like you to 1) describe the performance of The Funds endowment over the past 5 years and 2) decide whether The Fund should consider a change in its longstanding investment policy. Weaver is also concerned with how risks should be defined in the context of The Funds investments. She felt strongly that a return of 0.5% per month (0.005 in decimal form) represented a "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 be addressed:

1. How well did The Funds portfolio perform over the fiveyear period in terms of average monthly 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 and the minimum variance portfolio. (Use Solver in Excel to achieve this.) What is the Sharpe Ratio of each?

(When solving for the variancecovariance 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 3asset example spreadsheet that I used in class that you may use as a template to build 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 for standard deviation and expected return, instead of just hitting , you must hit (hold it down, then hit) (hold it down also, then hit) . If you dont do this, Excel will give you an error. You can check to see if you have done this correctly by looking at the formulaif 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 have points both above and below the MVP. So choose returns both above and below the return on your MVP and use Solver to plot those points. See how the target expected return in G30 of the Example spreadsheet relates to the other target expected returns. Remember, here is where you add the extra constraint in Solver: expected return = target expected return.

4. Calculate the investment proportions required to construct a complete portfolio (i.e. one which mixes the optimal risky portfolio from question #2 above with Tbills, 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 a portfolio?

(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 Tbills to achieve the college's objective of a 'floor' rate of return equal to 0.005 (0.5%) per month. What is the standard deviation of the portfolio?

6. Weaver is also interested in knowing if the college should include a sixth asset class, cryptocurrency, in its portfolio. Make a case for or against the inclusion of cryptocurrency in the college's overall portfolio. Justify your decision by solving for the new tangency portfolio after including cryptocurrency. What is the Sharpe ratio 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 6asset 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 Funds 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?

P