Question

Goal: Finding Annual Expected Return and Beta for your portfolio. Please provide your data sheet along with your answer. Assumptions ad Conditions: 1. Return and Beta Frequency: monthly, 2016 January- 2019 December 2. Portfolio Assets: a. Chipotle Mexican Grill, Inc.(CMG) b. United Parcel Service, Inc. (UPS) c. HP Inc.(HPQ) 3. Portfolio Weights: 40% on CMG, 35% on UPS, and 25% on HPQ 4. Question1: Find Individual Expected Return, standard deviation and Individual Beta. 5. Question 2: Find Annual Portfolio Expected Return and Portfolio Beta. (Please use market index S&P500-AGSPC, Risk-Free Rate-AIRX)

data of return of stock is not mention, i used yahoo finace to look up the company

Answer 1

Clipboard B Font Alignment Number Styles Cells Editing F4 Clipboard - A X fi C =B4/B3-1 D L M N O STEP 1 CALCULATING INDIVIDUAL First, we will find expected returns for individual stocks use AVERAGE MONTHLY returns to calculate expected returns NAME OF COMPANY 10 01-08-2016/ 423.9995.79306 CMG UPS HPO EXPECTED RETURN 0.0177 0.0098 0.0213 NAME OF COMPANY B E F G H I PRICE PRICE RETURNS 2 DATE S&P-GSPC CMG UPS HPQ S&P-GSPC 3 01-01-2016 452.971 81.310257 8.415976 1940.23999 4 01-02-2016 509.16 84.23288 9.265374 1932.22998 0.124048 0.035944 0.100927 -0.004128 5 01-03-2016 470.97 92.750908 10.67815 2059.73999 -0.07501 0.101125 0.152479 0.065991 6 01-04-2016 420.97 92.399147 10.75409 2065.3000491 -0.10616 -0.00379 0.007112 0.002699 7 01-05-2016 441.96 90.657913 11.72695 2096.949951 0.049861 -0.01884 0.090465 0.015325 8 01-06-2016 402.76 95.456322 10.9995 2098.860107 -0.0887 0.052929 -0.06203 0.000911 9 01-07-2016 423.99 95.793068 12.50836 2173.600098 0.052711 0.003528 0.137175 0.03561 10 01-08-2016 413.73 96.785538 12.82977 2170.949951 -0.0242 0.010361 0.025696 -0.001219 11 01-09-2016 423.5| 97.596039 13.86544 2168.27002 0.023614 0.008374 0.080724 -0.001234 12 01-10-2016 360.76 96.168144 13.05193 2126.1499021 -0.14815 -0.01463 -0.05867 -0.019426 13 01-11-2016 396.33 103.450378 13.87162 2198.810059 0.098597 0.075724 0.062802 0.034175 14 01-12-2016 377.32 103.025375 13.3672 2238.830078 -0.04797 -0.00411 -0.03636 0.018201 15 01-01-2017| 421.44 98.073601 13.67133 2278.8701171 0.11693 -0.04806 0.022752 0.017884 16 01-02-2017 418.74 95.045036 15.7788 2363.639893 -0.00641 -0.03088 0.154153 0.037198 17 01-03-2017| 445.52 97.1688 16.24208 2362.719971| 0.063954 0.022345 0.029361 -0.000389 18 01-04-2017 474.47 97.313683 17.22773 2384.199951| 0.06498 0.001491 0.060685 0.009091 19 01-05-2017| 477.35 95.964363 17.17281 2411.800049 0.00607 -0.01387 -0.00319 0.011576 20 01-06-2017| 416.1 100.945343 16.0011 2423.409912] -0.12831 0.051904 -0.06823 0.004814 21 01-07-2017 343.77 100.671509 17.61537 2470.300049 -0.17383 -0.00271 0.100885 0.019349 22 01-08-2017 316.71 104.386566 17.59692 2471.6499021 -0.07872 0.036903 -0.00105 0.000546 23 01-09-2017) 307.83 110.432236 18.40852 2519.360107|| -0.02804 0.057916 0.046121 0.019303 24 01-10-2017 271.9 108.078117 20.01238 2575.26001 -0.11672 -0.02132 0.087126 0.022188 25 01-11-2017 304.39 111.682861 19.91953 2584.840088 0.119493 0.033353 -0.00464 0.00372 26 01-12-2017 289.03 110.381851 19.51091 2673.6101071 -0.05046 -0.01165 -0.02051 0.034343 27 01-01-2018 324.76 117.95063 21.79866 2823.810059 0.12362 0.068569 0.117254 0.056179 318.41 96.726555 21.86409 2713.8300781 -0.01955 -0.17994 0.003002 -0.038947 29 01-03-2018 323.11 97.779686 20.48999 2640.870117 0.014761 0.010888 -0.06285 -0.026884 Sheet1 + Ready 98.07 16 01-02-2017_421.44 CMG UPS HPQ VARIANCE 0.0095 0.0045 0.0053 SD 0.09771197 0.067164943 0.072607702 01-05-2002.7L 419:17 100.6386566 1,14 477.35, 200.9453 NAME OF COMPANY CMG UPS HPQ COVARIANCE 0.000895 0.0014149 0.001453 VARIANCE BETA 0.0095 0.093719 0.0045 0.313636 0.0053 0.275521 304.39 111.69 26 01-12-2017 STEP 2 CALCULATING PORTFOLI 278 01-02-2018 The formula for portfolio return is the following: 33.11 97.779 @ -- + 100%

The data has been calculated for Jan 2016 to Dec 2019 on a monthly basis.

✓ Ruler ✓ Formula Bar Split Hide View Side by Side EET Synchronous Scrolling Gridlines Headings Zoom Macros F Normal Page Break Page Custom Preview Layout Views Workbook Views 100% Zoom to Selection New Arrange Freeze Window All Panes - Switch Windows Unhide & Reset Window Position Show Zoom Window Macros P24 K L M N O P Q R S T U V W X Y STEP 1 CALCULATING INDIVIDUAL RETURN, SD AND BETA First, we will find expected returns for individual stocks use AVERAGE MONTHLY returns to calculate expected returns NAME OF COMPANY Expected return of Individual stock = Average of the returns of stock CMG UPS HPO EXPECTED RETURN 0.0177 0.0098 0.0213 Expected return= average(F4:F50) NAME OF COMPANY To calculate the portfolio risk or standard deviation, we must calculate the variance first CMG UPS HPO VARIANCE 0.0095 0.0045 0.0053 SD 0.09771197 0.067164943 0.072607702 1006- Formula of Variance = Var.s(F4:F50) SD= SQRT(VARIANCE) NAME OF COMPANY Formula of Beta= Covariance/ Variance CMG UPS HPO COVARIANCE 0.000895 0.0014149 0.001453 VARIANCE BETA 0.0095 0.093719 0.0045 0.313636 0.0053 0.275521 F4:F50- return of stock 14:150- return of index Covariance= covar.s( 74:F50,14:150) variance=var.s(F4:f50) STEP 2 CALCULATING PORTFOLIO RETURN AND BETA The formula for portfolio return is the following: 29 Sheet1 = Ready 2 -

Calibri V 11 VAA = ab Wrap Text General Normal Bad 1 LIE 8 Cut E Copy - Format Painter Clipboard KII< > Paste 0 0 BIU - - - A- .00 Good Conditional Format as Formatting Table Neutral Insert Delete Format Merge & Center - Alignment is Σ AutoSum - A Fill Sort & Find & Clear Filter - Select Editing Font Numbers Styles Cells N45 M Io Pa R S T STEP 2 CALCULATING PORTFOLIO RETURN AND BETA 27 The formula for portfolio return is the following: E(R ) w; E(R;) NAME OF COMPANY the above is the formula for expected return CMG UPS HPQ EXPECTED RETURN 0.0177 0.0098 0.0213 WEIGHT 0.40 0.35 0.25 we will multiply the expected return with weights and calculate the sum of returns PORTFOLIO RETURN 0.015829856 portfolio return- =sumproduct(L34:L36,M34:M36) NAME OF COMPANY CMG UPS HPQ BETA 0.0937 0.3136 0.2755 WEIGHT 0.40 To calculate portfolio beta we will multiply the individual stock beta with weights assigned 0.35 0.25 0.216140299 Beta- = Sumproduct(L41:L43, M41:M43) 43 PORTFOLIO BETA 44 45 46 47 Sheet1 =