80.2K

Verified Solution

Link Copied!

can you please explain this in excel.

From her Investment Analysis class, Laura has been given an assignment to evaluate several securities on a risk-return tradeoff basis, The specific securities to be rescarched are Intemational Business Machines, Helmerich \& Payne, Inc, and the S\&P 500 Index. The respective ticker symbols for the stocks are IBM and IIP. She finds the following data on the securities in quetion. Complete the steps below using cell references to given data or previous calculations, In some cases, a simple cell reference is all you need, To coppypavie a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. if a specific Ercel function is to be used, the directions will specify the use of that function. Do not hpe in numerical data into a cell or function. Instead, male a reference to the cell in which the data is found, Male your computations only in the bhe cells highlighted below, In all cases, unless othenvise directed, wse the carliest appearance of the data in your formulas, wswally ihe Given Data section. Note: The value of the S\&P 500 Index includes dividends. Price values are the beginning of the year values. Dividends are the end of year values. To Do Creale a spreadshect and analyze the following market transactions. a. Use the data that Laura has found on the three securities and calculate the holding period return for cach year and the average retum over a five-year period. b. Calculate the slandard deviations of the returns for IBM, HP, and the S\&P 500 Index. c. What industries are atsociated with IBM and HP? d. Based on your answer in part c and your results for the average return and the standard deviation, what conclusions can Laura make a. Use the data that Laura has found on the three securities and calculate the holding period return for each year and the average return over a five-year period. Specifically, the HPR will be based upon five unique one-year periods (i.c, 2012 to 2013, 2013 to 2014,2014 to 2015, 2015 to 2016, and 2016 to 2017). Use the following formula: HPR=[lne+(VnV0)]V0 where lne=incomeduringperiod,Vn=endinginvestmentvalue,V0=beginninginvestmentvalue. The average rate of return could be interpreted as either the arithmetic average of the HPRs shown in the HPR row or the IRR of the following investment; a share is bought at the end of 2011, dividends are received annually during the period 2012-2016, the share is sold at the very end of 2016 (after 2016 dividends are received). Assume that the prices in the table above are given for the beginning of the year. b. Calculate the standard deviations of the returns for IBM, HP, and the S\&P 500 Index. From her Investment Analysis class, Laura has been given an assignment to evaluate several securities on a risk-return tradeoff basis, The specific securities to be rescarched are Intemational Business Machines, Helmerich \& Payne, Inc, and the S\&P 500 Index. The respective ticker symbols for the stocks are IBM and IIP. She finds the following data on the securities in quetion. Complete the steps below using cell references to given data or previous calculations, In some cases, a simple cell reference is all you need, To coppypavie a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. if a specific Ercel function is to be used, the directions will specify the use of that function. Do not hpe in numerical data into a cell or function. Instead, male a reference to the cell in which the data is found, Male your computations only in the bhe cells highlighted below, In all cases, unless othenvise directed, wse the carliest appearance of the data in your formulas, wswally ihe Given Data section. Note: The value of the S\&P 500 Index includes dividends. Price values are the beginning of the year values. Dividends are the end of year values. To Do Creale a spreadshect and analyze the following market transactions. a. Use the data that Laura has found on the three securities and calculate the holding period return for cach year and the average retum over a five-year period. b. Calculate the slandard deviations of the returns for IBM, HP, and the S\&P 500 Index. c. What industries are atsociated with IBM and HP? d. Based on your answer in part c and your results for the average return and the standard deviation, what conclusions can Laura make a. Use the data that Laura has found on the three securities and calculate the holding period return for each year and the average return over a five-year period. Specifically, the HPR will be based upon five unique one-year periods (i.c, 2012 to 2013, 2013 to 2014,2014 to 2015, 2015 to 2016, and 2016 to 2017). Use the following formula: HPR=[lne+(VnV0)]V0 where lne=incomeduringperiod,Vn=endinginvestmentvalue,V0=beginninginvestmentvalue. The average rate of return could be interpreted as either the arithmetic average of the HPRs shown in the HPR row or the IRR of the following investment; a share is bought at the end of 2011, dividends are received annually during the period 2012-2016, the share is sold at the very end of 2016 (after 2016 dividends are received). Assume that the prices in the table above are given for the beginning of the year. b. Calculate the standard deviations of the returns for IBM, HP, and the S\&P 500 Index

Answer & Explanation Solved by verified expert

See Answer