1) Assume that the power function, U(W) = Wwith
describes the risk aversion level of investors.
Find your risk aversion level based on the following three questions (a-c):
a) You are approached with a 50/50 gamble. There is a potential downside (you can lose money out of your own pocket!). The gamble will cost you $1,000 if you lose…how much would the gamble have to pay if you win in order for you to accept this gamble? If you choose a number < $1,000, you’re doing this wrong! (You will have to input a personal net worth to make this work…even if you are in debt, do not use a net worth of less than $10,000, i.e., let $10,000 be your net worth even if it’s really less than that!)
b) Now, imagine you are approached with a “prize” that comes as part of a 50/50 gamble. It will pay $3000 if you win…If you lose, it will pay nothing. What would you be willing to accept as a “sure thing” prize, rather than gamble on the $3000 vs. $0 payoff? By the way, if you choose a number > $1500 as your “sure thing”, you’re doing this wrong…$1500 would be your “average winnings” WITH a gamble! Again, use $10,000 as a minimum of starting net worth, though you can use more if you’re comfortable stating a higher figure.
c) Again, you’re approached with a gamble with a potential downside. You are offered a gamble that will pay you $500 if you win and cost you $500 if you lose. What would the probability of you winning have to be in order for you to accept this gamble? If you use a number less than or equal to 50% (p < 0.50), you’re doing this wrong. Again, use $10,000 as a minimum of net worth, though you can use more if you’re comfortable stating a higher figure.
Now, each of these scenarios suggests a level of risk aversion for you, measured by . (See the “Risk Aversion Examples” from eLearning (Part 5, and reposted in Part 6) for an example of how to “narrow in” on the exact level of your …) Solve for your for each of a-c above, showing the detail of your calculations in Excel. Then, use the (old-fashioned) arithmetic average of the results of these three cases in order to determine your which will also give your . You will use these values below.
2) Consider the following set of stocks: BA, BAC, CSCO, CVX, KO, XOM, HD, HON, HUM, JNJ, MCD, MRK, NKE, ORCL, PEP, PFE, PG, TGT, VZ, WMT. (Oh, you might as well grab the values of ^GSPC, which represents the S&P 500, while you’re collecting data).
a) Use the annual returns of these 20 firms, over the last 5 years (December 31st, 2015-December 31st, 2020…if New Year’s Eve is on the weekend, just use the last trading day of the year), in order to find the efficient frontier of portfolios using Excel’s optimizer add-in. This will involve finding the full variance/covariance matrix of returns amongst these 20 firms (a “large” task!). I advise, as a default, using daily adjusted closing prices from Yahoo! Finance at the end of each year, or other places where you know you can verify the returns (“adjusted” prices account for stock splits and dividends automatically). If a firm does not have a full 5 years of data, use as many full annual (calendar) returns as you can to form historical expected returns, variances, and covariances, and leave any missing years blank. Just to be clear: for each stock, you should be trying to gather 6 data points (at each “year end” on the last trading day of December) in order to help you calculate 5, one-year returns.
For the expected returns of each stock, use the geometric average annual return. Allow no asset to have more than 30% of your overall investment. Do not allow any shorting (negative weights for stocks). Also, check the GICS codes of each firm (consider using Fidelity’s research center). Create all constraint(s) necessary to ensure that you don’t place more than 40% of your funds into any single GICS code/sector (there are 11 such sectors (though we might not have members of all of them)…and many ways to find them, but try fidelity.com>research>stocks>symbol lookup).
The expected returns, variance/covariance matrix, and the portfolio optimization procedure we’ve developed in class will be utilized in order to create the efficient frontier. Draw a graph of the efficient frontier of the next year’s expected return and standard deviation based on at least 7 efficient frontier points of your choosing. Make sure the first of these is the minimum variance portfolio (Hint: you should START building the frontier with the minimum variance portfolio and THEN create the frontier by trying some various, specific expected return levels that are greater than the expected return of the minimum variance portfolio). Do not show the suboptimal points with expected returns lower than that of the minimum variance portfolio when creating your frontier (that wouldn’t be ‘efficient’!).
b) Presume you are offered the ability to invest in these stocks for an extremely low cost, and thus, you decide to purchase the optimal portfolio of these stocks based on your level of risk aversion. Given your risk aversion level, found in question 1, and the constraints of the portfolio from part a), find the specific, best portfolio of stocks for you. This will require optimizing (maximizing) the utility function, as noted in our “Part 6 Slides.” Make sure you highlight exactly what you would buy (what % of each of the 20 stocks you buy) so that it’s easy for me to see.
3) The procedure used to form the efficient frontier and your optimal portfolio, in question 2, used a variance/covariance matrix based on annual returns over the past 5 years. Let’s continue to accept this as a reasonable way of estimating variances and covariances (You’ll reconsider this assumption later).
The expected returns you used, however, were also historical. This can be problematic. (e.g., expected returns for risky assets should always be positive…in fact, they should be greater than the risk free rate…and we could actually have a negative expected return if we just use the historical geometric average. Expected returns also shouldn’t be too HIGH…right?).
Redo the optimizations from question 2. This time, use the beta of each of your stocks over your measurement period. Use the “=Slope” command in Excel with the annual returns of each of the 20 companies (that you calculated earlier) regressed on the annual returns of the S&P 500 (^GSPC in Yahoo! Finance). This is your “Historical Beta.” THEN, use a revised beta “smoothing” technique of:
SMOOTHED BETAx = 0.8(Historical Betax) + 0.2
Use 9% as the expected market return over the next year and 2% as the risk-free rate, AND the SMOOTHED BETA to form each firm’s expected return for the upcoming year. This is a CAPM-based expected return.
a) Recreate the efficient frontier (similar to question 2a), based on these new expected return values, as well as the same variance/covariance matrix you’ve already created. Again, allow no asset to have more than 30% of your overall investment or any GICS code to hold more than 40% of your funds. Do not allow any shorting (negative weights for stocks). No risk-free asset will be held. Again, draw a graph of the efficient frontier of the next year’s expected return and risk. Again, base this on at least 7 frontier points of your choosing starting with the minimum variance portfolio.
b) Use your risk aversion level from question 1, the new expected return levels described just above in 3a, and the same variance/covariance matrix (again) that you created earlier to recreate your specific, optimal portfolio from this new frontier (this is like question 2b except we now use the newly found, beta-based expected returns instead of the historical returns).
For Project 2, you should be placing one Excel file (with the tabs discussed above) and one Word document (see below) into the class Dropbox online…For Excel, I’m thinking you’ll want (at least) 6 tabs showing calculations…risk aversion work/calculation (gamma and lambda); price collection data along with scratch work of returns, betas, and your covariance matrix; 2a optimizer; 2b optimizer; 3a optimizer; 3b optimizer…and then there are 2 graphs… these graphs may be their own Excel tabs or overlaid onto your optimization worksheets noted in the point above (so now we’d be up to 8 tabs total if your graphs are each on its own tab). Some students use additional tabs to do portions of their work, and that’s fine too. But you should definitely have each of 2a, 2b, 3a, and 3b on its own tab because you can only show the specs of one “solver” at a time…and each of 2a, 2b, 3a, and 3b requires its own specs that I need to see! Note: It is imperative that all of your optimization programs be well labeled, easy to follow and (of course) as correctly specified as possible!
When doing your graphs, clearly show the mean and standard deviation of each frontier’s minimum variance portfolio as well as the coordinates of the other points you use to sketch your graph. As always…SHOW calculation work whenever possible. You should only be typing numbers directly into boxes when they’re grabbed directly from an outside source (like a stock price you’re copying or a value I’ve given you in these directions).
On the two optimization worksheet Excel tabs you used to create your personal portfolio holdings (i.e., 2b and 3b). Include two lists of the final weights that YOU would invest in, based on YOUR risk aversion level (one answering question 2b and the other answering question 3b).
--Finally, in Word, write out a brief, but thorough and well-written response memo to the following questions:
1) Is using historical information regarding variance and covariance a reasonable thing to do in our optimizations if we’re trying to be realistic? Why or why not? (Hint: What happens in the optimization procedure when the solver is forming positions? What is it trying to do? Can this be greatly impacted by unlikely co-movements that happened in the past?)
Additionally, address:
2) Do you agree with how your gamma, from Part 1, suggests a specific portfolio for you in Part 3b? Why or why not? i.e., do you think your gamma pointed you to an “order” off of the efficient frontier “menu” that you actually would order? Why or why not?
Your (well-written, thoughtful response) should be written as a brief memo in Word and placed separately into Dropbox…so one Excel sheet with many tabs, and one Word doc (2 TOTAL UPLOADS) will be your submissions to Dropbox.
The Dropbox will close at the due date. Subsequent submissions must be emailed to me and will be penalized as late (see our syllabus for details…think 1% lost per hour initially, etc.)
---------------------------------------------------------------------------------------------------------------
It’s your primary responsibility to include all above information, it’s also your responsibility to organize and present your findings in an efficient, legible, manner and answer all questions.
This is a challenging assignment which implements a great deal of data work, programming, editing, decision making, and thinking. The project is worth 24% of your final grade, and thus it is meant to take considerable effort, spaced over multiple sittings.
*NOTE: There’s no “template” this time. One reason to start early is to get comfortable organizing data in an effective way. I will show some hints/suggestions in class, providing some PICTURES of what parts of a “good” student Project 2 might look like. But not a template to fill out.
I strongly advise you to begin your work as soon as possible to allow yourself ample time to ask any questions which might arise ASAP. The due date is at 11:59 pm, Sunday, April 11th. The online Dropbox will close at that point, and if you are even a few minutes late, you would have to email submissions to me directly. PLEASE BE SPECIFIC WITH QUESTIONS! It’s very helpful to me and to you if you can point to specific cells in your calculation work that you are struggling with! It’s helpful to YOU if you can ask early so as to implement the help!
*Emotional warning! As soon as our Project 2 due date arrives, Project 3 will be distributed!
Project 2 Rubric
--Correctly calculating personal risk aversion level (20 points)
--Data collection, organization (for returns, betas, covariance matrix) (10 points)
--Correctly setting up optimization sheets for efficient frontiers (2A and 3A) (32 points)
--Correctly setting up optimization sheets for personal allocation (2B and 3B) (16 points)
--Organization and presentation of optimization sheets (making them easy to follow) (7 points)
--Presenting a well-edited write-up to the supplemental question (15 points)
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
