solution20report1711347035972_1711347516284 (3)

docx

School

University of Utah *

*We aren’t endorsed by this school

Course

4070

Subject

Business

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by PrivatePelican1484

Department of Finance David Eccles School of Business University of Utah Spring 2024 FINAN 4070-Investments 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 , 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. 1

- 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. 2

Project 1 Write-up Name(s) ________________________________________ Section I – Index creation and calculations 1. You are creating an index with five stocks: McDonald's Corporation (MCD), Eli Lilly and Company (LLY), 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/2019 to 12/31/2023. Compute the monthly holding period returns to the securities. Fill in the following table with the first few rows of your results: (4 Points) Monthly Adjusted Close Prices Date MCD LLY MSFT ETR AXP 1/1/2019 158.4 110.4 98.98 73.10 95.3 2/1/2019 162.8 116.3 106.18 76.49 100.4 3/1/2019 169.3 120.1 112.26 79.17 101.9 4/1/2019 176.1 108.4 124.32 80.22 109.2 Monthly HPRs Date MCD LLY MSFT ETR AXP 1/1/2019 -- -- -- -- -- 2/1/2019 2.83% 5.36% 7.27% 4.64% 4.90% 3/1/2019 3.29% 2.74% 5.27% 2.46% 1.44% 4/1/2019 4.03% -9.80% 10.73% 1.32% 7.25% 2. Calculate averages, sample standard deviations, and correlations using your HPRs. Fill in the following table with your results: (4 Points) MCD LLY MSFT ETR AXP arithmetic average 121 319 248 76 153 geometric average 1.05000 % 2.85700% 2.28490% 0.53300% 1.14600 % standard deviation 55.99 83.80 63.99 68.82 88.64 Correlations MCD LLY MSFT ETR AXP MCD 1 -- -- -- -- LLY 0.0933 1 -- -- -- 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

MSFT 0.4564 0.3347 1 -- -- ETR 0.5960 -0.0579 0.3249 1 -- AXP 0.5300 -0.006 0.4080 0.3510 1 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 = 191.12052 geometric average =0.01803 standard deviation =47.02617 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 = 183.60391 geometric average = 0.01728 standard deviation =47.10242 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) MCD LLY MSFT ETR AXP 1/1/2019 1263 1812 2021 2736 2098 2/1/2019 1228 1720 1883 2615 1992 3/1/2019 1182 1665 1782 2526 1964 10/1/2023 771 362 593 2141 1379 11/1/2023 718 339 530 2019 1175 12/1/2023 678 344 533 2000 1071 6. Create a market-value weighted index. Assuming you have 40 shares of MCD, 45 shares of LLY, 50 shares of MSFT, 20 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 = 204.89281 geometric average = 0.01937 standard deviation = 47.98575 4

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) The price-weighted index has a higher average return than the equal-weighted index, with arithmetic averages of 191.12052 and 183.60391 respectively. Whereas, in terms of geometric averages, the equal- weighted index outperforms the price-weighted index, with values of 103 and 96.5. it is noticed, the price-weighted index exhibits no much volatility, as indicated by its standard deviation of 47.02617 compared to 47.10242 for the equal-weighted index. This difference in performance can be attributed to the price-weighted index giving more weight to higher-priced stocks, potentially capturing stronger-performing stocks. In contrast, the equal-weighted index assigns the same weight to each stock, which might dilute the impact of high performers. Thus, in this scenario, the price-weighted index demonstrates better performance in terms of average returns and volatility. 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) The average return for the price-weighted index (191.12052) is lower than that of the market value-weighted index (204.89281). This is because the Market weighted index considers market capitalization, which may fully capture the price movements of individual stocks with higher prices. 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 5

your numbers. You may want to support your assertions with calculations.) (4 Points) Comparing the two indices, we find that the average return for the market value-weighted index (204.89281) is greater than that of the equal-weighted index (183.60391). So therefore, the average return for the market value-weighted index is greater than that of the equal-weighted index. This is primarily because the market value-weighted index gives higher weights to stocks with higher market capitalization, which experienced higher returns during the period analyzed. This weighting scheme results in higher average returns for the market value-weighted index compared to the equal-weighted index. Section II – Solving for the optimal risky portfolio 1. Download 5 years (1/1/2019 to 12/31/2023) of monthly data from Yahoo! Finance for Advanced Micro Devices, Inc. (AMD), Adobe Inc. (ADBE), 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) Monthly Adjusted Close Prices Monthly Holding Period Returns Date AMD ADBE XLK AMD ADBE XLK 1/1/2019 24 248 63 -- -- -- 2/1/2019 24 263 67 -3.61% 5.92% 6.91% 3/1/2019 26 266 70 8.46% 1.52% 4.43% 4/1/2019 28 289 75 8.27% 8.54% 6.71% 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) AMD ADBE XLK Arithmetic average 416.24665 196.91328 212.47333 Geometric average 3.09000 1.50020 1.90870 Sample standard deviation 15.1413381 9.66890 6.63712 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) AMD ADBE XLK Sharpe Ratio 27.50418% 20.36525% 32.01191% 4. Compute a correlation matrix for your three securities. Fill in the following table: (3 Points) AMD ADBE XLK 6

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

AMD 1 -- -- ADBE 0.581638 1 -- XLK 0.69370 0.82698 1 5. Create a portfolio by varying the proportion of your budget between AMD and ADBE. 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) Weight in AMD Weight in ADBE Portfolio standard deviation Portfolio expected return 0 1 9.66890 196.91328 0.1 0.9 9.66101 218.84662 0.2 0.8 9.80961 240.77996 0.3 0.7 10.10781 262.71329 0.4 0.6 10.54293 284.64663 0.5 0.5 11.09886 306.57997 0.6 0.4 11.75850 328.51330 0.7 0.3 12.50543 350.44664 0.8 0.2 13.32498 372.37997 0.9 0.1 14.20460 394.31331 1 0 15.13381 416.24665 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

175.00 225.00 275.00 325.00 375.00 425.00 475.00 0.00 2.00 4.00 6.00 8.00 10.00 12.00 14.00 16.00 f(x) = 0.03 x + 3.73 R² = 0.94 Portfolio Risk-Return Profile Portfolio Expected Return Std Deviation 7. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with AMD and ADBE. 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) Weight in AMD Weight in ADBE Portfolio standard deviation Portfolio expected return 0.69610 0.30390 12.47% 349.59059 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.) Optimal Sharpe ratio = 28.02339% The Sharpe ratio measures the return of an investment compared to its risk. A higher Sharpe ratio suggests better risk-adjusted returns. The optimal risky portfolio has a Sharpe ratio of 28.02339%. This means it generates 28.02339% excess return for each unit of risk taken. When comparing it to individual securities, remember that the portfolio is diversified. Diversification spreads risk across different assets, reducing the impact of any single asset's performance on the overall portfolio. Because of diversification, the optimal risky portfolio tends to have a higher Sharpe ratio than individual securities. This is because the portfolio is constructed to balance risk and return more effectively, benefiting from diversification's risk-reducing effect while still capturing returns. So, even though individual securities might have their 8

own Sharpe ratios, the combined effect of diversification in the optimal risky portfolio generally results in a higher Sharpe ratio. This is because diversification helps to mitigate risks while maximizing returns, leading to a more favorable risk-return profile for the portfolio compared to individual securities. 9. Create a portfolio by varying the proportion of your budget between AMD 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) Weight in AMD Weight in XLK Portfolio standard deviation Portfolio expected return 0 1 6.63721 212.47333 0.1 0.9 7.10741 232.85066 0.2 0.8 7.72350 253.22799 0.3 0.7 8.45365 273.60533 0.4 0.6 9.27094 293.98266 0.5 0.5 10.15436 314.35999 0.6 0.4 11.08812 334.73732 0.7 0.3 12.06051 355.11464 0.8 0.2 13.06293 375.49198 0.9 0.1 14.08896 395.86932 1 0 15.13381 416.24665 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) 175.00 225.00 275.00 325.00 375.00 425.00 475.00 0.00 2.00 4.00 6.00 8.00 10.00 12.00 14.00 16.00 f(x) = 0.03 x + 3.73 R² = 0.94 Portfolio Risk-Return Profile AMD vs XLK Portfolio Expected Return Std Deviation 9

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

11. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with AMD 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) Weight in AMD Weight in XLK Portfolio standard deviation Portfolio expected return 0.37541 0.62459 9.10548 288.97172 12. Compute the Sharpe ratio for this optimal risky portfolio (made of AMD and XLK). Assume a monthly risk-free rate equal to 0.37%. Fill in the table below. (1 Points) Sharpe Ratio 31.73561% 13. Create a portfolio by varying the proportion of your budget between ADBE 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) Weight in ADBE Weight in XLK Portfolio standard deviation Portfolio expected return 0 1 6.63721 212.47333 0.1 0.9 6.79487 210.91733 0.2 0.8 6.99399 209.36132 0.3 0.7 7.23115 207.80532 0.4 0.6 7.50274 206.24931 0.5 0.5 7.80517 204.69331 0.6 0.4 8.13499 203.13730 0.7 0.3 8.48903 201.58130 0.8 0.2 8.86437 200.02529 0.9 0.1 9.25842 198.46929 1 0 9.66890 196.91328 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) 10

195.00 200.00 205.00 210.00 215.00 0.00 2.00 4.00 6.00 8.00 10.00 12.00 f(x) = − 0.2 x + 48.33 R² = 0.98 Portfolio Risk-Return Profile ADBE vs XLK Portfolio Expected Return Std Deviation 15. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with ADBE 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) Weight in ADBE Weight in XLK Portfolio standard deviation Portfolio expected return -0.38195 1.38195 6.46093 218.41656 16. Compute the Sharpe ratio for this optimal risky portfolio (made of ADBE and XLK). Assume a monthly risk-free rate equal to 0.37%. Fill in the table below. (1 Points) Sharpe Ratio 33.80515% 17. Compare your optimal risky portfolio of AMD and XLK with your optimal risky portfolio of ADBE 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) In the AMD and XLK portfolio, XLK carries a weight of 0.62459, while AMD holds the remaining weight of 0.37541. Conversely, in the ADBE and XLK portfolio, XLK accounts for a weight of 1.38195, with practically negligible allocation to ADBE due to its negative weight. The Sharpe ratio for the ADBE and XLK portfolio is 33.80515%, indicating a higher risk-adjusted return compared to the 31.71968% Sharpe ratio of the AMD and XLK portfolio. This suggests that the ADBE and XLK portfolio offers superior performance in terms of risk-adjusted returns, likely attributed to a more favorable combination of risk and return characteristics within that portfolio. Factors contributing to this discrepancy could include historical performance, volatility, and the correlation between the assets (ADBE, XLK) within each portfolio. 11

18. Based on your analysis, select the optimal risky portfolio in which you should invest. Assume an absolute risk aversion ( A) of 6. 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 = 0.80385 expected return (complete portfolio) = 212.47333 risk (complete portfolio) = 6.63721 Sharpe (complete portfolio) = 32.0119 Part III – Using an index to build an optimal risky portfolio 1. You want to create one more optimal risky portfolio, comprising AMD and your value-weighted index (from part I question 6). You have already calculated the averages and standard deviations for the returns for AMD and the value-weighted index. You should only need one more number for this calculation: correlation. Find the correlation between AMD and your value-weighted index. (1 Points) correlation = 0.37904 2. Use Solver to find the optimal risky portfolio (maximum Sharpe ratio) for your portfolio created with AMD 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) Weight in AMD Weight in index Portfolio standard deviation Portfolio expected return 0.10009 0.89991 5.08926 226.04636 3. Compare your Sharpe ratio here with the Sharpe ratio obtained when you found the optimal risky portfolio for AMD 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.) The higher Sharpe ratio of the index relative to XLK suggests that it offers a more favorable trade-off between risk and return. The discrepancy in Sharpe ratios between the portfolio using your index and the XLK ETF can be attributed to various factors. Firstly, the construction of the index may prioritize stocks with lower volatility or higher expected returns compared to those 12

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

included in the XLK ETF. This selective approach to portfolio construction aims to optimize risk- adjusted returns, potentially resulting in a higher Sharpe ratio. Despite containing fewer stocks, the index may offer superior diversification benefits. Careful selection of stocks within the index can effectively reduce overall portfolio risk without sacrificing returns. Additionally, if the index is actively managed, portfolio managers can make strategic decisions to further enhance risk-adjusted returns. This may involve dynamically adjusting weights based on market conditions, fundamental analysis, or other factors, providing an edge over passively managed ETFs like the XLK. Moreover, the index may have exposure to specific factors associated with higher risk-adjusted returns relative to those present in the XLK ETF. This factor exposure could include industry sectors or factors like value or growth, which contribute to superior performance. Lastly, constructing and maintaining the index may be more cost-efficient compared to investing in the XLK ETF. Lower expenses translate to higher net returns, contributing to a higher Sharpe ratio for the index portfolio. Overall, these factors collectively contribute to the observed difference in Sharpe ratios between the two portfolios. BONUS: for up to 5 points: Out of all the securities (stocks and ETFs) that are studied in this project, which security has the highest monthly expected return? What would be the effective annual return for this security assuming discrete compounding of 12 months? Would you expect to see this kind of return for this security GOING FORWARD ? What does this tell you about using historical averages as expected returns? What else can you do to generate another expected return? Out of all the securities studied in this project, the security with the highest monthly expected return is XLK, the technology sector SPDR (ETF), with an arithmetic average of 212.47333. Effective Annual Return= (1+Monthly Expected Return/100)12−1 i= (1+0.021212) ^12−1 = (1+0.0212/12) ^12−1 i=0.021407=0.02141 I=i×100=2.14100 This effective annual return is quite high and may not be sustainable going forward. It is essential to consider that historical averages do not necessarily reflect future performance accurately. The past performance of XLK may have been influenced by various market conditions, economic factors, and specific events that may not repeat in the future. To generate another expected return, one must conduct thorough fundamental analysis, evaluate current market conditions, assess industry trends, and consider future growth prospects for XLK constituents. Additionally, incorporating forward-looking estimates, such as earnings forecasts, 13

economic indicators, and geopolitical factors, can provide a more comprehensive and updated view of expected returns. Moreover, employing quantitative models or scenario analysis can help in generating alternative expected returns by simulating various market scenarios and their potential impact on XLK's performance. 14

solution20report1711347035972_1711347516284 (3)

Related Documents