P1

1

13

Project 1 – submission closes at 11:59 pm on Thursday, November 9th

Please note that all guidelines below apply to all students and all projects. Guidelines are nonnegotiable.

Guidelines for submissions:

· Projects can be submitted to Canvas from the time the assignment is posted until the deadline. Project 1 must be submitted by Thursday, November 9th at 11:59 pm. At 11:59 pm, submissions will close. No late projects will be accepted or graded. To ensure a project is turned in on time, please submit early, incomplete versions of projects periodically leading up to the due date. Only the last project submission before the due date will be graded.

· Hard copies of projects will not be accepted. Only digital submissions will be graded.

· You are expected to cite any and all sources used in the completion of this project. Anything taken directly from the course notes or text should be quoted appropriately. An informal list of resources at the end of the project write-up is sufficient for a works cited page.

· Turn in both a Microsoft Word document of your project write-up (fill in the blanks) and a Microsoft Excel spreadsheet with your analysis by uploading them to Canvas. Only Word documents and Excel spreadsheets will be accepted (no pdfs allowed). You are expected to show all work in your spreadsheet. If I cannot find the work in your spreadsheet, you will receive a 0 for the question, regardless of what is presented in your write-up.

· If you have difficulty turning your project in via Canvas, email the project to me immediately. If you wait until after the deadline to alert me to a problem, your project will be counted as late and will not be graded.

· Any projects with blatant plagiarism—sentences or phrases directly lifted from the internet without appropriate quotations and citation— will receive a zero for the project and all students involved will be charged with an Honor Code violation.

· Students are expected to conform to basic conventions of standard written English. If your writing is riddled with errors, misspellings, typos, random capitalizations, and/or font changes to the point where it becomes distracting to your reader, I reserve the right to deduct up to 10 points from your final grade.

· Please round all final answers to five decimal places. Do not round intermediate calculations.

Guidelines for groups:

· Students are expected to complete this project individually or in a group of two or three students.

· You are responsible for forming your own teams.

· You may only work with students in the same section of FINAN 4070.

· Each group/pair of students need only turn in one project—please remember to put the names of all student collaborators on the first page.

· Each student in the group will receive the same grade.

· If you have any questions or need assistance, please see me in office hours or contact me. Please keep mind that many students will wait until the last minute to seek help. Come to office hours the week before the project is due and beat the rush.

· I will not “pre-grade” assignments. If you have specific questions, I am happy to answer them. However, I will not look over assignments to see if everything is “right” before the submissions are due.

· There should be no discussion of this project between teams. If you have elected to work alone, the only person with whom you may discuss this project is me. All teams of students are expected to perform the entire analysis (collecting data, analyzing data, and interpreting the results) by themselves. Therefore, illicit collaboration on this project includes, but is not limited to: sharing data between groups, sharing spreadsheets between groups, looking at another team’s spreadsheet for “inspiration,” sharing write-ups between groups, discussing the answers to short-answer questions with a person in another group, discussing any part of this project with students from previous years’ classes, and consulting with or using spreadsheets from students from previous years’ classes. Any discussion of this project outside teams constitutes a violation of the Honor Code.

Notes on grading:

· Any re-grade requests or challenges to grading must be submitted in writing within one week following receiving your grade.

Project 1 Write-up

Name(s) ________________________________________

Section I – Index creation and calculations

1. You are creating an index with five stocks: Starbucks Corporation (SBUX), Johnson & Johnson (JNJ), Microsoft Corporation. (MSFT), Entergy Corporation (ETR), and American Express Company (AXP). Begin by downloading monthly prices from Yahoo!Finance for all five stocks from 1/1/2018 to 12/31/2022. Compute the monthly holding period returns to the securities. Fill in the following table with the first few rows of your results: (4 Points)

2. Calculate averages, sample standard deviations, and correlations using your HPRs. Fill in the following table with your results: (4 Points)

3. Create a price-weighted index using your five stocks. What are the arithmetic and geometric averages and sample standard deviation for your price-weighted index returns? (4 Points) arithmetic average = geometric average = standard deviation =

4. Create an equal-weighted index using your five stocks. What are the arithmetic and geometric averages and sample standard deviation for your equal-weighted index returns? (4 Points) arithmetic average = geometric average = standard deviation =

5. Calculate the number of shares you must have in each stock for each month for your equal-weighted index returns, assuming an initial portfolio value of $1,000,000. How many shares of each stock should you have for the first three months of the sample? The last three months of the sample? Fill in the table below: (4 Points)

6. Create a market-value weighted index. Assuming you have 30 shares of SBUX, 20 shares of JNJ, 40 shares of MSFT, 25 shares of ETR, and 35 shares of AXP. What are the arithmetic and geometric averages and sample standard deviation for your market value-weighted index returns? (4 Points) arithmetic average = geometric average = standard deviation =

7. For which index is your average return greater, price-weighted or equal-weighted? Why? (Hint: a complete answer will include a very specific discussion of your numbers. You may want to support your assertions with calculations.) (4 Points)

8. For which index is your average return greater, price-weighted or market-value-weighted? Why? (Hint: a complete answer will include a very specific discussion of your numbers. You may want to support your assertions with calculations.) (4 Points)

9. For which index is your average return greater, equal-weighted or market-value-weighted? Why? (Hint: a complete answer will include a very specific discussion of your numbers. You may want to support your assertions with calculations.) (4 Points)

Section II – Solving for the optimal risky portfolio

1. Download 5 years (1/1/2018 to 12/31/2022) of monthly data from Yahoo!Finance for NVIDIA Corporation (NVDA), Texas Instruments Incorporated (TXN), and XLK, a technology sector SPDR (ETF). Compute the monthly holding period returns to the securities. Fill in the following table with the first few rows of your results: (3 Points)

2. Compute the arithmetic and geometric averages and the sample standard deviation for each security’s returns over the five year period. Fill in the following table: (3 Points)

3. Compute the Sharpe ratio for each security. Please use the arithmetic average for your expected returns. Assume a monthly risk free rate of 0.37%. Fill in the following table:

(3 Points)

4. Compute a correlation matrix for your three securities. Fill in the following table:

(3 Points)

5. Create a portfolio by varying the proportion of your budget between NVDA and TXN. Compute the portfolio standard deviation and expected (average) return. Use the arithmetic average for your expected return calculation. Fill in the following table: (4 Points)

6. Plot your portfolio expected return (y-axis) and standard deviation (x-axis). Make sure to title your graph and label your axes. The space below is left for your graph. (3 Points)

7. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with NVDA and TXN. Assume no short sales are allowed. Fill in the table below: (3 Points)

(Important!! remember that it is possible to get a result that puts all your money in one security)

8. Compute the Sharpe ratio for this optimal risky portfolio. Assume a monthly risk free rate equal to 0.37%. How does this Sharpe ratio compare to the Sharpe ratio of each individual security (computed in #3)? Why? (4 Points)

(Note: I am looking for an intuitive explanation here do not say the number is larger or smaller because a number in a formula is larger or smaller.)

9. Create a portfolio by varying the proportion of your budget between NVDA and XLK. Compute the portfolio standard deviation and expected (average) return. Use the arithmetic average for your expected return calculation. Fill in the following table: (4 Points)

10. Plot your portfolio expected return (y-axis) and standard deviation (x-axis). Make sure to title your graph and label your axes. The space below is left for your graph. (3 Points)

11. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with NVDA and XLK. Assume no short sales are allowed. Fill in the table below: (3 Points)

(Important!! remember that it is possible to get a result that puts all your money in one security)

12. Compute the Sharpe ratio for this optimal risky portfolio (made of NVDA and XLK). Assume a monthly risk free rate equal to 0.37%. Fill in the table below. (1 Points)

13. Create a portfolio by varying the proportion of your budget between TXN and XLK. Compute the portfolio standard deviation and expected (average) return. Use the arithmetic average for your expected return calculation. Fill in the following table: (4 Points)

14. Plot your portfolio expected return (y-axis) and standard deviation (x-axis). Make sure to title your graph and label your axes. The space below is left for your graph. (3 Points)

15. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with TXN and XLK. Assume no short sales are allowed. Fill in the table below: (3 Points)

(Important!! remember that it is possible to get a result that puts all your money in one security)

16. Compute the Sharpe ratio for this optimal risky portfolio (made of TXN and XLK). Assume a monthly risk free rate equal to 0.37%. Fill in the table below. (1 Points)

17. Compare your optimal risky portfolio of NVDA and XLK with your optimal risky portfolio of TXN and XLK. How much of your portfolio weight do you put into XLK in each portfolio? Why? Which optimal risky portfolio has a higher Sharpe ratio? Why? (4 Points)

18. Based on your analysis, select the optimal risky portfolio in which you should invest. Assume an absolute risk aversion ( A) of 5. How much of your investment do you put in the risky portfolio? How much of your investment do you put in the riskless asset (monthly average return = 0.37%)? What is your final (complete) portfolio expected return, risk, and Sharpe ratio?

(4 Points)

y =

expected return (complete portfolio) =

risk (complete portfolio) =

Sharpe (complete portfolio) =

Part III – Using an index to build an optimal risky portfolio

1. You want to create one more optimal risky portfolio, comprising NVDA and your value-weighted index (from part I question 6). You have already calculated the averages and standard deviations for the returns for NVDA and the value-weighted index. You should only need one more number for this calculation: correlation. Find the correlation between NVDA and your value-weighted index. (1 Points) correlation =

2. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with NVDA and your index. Assume no short sales are allowed and a monthly risk free rate of 0.37%. Fill in the table below: (3 Points)

(Important!! remember that it is possible to get a result that puts all your money in one security)

3. Compare your Sharpe ratio here with the Sharpe ratio obtained when you found the optimal risky portfolio for NVDA and XLK. XLK is an ETF comprising 66 stocks. Your index only has five stocks. Why is your Sharpe much higher using your index, relative to the Sharpe obtained in the analysis using the ETF? (4 Points)

(Note: I am again looking for an intuitive explanation here do not say the number is larger or smaller because a number in a formula is larger or smaller.)