The estimation of company betas is normally brushed over in many introductory finance texts. This often leads students to view beta as some magical number and not until later courses in finance that they realize that the choice of different returns, indices and intervals examined can deliver different beta estimates. This internet exercise takes you through the basic calculation of beta using Excel in order to help demystify a company beta. Along the way you will find how to download share price and index data of the internet which can then be manipulated in Excel. Finally, you will tie the concept of beta and the CAPM to stock valuation. The Yahoo Finance site has a wealth of financial information on companies and countries from all over the world. Once you arrive at this site you will find a box titled “Get Quotes” which you need to type in the stock symbol (if you are unsure of the stock symbol go to “Symbol Lookup”). To download financial data from the internet follow the detailed instructions in the text box below: Instructions for Downloading Stock Data
• In the Get Quotes box type in the stock symbol (for example Verizon is VZ and the S&P500 index is ^GSPC) and click the GO box.
• Click on Historical Data from the Quotes section under the more on VZ (for example) column. Click on the Time Period in the Set Date Range (then and enter the date range as instructed below and click Done button, Show: Historical Prices, set Frequency to Monthly, and click Apply.
• The first page of data should now be visible on the screen. Click the Download Data link. Save the data to a file, it will be saved as a CSV file.
• Open Excel and then open the file you saved.
• At this point you should have seven columns including the; Date, Open, High, Low and Closing share prices for that month, plus Volume and Adj Close data for each month. You can only need the Date, Open and Close columns for each stock or index.
• • To download dividend payments change the Show setting to Dividends Only, click Apply and export and save this info to a spreadsheet. You will need to do this for any of your stocks that have paid dividends over the time period below. Alternatively, you can just enter the dividends for each stock into your spreadsheet from the Yahoo Finance price table itself. The S&P 500 Index already includes dividends.
1. First download into Excel the APPLE (AAPL), Microsoft (MSFT), and S&P 500 Stock Index (^GSPC) monthly price data for the 5-year period from the Feb. 1, 2015 to Jan. 31, 2020 using the instructions outlined above. Using the Open and Close price data for each month (the date given is the first trading day for that month: i.e. the open and close for the month) to calculate the monthly percentage holding period returns for each stock and index where each month’s Open price is the beginning of the month price and each month’s Close price is the end of the month price. You can get rid of the other data provided for each month. In addition, you will need to include dividend payments for each stock to calculate monthly returns for the months that they made a dividend payment (the index doesn’t have dividend payments). For example, September’s monthly return is (Sept Close – Sept. Open + any Sept Div)/Sept Open. You should have 60 monthly returns for each stock and index from Feb. 1, 2015 to Jan. 31, 2020. Helpful Hint: I strongly urge creating a table of the appropriate opening & closing prices and dividends for each stock along with opening and closing prices for the S&P 500 Index on a separate worksheet and then do your calculations on this “fresh” worksheet.
2. What is the average monthly return for each stock and the S&P 500 index over the entire time period? Convert the average monthly return for each stock and index into an APR (annualized rate) by multiplying your monthly average for each stock by 12. Please convert your answers to percentages
3. Calculate the (population) standard deviation of monthly returns for each stock and the index using Excel.
4. Now calculate the beta for each stock using Excel’s Slope or Linest function to use estimate the slope of the linear regression line, which is beta. Use the S&P 500 index as the market portfolio in finding beta.
5. Now use the CAPM/SML Equation to estimate each stock’s required return. Use 10% as the required market return and the recent 1-year Treasury rate of 1% as the risk-free rate. Would you recommend buying APPLE and/or Microsoft if your APR average for each stock from #2 is your expected return? Explain your answer.
6. Construct a graph of the Security Market Line given by the parameters in #5. Point out the SML returns you calculated in #5 on your SML. Also, find and plot APPLE’s and Microsoft’s expected returns from the information given below gathered from their recent Yahoo Finance stock quotes against each stock’s beta on your SML graph. Would you recommend buying APPLE and/or Microsoft based on this information? Explain your answers. Note: you are evaluating each stock individually, not in comparison in one another.
|Estimated Price 1 year from now||332.91||194.19|
|Annual Expected Dividend (D1)||3.08||2.04|
Can I get help with question 5 and 6?Get Finance homework help today
We aim at 100% customer satisfaction. As such, we never compromise on the quality of our assignment services and strive to maintain the highest standards of work for all our clients. Our writers craft each paper carefully to match the requirements on the instructions form while at the same time being surgical and objective while handling client work.
We receive many applications from writers around the world daily. Nevertheless, we only choose the cream of the crop to tackle your “help with homework” requests. Hence, our homework helpers are highly-qualified individuals who adhere to our quality policy.
We maintain a fair pricing policy for all our homework help services. Our pricing system generates quotations based on the requirements of each paper. Hence regardless of the complexity or duration of a paper, our system determines the most accurate and pocket-friendly price for all our clients.
At MyHomeworkWriters, we guarantee all students swift delivery of their assignments. We understand that time is an essential factor in the academic world. We, therefore, ensure that all assignments are delivered on or before the agreed deadline. This leaves room for editing to incase the client wants changes done to the work delivered by our writers.
We maintain a zero-plagiarism policy in all our papers. Our writers stick to the instructions to deliver plagiarism-free work. Other than that, they extensively research the work they are handling and are keen to avoid any chance of similarities with previously written papers. Our editors check every completed work to ascertain it’s at the set standards.
Our main objective is to assist you through every step of your academic journey. Thus, we completely understand that you might run into trouble once in a while when interacting with our system. If you do run into trouble, please get in touch with our support agents and they will immediately provide you with assistance. Feel free to contact us via the Chat window or email us.
Follow these simple steps to get your homework done
Fill in the order form and provide all details of your assignment.
Choose the payment system that suits you most.
You will be able to track the progress of your paper from our system.
Once your paper is ready, we will email it to you.
Myhomeworkwriters holds a reputation for providing high-quality assignment writing services. You only need to give us all the requirements for your “do my homework” order, and our writers will give you the best results.
Our team is skilled and equipped in searching for information required to complete work handed over to them. Regardless of the complexity of the work, we are determined to get the job done. Extensive research when handling client requests makes it possible for us to be able to stick to the boundaries and guidelines set by the clients. Thus enabling us to keep the work original and plagiarism free.
We have highly qualified writers to assist you whenever you place in "help with my homework" order. Our essay writing service is designed to not only help students get good grades but also excel in their studies. Our writers are highly experienced and educated specialists and professionals who want to do more than creating a difference in the academic field.
Our editors have a keen eye when going through work. They can capture even the smallest of errors. This has been made possible by the fact that we have been in the academic field for more than 5 years now. Thus, they bring with them previous experience when going through work to be able to capture everything. These unique skills make it possible for us to deliver error-free work to our clients.
Our team has very experienced specialists and professionals. This gives us a competitive edge over our competitors. Whenever we receive work that needs to be redone, everyone gets involved. That’s inclusive of our editors. This collective involvement in doing work makes it possible for us to deliver high-quality work to all our clients. Other than that it makes it possible for us to adhere to our originality and plagiarism-free policies.