Finance 3507: Security Analysis & Portfolio Project Assignment: Due by 11:59pm on 6/24/2024 Professor Yan Li The total score for this project is 100 points. The point for each question is provided in the parenthesis. The project can be completed by a group of five (or fewer) students. It is your responsibility to make sure the accuracy of the work done by your colleagues. ALL MEMBERS OF YOUR GROUP WILL RECEIVE THE SAME GRADE ON THE PROJECT. Your final project will include an Excel file which shows the detailed calculation and all your answers to the questions asked in the project. The Excel file must be submitted on Canvas by 11:59pm on 6/24/2024. Please submit ONE copy for each group with ALL group members' names listed. No credit will be given to a student unless both his/her first and last names are included in the project. NO LATE ASSIGNMENT IS ACCEPTED. Part One: Portfolio Management Go to http://finance.yahoo.com, download monthly Adj. close prices (adjusted for dividend and stock split) from 01/01/2017 through 12/01/2022 for the following three companies. You can use a monthly risk free rate of 0.08% for related analysis. Company Ticker APPLE AAPL WALMART WMT GOLDMAN SACHS GS Q1: (6') List dates, Adj. close prices for the three companies. Q2: (6') Compute monthly holding period return using Adj. close prices for each stock. Q3: (6') Use the EXCEL statistical functions to compute mean (AVERAGE) and standard deviation (STDEV) of the monthly return for each stock. Assume that historical mean returns are good estimates of expected returns. To achieve diversification, John invests in APPLE and WALMART. Q4: What are the weights on the two stocks to achieve the optimal risky portfolio for John (4')? What are the mean and standard deviation of returns on his optimal risky portfolio (4')? Must show your calculation by including the formulas you used (4'). Mary instead invests in APPLE and GOLDMAN SACHS. Q5: What are the weights on the two stocks to achieve the optimal risky portfolio for Mary (4')? What are the mean and standard deviation of returns on her optimal risky portfolio (4')? Must show your calculation by including the formulas you used (2'). Q6: Discuss whose optimal portfolio performs better (John or Mary) (5'), and explain why (5').

2 Part Two: Estimation of Beta Please answer the following questions for two stocks, Dynex Capital Inc. (ticker DX) and International Paper Company (ticker IP). The risk free rate for this exercise is provided in a separate EXCEL file with file name "risk_free_rate.xls". Note that the risk free rate is quoted as annual percentage rate. Q7: (4') Download monthly Adj. close prices from 01/01/2017 through 12/01/2022 for each stock. Q8: (8') Compute monthly holding period return using Adj. close prices for each stock. Suppose we consider the US S&P 500 ETF (ticker: SPY) as the market portfolio. Q9: (4') Download monthly Adj. close prices for SPY from 01/01/2017 through 12/01/2022. Q10: (4') Compute monthly holding period return using Adj. close prices for SPY. Q11: (12') Estimate betas of DX and IP based on the index model regressions, and show your regression output. Q12: Calculate mean return (4'), Standard deviation (4'), and beta of the two portfolios (6'): Portfolio A (90% in SPY and 10% in DX), Portfolio B (90% in SPY and 10% in IP). Include the formulas you used for your calculation (4').