Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Year Risk-Free Rate 2010 3.22% 2011 2.78% 2012 1.80% 2013 2.35% 2014 2.54% 2015 2.14% 2016 1.84% 2017 2.33% 2018 2.91% 2019 2.14% Market (S&P
Year | Risk-Free Rate |
2010 | 3.22% |
2011 | 2.78% |
2012 | 1.80% |
2013 | 2.35% |
2014 | 2.54% |
2015 | 2.14% |
2016 | 1.84% |
2017 | 2.33% |
2018 | 2.91% |
2019 | 2.14% |
Market (S&P 500) | Coca Cola | Coca Cola | Pepsi | Pepsi | |
Data | Close | Adj Close | Dividend | Adj Close | Dividend |
12/31/2009 | $ 1,115.10 | $ 18.95 | $ 1.64 | $ 43.96 | $ 1.78 |
12/31/2010 | $ 1,257.64 | $ 23.27 | $ 1.76 | $ 49.02 | $ 2.37 |
12/30/2011 | $ 1,257.60 | $ 26.21 | $ 1.88 | $ 51.36 | $ 2.03 |
12/31/2012 | $ 1,426.19 | $ 28.33 | $ 1.53 | $ 54.64 | $ 2.13 |
12/31/2013 | $ 1,848.36 | $ 33.21 | $ 1.12 | $ 68.11 | $ 2.24 |
12/31/2014 | $ 2,058.90 | $ 34.96 | $ 1.22 | $ 79.87 | $ 2.53 |
12/31/2015 | $ 2,043.94 | $ 36.75 | $ 1.32 | $ 86.86 | $ 2.76 |
12/30/2016 | $ 2,238.83 | $ 36.62 | $ 1.40 | $ 93.63 | $ 2.96 |
12/29/2017 | $ 2,673.61 | $ 41.89 | $ 1.48 | $ 110.31 | $ 3.17 |
12/31/2018 | $ 2,506.85 | $ 44.73 | $ 1.56 | $ 105.00 | $ 3.59 |
12/30/2019 | $ 3,221.29 | $ 53.86 | $ 1.60 | $ 133.88 | $ 3.79 |
Step 1:
- Calculate the Total Return for each year 2010 2019 for the Market, Coca Cola and Pepsi
- Calculate the Dividend Yield for each year 2010 2019 for Coca Cola and Pepsi
- Calculate the Dividend Growth for each year 2010 2019 for Coca Cola and Pepsi
- Calculate the Return Relative for each year 2010 2019 for the Market, Coca Cola and Pepsi
Step 2:
- Calculate the Expected (Average) Return from 2010 2019 for the Risk Free Rate, Market, Coca Cola, and Pepsi
- Calculate the Average Dividend Yield from 2010 2019 for Coca Cola and Pepsi
- Calculate the Average Dividend Growth from 2010 2019 for Coca Cola and Pepsi
- Calculate Geometric Mean Return from 2010 2019 for the Market, Coca Cola and Pepsi
- Calculate Cumulative Wealth Index from 2010 2019 for the Market, Coca Cola and Pepsi
- Calculate the value of $5,000 invested in 2010 at the end of 2019 for the Market, Coca Cola and Pepsi
- Calculate Standard Deviation from 2010 2019 for the Market, Coca Cola and Pepsi (please note for the standard deviation, this is a sample of the data)
- Calculate Beta from 2010 2019 for the Market, Coca Cola and Pepsi
- Calculate CAPM Required Rate of Return for the Market, Coca Cola and Pepsi
- Calculate the Coefficient of Determination (R2) for the Market, Coca Cola and Pepsi
Step 3:
- Calculate Coca Colas intrinsic stock price using the constant dividend growth model, assuming that the growth rate calculated in Step 2.c for Coca Cola is the constant growth rate going forward
- Calculate Pepsis intrinsic stock price using the multiple-growth dividend rate model, assuming that the that the growth rate calculated in Step 2.c for Pepsi is the growth rate for the next five years and that the growth rate for year 6 and going forward is 6.10%
Step 4:
- Using the 2010 2019 Total Returns calculated from Step 1.a for the Market, Coca Cola and Pepsi, construct a portfolio consisting of the following: 50% Market (S&P 500 index), 20% Coca Cola, and 30% Pepsi for the period of 2010 2019
- From this portfolio, calculate the Sharpe Ratio
- From this portfolio, calculate the Treynor Ratio
- From this portfolio, calculate the Jensens Alpha
Step 5:
- Using the 2010 2019 Total Returns calculated from Step 1.a for the Market, Coca Cola and Pepsi, construct a portfolio consisting of the following: 30% Market (S&P 500 index), 50% Coca Cola, and 20% Pepsi for the period of 2010 2019
- From this portfolio, calculate the Sharpe Ratio
- From this portfolio, calculate the Treynor Ratio
- From this portfolio, calculate the Jensens Alpha
Step 6:
- From the calculated Share Ratio, Treynor Ratio, and Jensens Alpha for the portfolio in Step 4 and Step 5, which portfolio provides the best return relative to risk? (e.g. which portfolio would you select because it provides the best return relative to risk?)
Please show me step-by-step solve this in Excel.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started