Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I need all of this created in a new excel file. I do not know how to complete all of this. 1. Start by downloading

I need all of this created in a new excel file. I do not know how to complete all of this.

1. Start by downloading monthly data for Microsoft and the SP500. Start the data in January of 2014 through the latest month. (Please note the start and end dates.) Delete all but the appropriate data for calculating returns leaving three columns labeled: Date, MSFT and SP500.

2. To the right of those columns, calculate the arithmetic and geometric monthly returns for both MSFT and the SP500. Label appropriately, MSFTA, SP500A, MSFTG, SP500G.

3. Give yourself some space above those columns and calculate the monthly averages and standard deviations for those returns.

4. I want you to create a simple pivot table for the total returns for each year. Put the pivot table in a tab of its own named Pivot. You will need to create a variable in the original data that will allow you to tabulate the returns by year.

5. In the pivot table, put years in the left hand column and calculate the total return for each year. (Note, I did not say average.)

6. Go back to the original data and use it to calculate the beta for MSFT. Put the beta in a new tab called Beta.

7. Below the beta estimate, calculate the expected return for the market using the following data. Label your answer Rm and put it close to the data so I know where to find it.ScenarioprobExp Returnriots0.1-0.33slow growth0.3-0.05medium growth0.50.09high growth0.10.18

8. You know the t-bill rate is .001, so you have all the information needed to use the CAPM to calculate the expected return for MSFT. Please put this calculation to the right of the table we used above.

9. To finish up, lets do a simple time value of money calculation. You should have room below the last two questions to do the PV, FV, Rate, etc. table as I showed you in class. How many years would it take you to get to $5,000,000 if you saved $500 monthly in an investment yielding 7%?

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Entrepreneurial Financial Management An Applied Approach

Authors: Jeffrey R Cornwall, David O Vang, Jean M Hartman

5th Edition

0367335417, 978-0367335410

More Books

Students also viewed these Finance questions

Question

Define a class, subclass, and super class, and give examples.

Answered: 1 week ago