Say you are a stock investor. You are considering to invest in MICROSOFT CORP. T

Question

Say you are a stock investor. You are considering to invest in MICROSOFT CORP. To that end, you download the stocks’ historical price data given in “Computer_Exercise2.xlsx”. More precisely, you analyze the end-of-month stock price from January 2003 to December 2013. PLEASE ANSWER THE FOLLOWING QUESTIONS IN EXCEL, USING PROPER EXCEL FUNCTIONS.

1. To start, you want to get an idea of the monthly return that you could have obtained by buying in one month an selling in the next (assuming that there are no dividends). Compute the monthly net return; i.e. the proportionate change in prices.

2. Now you want to know in how many of the total months in your sample you would have made a positive net return. What is the number of months in which your net return would have been positive?

3. You decided that you want to invest in MICROSOFT CORP. Say that the outcome of your “experiment” (i.e. your investment) that you are interested in is whether you will win a positive net return in the first month or not. That is, you define a Bernoulli random variable, X, that can take on values 1 (positive net return next month), and 0 (zero or negative net return next month). Since you don’t know how to otherwise approach the question (you are a first-time investor), you decide that the likelihood of successes in the past should be an indication of the future probability of success. Hence, what is the probability of success (i.e. positive net return) based on the 2003-2013 sample?

4. What is the expected value/population mean of your Bernoulli variable X?

5. What are the (population) variance and standard deviation of X? Having computed the latter, comment on how good your “guess” for X (i.e. in the expected value in 4.) really is.

6. Is the distribution of X symmetric about its mean? Why or why not?

7. Instead, now assume that your investment horizon is 12 months, where in each month you either can gain a positive net return (Xi=1) or a negative net return (Xi=0). Assume that the random variables Xi, i=1, 2,…,12, are independent. The probability of success is still the same as in 3., and it is the same for all i. What is the probability that you will make a positive net return in 7 (out of the in total 12) following months)?

8. Under the same assumptions as in 7., what is the probability that you will make a positive net return in more than 6 (out of the in total 12) following months?

DATE MICROSOFT CORP Closing Price 20030131 47.46 20030228 23.7 20030331 24.21 20030430 25.56 20030530 24.61 20030630 25.64 20030731 26.41 20030829 26.52 20030930 27.8 20031031 26.14 20031128 25.71 20031231 27.37 20040130 27.65 20040227 26.53 20040331 24.93 20040430 26.13 20040528 26.23 20040630 28.56 20040730 28.49 20040831 27.3 20040930 27.65 20041029 27.97 20041130 26.81 20041231 26.72 20050131 26.28 20050228 25.16 20050331 24.17 20050429 25.3 20050531 25.8 20050630 24.84 20050729 25.61 20050831 27.38 20050930 25.73 20051031 25.7 20051130 27.68 20051230 26.15 20060131 28.15 20060228 26.87 20060331 27.21 20060428 24.15 20060531 22.65 20060630 23.3 20060731 24.06 20060831 25.7 20060929 27.35 20061031 28.71 20061130 29.36 20061229 29.86 20070131 30.86 20070228 28.17 20070330 27.87 20070430 29.94 20070531 30.6901 20070629 29.47 20070731 28.99 20070831 28.73 20070928 29.46 20071031 36.81 20071130 33.6 20071231 35.6 20080131 32.6 20080229 27.1999 20080331 28.38 20080430 28.52 20080530 28.32 20080630 27.51 20080731 25.72 20080829 27.29 20080930 26.69 20081031 22.33 20081128 20.22 20081231 19.44 20090130 17.1 20090227 16.15 20090331 18.37 20090430 20.26 20090529 20.89 20090630 23.77 20090731 23.52 20090831 24.65 20090930 25.72 20091030 27.73 20091130 29.41 20091231 30.48 20100129 28.18 20100226 28.67 20100331 29.2875 20100430 30.535 20100528 25.8 20100630 23.01 20100730 25.81 20100831 23.465 20100930 24.49 20101029 26.665 20101130 25.2575 20101231 27.91 20110131 27.725 20110228 26.58 20110331 25.39 20110429 25.92 20110531 25.01 20110630 26 20110729 27.4 20110831 26.6 20110930 24.89 20111031 26.63 20111130 25.58 20111230 25.96 20120131 29.53 20120229 31.74 20120330 32.255 20120430 32.015 20120531 29.19 20120629 30.59 20120731 29.47 20120831 30.82 20120928 29.76 20121031 28.54 20121130 26.615 20121231 26.7097 20130131 27.45 20130228 27.8 20130328 28.605 20130430 33.1 20130531 34.9 20130628 34.545 20130731 31.84 20130830 33.4 20130930 33.28 20131031 35.405 20131129 38.13 20131231 37.41

Explanation / Answer

Answer for question no.1:

Net return in calculated by deducting the next month closing price with the closing price of the previous month. Under mentioned is the list of such net returns for the data provided>

DATE   Closing Price   Net return
20030131   47.46  
20030228   23.7   -23.76
20030331   24.21   0.51
20030430   25.56   1.35
20030530   24.61   -0.95
20030630   25.64   1.03
20030731   26.41   0.77
20030829   26.52   0.11
20030930   27.8   1.28
20031031   26.14   -1.66
20031128   25.71   -0.43
20031231   27.37   1.66
20040130   27.65   0.28
20040227   26.53   -1.12
20040331   24.93   -1.6
20040430   26.13   1.2
20040528   26.23   0.1
20040630   28.56   2.33
20040730   28.49   -0.07
20040831   27.3   -1.19
20040930   27.65   0.35
20041029   27.97   0.32
20041130   26.81   -1.16
20041231   26.72   -0.09
20050131   26.28   -0.44
20050228   25.16   -1.12
20050331   24.17   -0.99
20050429   25.3   1.13
20050531   25.8   0.5
20050630   24.84   -0.96
20050729   25.61   0.77
20050831   27.38   1.77
20050930   25.73   -1.65
20051031   25.7   -0.03
20051130   27.68   1.98
20051230   26.15   -1.53
20060131   28.15   2
20060228   26.87   -1.28
20060331   27.21   0.34
20060428   24.15   -3.06
20060531   22.65   -1.5
20060630   23.3   0.65
20060731   24.06   0.76
20060831   25.7   1.64
20060929   27.35   1.65
20061031   28.71   1.36
20061130   29.36   0.65
20061229   29.86   0.5
20070131   30.86   1
20070228   28.17   -2.69
20070330   27.87   -0.3
20070430   29.94   2.07
20070531   30.6901   0.7501
20070629   29.47   -1.2201
20070731   28.99   -0.48
20070831   28.73   -0.26
20070928   29.46   0.73
20071031   36.81   7.35
20071130   33.6   -3.21
20071231   35.6   2
20080131   32.6   -3
20080229   27.1999   -5.4001
20080331   28.38   1.1801
20080430   28.52   0.14
20080530   28.32   -0.2
20080630   27.51   -0.81
20080731   25.72   -1.79
20080829   27.29   1.57
20080930   26.69   -0.6
20081031   22.33   -4.36
20081128   20.22   -2.11
20081231   19.44   -0.78
20090130   17.1   -2.34
20090227   16.15   -0.95
20090331   18.37   2.22
20090430   20.26   1.89
20090529   20.89   0.63
20090630   23.77   2.88
20090731   23.52   -0.25
20090831   24.65   1.13
20090930   25.72   1.07
20091030   27.73   2.01
20091130   29.41   1.68
20091231   30.48   1.07
20100129   28.18   -2.3
20100226   28.67   0.49
20100331   29.2875   0.6175
20100430   30.535   1.2475
20100528   25.8   -4.735
20100630   23.01   -2.79
20100730   25.81   2.8
20100831   23.465   -2.345
20100930   24.49   1.025
20101029   26.665   2.175
20101130   25.2575   -1.4075
20101231   27.91   2.6525
20110131   27.725   -0.185
20110228   26.58   -1.145
20110331   25.39   -1.19
20110429   25.92   0.53
20110531   25.01   -0.91
20110630   26   0.99
20110729   27.4   1.4
20110831   26.6   -0.8
20110930   24.89   -1.71
20111031   26.63   1.74
20111130   25.58   -1.05
20111230   25.96   0.38
20120131   29.53   3.57
20120229   31.74   2.21
20120330   32.255   0.515
20120430   32.015   -0.24
20120531   29.19   -2.825
20120629   30.59   1.4
20120731   29.47   -1.12
20120831   30.82   1.35
20120928   29.76   -1.06
20121031   28.54   -1.22
20121130   26.615   -1.925
20121231   26.7097   0.0947
20130131   27.45   0.7403
20130228   27.8   0.35
20130328   28.605   0.805
20130430   33.1   4.495
20130531   34.9   1.8
20130628   34.545   -0.355
20130731   31.84   -2.705
20130830   33.4   1.56
20130930   33.28   -0.12
20131031   35.405   2.125
20131129   38.13   2.725
20131231   37.41   -0.72

Answer for question no.2:

By applying number filter greater than zero on the net return column in excel sheet, it is observed that positive return is earned in 71 instances.

Answer for question no.3:

Probability of success = number of times postive return is earned/Total number of instances in the sample

=71/131

=0.54199

Answer for question no.4:

Average of net positive earnings for 132 samples =1.3823

Avearage of net negative earnings for 132 samples=-1.8032.

Expected earnings=Probability of earnings positive returns * average positive return+Probability of earning negative return * average negative returns

=0.541985*1.3823+(1-0.541985)*-1.8032.

=-0.07671.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.