ps3

pdf

School

Columbia University *

*We aren’t endorsed by this school

Course

8306

Subject

Finance

Date

Jan 9, 2024

Type

pdf

Pages

Uploaded by CorporalHerring3953

Professor Xuelin Li Due: Fri, Nov 3 1 Problem Set 3 PROBLEM SET INSTRUCTIONS Please hand in one Excel spreadsheet per group. Write the names of all group members on the top of your Excel spreadsheet. Turn in homework on Canvas by midnight on the due date shown above. Question 1. Global drawdown episodes Go to the http://www.macrohistory.net/ website, and download the data set (Database  Download data  Excel). This dataset contains market and macroeconomic history data for 18 countries for over 150 years. For this problem, you will use the return data for France. The annual returns, including dividends, for each country are in the eq_tr column. Each row of the data corresponds to a country-year observation. The easiest way to work with the data is to take the country of interest, and copy all of its data to another spreadsheet (remember to also copy over the first row so you know which columns are which). a) Using the annual returns starting from the earliest to the most recent observations, construct the cumulative return series for your country of interest (follow the instructions for calculations cumulative returns in the eq2_refresher_on_returns handout. Assume your investment is worth $1 in the year prior to the first return observation, and then your investment will be worth 1 × (1 + 𝑒𝑒𝑒𝑒 _ 𝑡𝑡𝑟𝑟 1 ) in the first year, and it will be worth 1 × (1 + 𝑒𝑒𝑒𝑒 _ 𝑡𝑡𝑟𝑟 1 ) × (1 + 𝑒𝑒𝑒𝑒 _ 𝑡𝑡𝑟𝑟 2 ) in the second year and so on. b) Now plot the cumulative return series over the entire sample. (Alternatively, you may want to plot the natural logarithm of the cumulative return, the =log() function in Excel, to make the patterns easier to identify; this converts cumulative returns to continuously compounded returns). Visually identify what appears to be the longest drawdown in the sample for your country. A drawdown is a period of time that the stock market falls below its previous peak, before setting a new peak (as we discussed in class). c) Now identify the largest percentage fall that the stock market in question experiences from its peak to its trough. d) What do you think about the risks of stock market investing in light of this analysis?

Professor Xuelin Li Due: Fri, Nov 3 2 Question 2. Constant Growth Dividend Discount Model for Apple In this problem, you will find the intrinsic value of a share of Apple Inc (ticker: AAPL) using the constant growth dividend discount model. You obtain data for the valuation model from the Bloomberg equity screen for AAPL as follows (To bring up the screen, type: “ AAPL US Equity ” and then enter, though this is optional since the data is provided below): Information Value as of 10/21/2023 Bloomberg command AAPL market close 172.88 Type: HP Enter Last quarterly dividend 0.24 Type: DVD Enter Analyst Annual EPS Adjusted Estimates 2023: 𝐸𝐸 0 = 6.07 2024: 𝐸𝐸 1 = 6.60 2025: 𝐸𝐸 2 = 7.44 Type: EEO Enter AAPL adjusted beta vs S&P 500 over last 2 years 𝐴𝐴𝐴𝐴𝐴𝐴 𝛽𝛽 = 1.25 Type: BETA Enter and adjust the start and end dates Current 5-Year Treasury Yield 𝑟𝑟 𝐹𝐹 = 4.86% Type: USSW Enter Key Assumptions : • Apple’s last annual dividend ( D 0 ) is equal to its most recent quarterly dividend times four. • Apple’s payout ratio is constant, implying that its dividend growth is equal to its earnings growth. • We estimate Apple’s assumed constant earnings growth ( g ) as the simple average of (1) the long-run economic growth rate of 4% 1 and (2) the 2-year expected growth rate implied by analysts’ next two-year adjusted EPS forecasts—i.e., the rate g that satisfies 𝐸𝐸 0 × (1 + 𝑔𝑔 ) 2 = 𝐸𝐸 2 . • Apple’s required rate of return ( 𝑘𝑘 = 𝐸𝐸 [ 𝑟𝑟 𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴 ] ) is given by the CAPM, (i.e. 𝐸𝐸�𝑟𝑟 𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴𝐴 � = 𝑟𝑟 𝐹𝐹 + 𝛽𝛽 × 𝐸𝐸𝑒𝑒𝐸𝐸𝐸𝐸𝑡𝑡𝐸𝐸 𝑅𝑅𝐸𝐸𝑅𝑅𝑘𝑘 𝑃𝑃𝑟𝑟𝑒𝑒𝑃𝑃𝐸𝐸𝐸𝐸𝑃𝑃 ). You can use 6% as the equity risk premium, or another value as long as you can justify it. • Use the DDM relationship 𝑃𝑃 0 = 𝐷𝐷 0 (1 + 𝑔𝑔 )/( 𝑘𝑘 − 𝑔𝑔 ) to determine intrinsic value. 1 2% annual inflation plus 2% annual real GDP growth.

Professor Xuelin Li Due: Fri, Nov 3 3 a) What is the intrinsic value of Apple today? (NOTE: It is possible that, given the current data for Apple, you may get some “strange” answers here. Discuss why.) b) According to this analysis, is Apple underpriced or overpriced? c) At what discount rate is Apple fairly valued? Would you be willing to buy Apple stock if you were to earn this return over the next few years? d) Based on your valuation analysis in part (a), if the market converges to your fair value next year, what 1-year holding period return would you expect from buying Apple at its current price? (See the handout eq2_additional_information which explains the “convergence to fair value” concept.) e) Based on your analysis in parts (a) and (b), would you overweight or underweight AAPL in your overall portfolio? Do you trust this analysis enough to actually invest or not invest in Apple based on it? Question 3. Constructing Efficient Portfolios with Three Stocks. 5 years of end-of-month share prices for three US firms-- Merck (MRK), Microsoft (MSFT), and United Technologies (UTX)---are provided in the problem set template on Canvas. For convenience, the prices in this spreadsheet are adjusted to include dividend payments. a) Compute the monthly stock returns for each of the three stocks as the percentage change in adjusted prices. Compute average monthly return and the standard deviation of monthly returns for each stock. Annualize average monthly returns by multiplying by 12 and annualize the standard deviation of monthly returns by multiplying by √ 12 . b) Compute the (annualized) Sharpe ratio for an investor holding each individual stock, assuming that the annualized risk-free rate is 0.50%. c) Now suppose the investor constructs a portfolio that equally weights MRK, MSFT, and UTX, thus creating a fourth risky asset. Compute the 60 historical monthly returns for this portfolio in the column labeled “Eq_Wt_Ret.” Calculated the average return and standard deviation (monthly and annualized), and the Sharpe ratio for this portfolio. d) If an investor could combine any one of the four risky portfolios above (pure MRK, pure MSFT, pure UTX, or the equal-weighted portfolio) with the risk-free asset, which risky portfolio should she choose as part of her overall portfolio? e) Using Solver, find the weights on MRK, MSFT, and UTX of the portfolio that has the highest possible Sharpe ratio. Calculate the average return and standard

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

Professor Xuelin Li Due: Fri, Nov 3 4 deviation (monthly and annualized), and the Sharpe ratio for this portfolio. Hint: towards the bottom of the spreadsheet you will find a row of three cells labeled “Max Sharpe Portfolio Weights.” Once you put a set of weights in these cells, you can use the Excel SUMPROD function (with appropriate absolute addresses), in the “Opt_Wt_Ret” column to calculate the 60 monthly returns of the portfolio with these weights. (note the, since the three weights must sum to 1, you should use an Excel equation to specify that the weight on UTX is 1 minus the sum of weights on MRK and MSFT). Then use Solver to adjust these weights on MRK and MSFT to maximize the portfolio Sharpe ratio. f) For an investor with a risk-aversion coefficient of A=3 calculate the optimal investment portfolio over this period. That is, find the optimal weights on MRK, MSFT, UTX and the risk-free asset (assuming that this investor knew the average returns, standard deviations and covariances at the beginning of this 5-year period).