Date	^SPX Price	MSFT Price	^GC Price	^TNX daily
9/3/2021	4535.43264	301.14	1831.5	0.00360%
9/7/2021	4520.02775	300.18	1796.3	0.00373%
9/8/2021	4514.06853	300.21	1791.2	0.00363%
9/9/2021	4493.2843	297.25	1797.8	0.00354%
9/10/2021	4458.57985	295.71	1789.9	0.00365%
9/13/2021	4468.7263	296.99	1792.3	0.00360%
9/14/2021	4443.04986	299.79	1805	0.00348%
9/15/2021	4480.70149	304.82	1792.6	0.00355%
9/16/2021	4473.75275	305.22	1754.6	0.00362%
9/17/2021	4432.98748	299.87	1749.4	0.00373%
9/20/2021	4357.72787	294.3	1761.8	0.00356%
9/21/2021	4354.18886	294.8	1776.1	0.00360%
9/22/2021	4395.63714	298.58	1776.7	0.00364%
9/23/2021	4448.98405	299.56	1747.7	0.00384%
9/24/2021	4455.47792	299.35	1749.7	0.00397%
9/27/2021	4443.1104	294.17	1750	0.00404%
9/28/2021	4352.63435	283.52	1737.5	0.00417%
9/29/2021	4359.46106	284	1722.9	0.00419%
9/30/2021	4307.53875	281.92	1757	0.00416%
10/1/2021	4357.03888	289.1	1758.4	0.00398%
10/4/2021	4300.45901	283.11	1767.6	0.00403%
10/5/2021	4345.71622	288.76	1760.9	0.00416%
10/6/2021	4363.54516	293.11	1761.8	0.00414%
10/7/2021	4399.76331	294.85	1759.2	0.00427%
10/8/2021	4391.33805	294.85	1757.4	0.00436%
10/11/2021	4361.19049	294.23	1755.7	0.00439%
10/12/2021	4350.64597	292.88	1759.3	0.00430%
10/13/2021	4363.80451	296.31	1794.7	0.00421%
10/14/2021	4438.26438	302.75	1797.9	0.00413%
10/15/2021	4471.36845	304.21	1768.3	0.00428%
10/18/2021	4486.46049	307.29	1765.7	0.00431%
10/19/2021	4519.63061	308.23	1770.5	0.00444%
10/20/2021	4536.19214	307.41	1784.9	0.00445%
10/21/2021	4549.78135	310.76	1781.9	0.00455%
10/22/2021	4544.89825	309.16	1796.3	0.00450%
10/25/2021	4566.478	308.13	1806.8	0.00444%
10/26/2021	4574.78884	310.11	1793.4	0.00440%
10/27/2021	4551.68114	323.17	1798.8	0.00416%
10/28/2021	4596.41508	324.35	1802.6	0.00426%
10/29/2021	4605.37665	331.62	1783.9	0.00423%
11/1/2021	4613.67301	329.37	1795.8	0.00428%
11/2/2021	4630.65336	333.13	1789.4	0.00421%
11/3/2021	4660.56958	334	1763.9	0.00429%
11/4/2021	4680.05584	336.44	1793.5	0.00414%
11/5/2021	4697.52681	336.06	1816.8	0.00395%
11/8/2021	4701.70204	336.99	1828	0.00407%
11/9/2021	4685.25118	335.95	1830.8	0.00390%
11/10/2021	4646.70767	330.8	1848.3	0.00424%
11/11/2021	4649.2692	332.43	1863.9	0.00424%
11/12/2021	4682.8474	336.72	1868.5	0.00430%
11/15/2021	4682.80354	336.07	1866.6	0.00441%
11/16/2021	4700.89713	339.51	1854.1	0.00444%
11/17/2021	4688.6707	339.12	1870.2	0.00436%
11/18/2021	4704.54194	341.27	1861.4	0.00432%
11/19/2021	4697.95511	343.11	1851.6	0.00418%
11/22/2021	4682.94395	339.83	1806.3	0.00442%
11/23/2021	4690.69805	337.68	1783.8	0.00453%
11/24/2021	4701.46485	337.91	1784.3	0.00447%
11/26/2021	4594.61828	329.68	1788.1	0.00403%
11/29/2021	4655.27019	336.63	1785.2	0.00416%
11/30/2021	4567.00149	330.59	1776.5	0.00393%
12/1/2021	4513.03616	330.08	1784.3	0.00390%
12/2/2021	4577.10053	329.49	1762.7	0.00394%
12/3/2021	4538.42786	323.01	1783.9	0.00366%
12/6/2021	4591.6718	326.19	1779.5	0.00390%
12/7/2021	4686.74546	334.92	1784.7	0.00403%
12/8/2021	4701.20786	334.97	1785.5	0.00410%
12/9/2021	4667.45395	333.1	1776.7	0.00404%
12/10/2021	4712.02414	342.54	1784.8	0.00405%
12/13/2021	4668.97209	339.4	1788.3	0.00387%
12/14/2021	4634.08762	328.34	1772.3	0.00391%
12/15/2021	4709.85391	334.65	1764.5	0.00398%
12/16/2021	4668.6712	324.9	1798.2	0.00387%
12/17/2021	4620.63899	323.8	1804.9	0.00381%
12/20/2021	4568.01724	319.91	1794.6	0.00386%
12/21/2021	4649.22664	327.29	1788.7	0.00404%
12/22/2021	4696.56157	333.2	1802.2	0.00396%
12/23/2021	4725.78824	334.69	1811.7	0.00406%
12/27/2021	4791.19195	342.45	1808.8	0.00403%
12/28/2021	4786.35412	341.25	1810.9	0.00403%
12/29/2021	4793.05812	341.95	1805.8	0.00420%
12/30/2021	4778.72691	339.32	1814.1	0.00412%
12/31/2021	4766.18297	336.32	1828.6	0.00411%
1/3/2022	4796.56125	334.75	1800.1	0.00442%
1/4/2022	4793.53722	329.01	1814.6	0.00453%
1/5/2022	4700.57837	316.38	1825.1	0.00463%
1/6/2022	4696.05383	313.88	1789.2	0.00471%
1/7/2022	4677.02766	314.04	1797.4	0.00481%
1/10/2022	4670.29038	314.27	1798.8	0.00483%
1/11/2022	4713.06732	314.98	1818.5	0.00474%
1/12/2022	4726.34572	318.27	1827.3	0.00469%
1/13/2022	4659.02549	304.8	1821.4	0.00465%
1/14/2022	4662.85412	310.2	1816.5	0.00481%
1/18/2022	4577.1084	302.65	1812.4	0.00506%
1/19/2022	4532.75962	303.33	1843.2	0.00496%
1/20/2022	4482.7276	301.6	1842.6	0.00498%
1/21/2022	4397.93917	296.03	1831.8	0.00475%
1/24/2022	4410.12743	296.37	1841.7	0.00471%
1/25/2022	4356.45388	288.49	1852.5	0.00484%
1/26/2022	4349.92864	296.71	1829.7	0.00502%
1/27/2022	4326.51368	299.84	1795	0.00491%
1/28/2022	4431.84912	308.26	1786.6	0.00484%
1/31/2022	4515.54758	310.98	1796.4	0.00484%
2/1/2022	4546.53903	308.76	1801.5	0.00489%
2/2/2022	4589.37656	313.46	1810.3	0.00480%
2/3/2022	4477.44195	301.25	1804.1	0.00496%
2/4/2022	4500.53146	305.94	1807.8	0.00524%
2/7/2022	4483.86529	300.95	1821.8	0.00520%
2/8/2022	4521.53887	304.56	1827.9	0.00530%
2/9/2022	4587.17889	311.21	1836.6	0.00523%
2/10/2022	4504.08479	302.38	1837.4	0.00551%
2/11/2022	4418.63652	295.04	1842.1	0.00530%
2/14/2022	4401.66698	295	1869.4	0.00541%
2/15/2022	4471.07425	300.47	1856.2	0.00555%
2/16/2022	4475.0111	299.5	1871.5	0.00555%
2/17/2022	4380.25856	290.73	1902	0.00535%
2/18/2022	4348.8692	287.93	1899.8	0.00524%
2/22/2022	4304.7576	287.72	1907.4	0.00529%
2/23/2022	4225.50488	280.27	1910.4	0.00536%
2/24/2022	4288.69601	294.59	1926.3	0.00534%
2/25/2022	4384.65449	297.31	1887.6	0.00539%
2/28/2022	4373.93915	298.79	1900.7	0.00499%
3/1/2022	4306.25889	294.95	1943.8	0.00464%
3/2/2022	4386.54409	300.19	1922.3	0.00506%
3/3/2022	4363.48675	295.92	1935.9	0.00501%
3/4/2022	4328.87	289.86	1935.9	0.00470%

1/1/2015

1/19/2015

2/16/2015

4/3/2015

5/25/2015

7/3/2015

9/7/2015

11/26/2015

12/25/2015

1/1/2016

1/18/2016

2/15/2016

3/25/2016

5/30/2016

7/4/2016

9/5/2016

11/24/2016

12/26/2016

1/2/2017

1/16/2017

2/20/2017

4/14/2017

5/29/2017

7/4/2017

9/4/2017

11/23/2017

12/25/2017

1/1/2018

1/15/2018

2/19/2018

3/30/2018

5/28/2018

7/4/2018

9/3/2018

11/22/2019

12/25/2019

1/1/2019

1/21/2019

2/18/2019

4/19/2019

5/27/2019

7/4/2019

9/2/2019

11/28/2019

12/25/2019

1/1/2020

1/20/2020

2/17/2020

4/10/2020

5/25/2020

7/3/2020

9/7/2020

11/26/2020

12/25/2020

1/1/2021

1/18/2021

2/15/2021

4/2/2021

5/31/2021

7/5/2021

9/6/2021

11/25/2021

12/24/2021

1/17/2022

2/21/2022

4/15/2022

5/30/2022

7/4/2022

9/5/2022

11/24/2022

12/26/2022

Transcribed Image Text:

4. Computing Beta in Excel Download the file named "Beta.xlsx" containing daily prices/returns for Microsoft stock (MFST), the S&P 500 Index (^SPX), gold (^GC), and risk free rate, represented by the U.S. government 10-year bond (^TNX) from Blackboard. a. Use the data to compute daily returns for Microsoft, the S&P index, and gold. Compute returns from the previous trading day's price to the current day's (You will lose one observation). The risk-free rate (^TNX) is already in daily percentage terms. b. Use the given risk-free rate (^TNX) to compute excess returns for MSFT and ^GC and the market risk premium for "SPX. c. Use the "-slope" and "-intercept" formulas in excel to compute the beta and alpha of MSFT and ^GC. d. Interpret alpha and beta for each asset. Do these beta make sense to you? Why or why not? 5. Capital Asset Pricing Model (CAPM) a. Explain why it is important to assume that investor's already hold the value- weighted "market", or tangency, portfolio in order to apply the Capital Asset Pricing Model (CAPM). b. Does the risk-free asset need to exist in order for us to derive the CAPM? If not, how do investors achieve 2-fund separation? (Hint: Your textbook can help with this.) 6. Securities Market Line (SML) a. Show and explain how the securities market line plots the relationship between risk and return. b. Explain how a security may be "undervalued" or "overvalued" based on the SML. c. Explain the concept of "alpha". Show alpha graphically on the SML. 7. Empirical tests of the CAPM a. When empirically testing an asset pricing model such as the CAPM, what is meant when it is said that such an analysis is a "dual test"? b. Explain some of critiques of Roll and others that have been made against the CAPM c. Do you think that the CAPM should still be used in practice when assessing an asset's risk/return? Why? 8. Asset Pricing Models a. What is Arbitrage Pricing Theory (APT)? b. How is APT different from the CAPM? c. If we conclude that the market is inefficient (to varying degrees), are asset pricing models still useful? Explain. 4. Computing Beta in Excel Download the file named "Beta.xlsx" containing daily prices/returns for Microsoft stock (MFST), the S&P 500 Index (^SPX), gold (^GC), and risk free rate, represented by the U.S. government 10-year bond (^TNX) from Blackboard. a. Use the data to compute daily returns for Microsoft, the S&P index, and gold. Compute returns from the previous trading day's price to the current day's (You will lose one observation). The risk-free rate (^TNX) is already in daily percentage terms. b. Use the given risk-free rate (^TNX) to compute excess returns for MSFT and ^GC and the market risk premium for "SPX. c. Use the "-slope" and "-intercept" formulas in excel to compute the beta and alpha of MSFT and ^GC. d. Interpret alpha and beta for each asset. Do these beta make sense to you? Why or why not? 5. Capital Asset Pricing Model (CAPM) a. Explain why it is important to assume that investor's already hold the value- weighted "market", or tangency, portfolio in order to apply the Capital Asset Pricing Model (CAPM). b. Does the risk-free asset need to exist in order for us to derive the CAPM? If not, how do investors achieve 2-fund separation? (Hint: Your textbook can help with this.) 6. Securities Market Line (SML) a. Show and explain how the securities market line plots the relationship between risk and return. b. Explain how a security may be "undervalued" or "overvalued" based on the SML. c. Explain the concept of "alpha". Show alpha graphically on the SML. 7. Empirical tests of the CAPM a. When empirically testing an asset pricing model such as the CAPM, what is meant when it is said that such an analysis is a "dual test"? b. Explain some of critiques of Roll and others that have been made against the CAPM c. Do you think that the CAPM should still be used in practice when assessing an asset's risk/return? Why? 8. Asset Pricing Models a. What is Arbitrage Pricing Theory (APT)? b. How is APT different from the CAPM? c. If we conclude that the market is inefficient (to varying degrees), are asset pricing models still useful? Explain.