Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please show the excel data used or the formulas used. Thank You! In this problem, we will find the portfolio return and risk for a
Please show the excel data used or the formulas used. Thank You!
In this problem, we will find the portfolio return and risk for a three-asset portfolio using IBM, JNJ, and KO. We have collected monthly stock prices for the past four years. As with our prior chapter, we are using the adjusted price, which accounts for stock splits and dividends. Our goal here is to find the portfolio's expected return and variance if we invest 25% in IBM, 50% in JNJ, and the remaining 25% in KO. (NOTE: We are assuming that the future returns will follow the historical pattern.) Next, we find the monthly returns for each stock in our portfolio. Return IBM Return JNJ Return KO IBM JNJ Expected return Variance Standard deviation Weight JNJ 139.90 139.79 135.74 132.20 128.20 145.01 138.19 136.39 132.08 129.95 118.98 116.43 123.12 124.74 Portfolio return Covariance IBM, JNJ Covariance IBM, KO Covariance JNJ, KO Portfolio variance Portfolio standard devation Date 4/1/2019 3/1/2019 2/1/2019 1/1/2019 12/1/2018 11/1/2018 10/1/2018 9/1/2018 8/1/2018 7/1/2018 6/1/2018 5/1/2018 4/1/2018 3/1/2018 2/1/2018 1/1/2018 12/1/2017 11/1/2017 10/1/2017 9/1/2017 8/1/2017 7/1/2017 6/1/2017 5/1/2017 4/1/2017 3/1/2017 2/1/2017 1/1/2017 12/1/2016 11/1/2016 10/1/2016 9/1/2016 8/1/2016 7/1/2016 6/1/2016 5/1/2016 4/1/2016 3/1/2016 2/1/2016 1/1/2016 12/1/2015 11/1/2015 10/1/2015 9/1/2015 IBM 140.44 141.10 136.54 132.87 112.36 121.29 112.67 147.59 141.44 139.95 134.90 134.95 138.44 146.53 147.37 154.81 145.09 139.63 144.25 135.85 132.53 134.05 142.53 140.05 147.08 159.79 163.71 158.88 151.12 146.36 138.66 143.32 142.12 143.67 135.76 134.91 128.06 132.90 113.82 108.40 119.54 119.98 120.55 124.76 KO 48.21 46.45 44.95 47.71 46.94 49.57 47.09 45.05 43.47 45.48 42.40 41.57 41.77 41.61 41.41 45.60 43.96 43.50 43.70 42.44 42.95 43.22 41.94 42.52 40.35 39.34 38.90 38.54 38.94 37.09 38.98 38.59 39.60 39.78 41.02 40.36 40.54 41.65 38.73 38.54 38.57 37.98 37.74 35.44 Note: use Ctrl+Shift+Enter 125.62 133.65 135.13 133.94 134.01 124.98 126.44 126.78 126.37 121.70 117.16 118.19 115.21 106.76 108.61 104.20 108.59 110.60 110.99 116.47 112.81 104.06 103.50 99.91 Portfolio vairance (using MMULT) Portfolio standard deviation 96.45 95.75 94.17 92.13 91.94 84.95Step 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