Using Excel Cell Formulas And Functions To Compute Answers

Using Excel Cell Formulas And Functions To Compute Answers To The

Using Excel Cell Formulas And Functions To Compute Answers To The

Develop a comprehensive analysis of stock returns, beta estimation, and stock valuation using Excel formulas and functions. Utilize downloaded historical data for Disney (DIS), Netflix (NFLX), and the S&P 500 Index (^GSPC) over a 5-year period from October 1, 2013 to September 30, 2018. Calculate monthly percentage returns based on opening and closing prices, incorporate dividends for stocks, and derive expected returns and risk measures. Use Excel’s regression tools for beta estimation and apply CAPM to determine required returns. Interpret your findings to assess potential investment decisions.

Paper For Above instruction

Introduction

Understanding the fundamentals of stock returns, risk metrics, and valuation models is essential in investment analysis. Central to this process is the computation of stock betas, which measure systematic risk relative to the market, and applying the Capital Asset Pricing Model (CAPM) to estimate the required return on equity. This paper demonstrates how to perform these calculations using Excel formulas and functions, supported by empirical data derived from Yahoo Finance. The chosen sample includes Disney (DIS), Netflix (NFLX), and the S&P 500 Index (^GSPC), with data spanning October 1, 2013, to September 30, 2018.

Data Collection and Preparation

The first step involves downloading monthly historical price data from Yahoo Finance for the specified stocks and index. Using the platform’s "Historical Data" feature, data is exported as CSV files, focusing on the Date, Open, Close, and Dividends columns. It is crucial to sort the dividend data chronologically and align it with the stock price data to accurately reflect dividend payments in return calculations. Only data from October 2013 to September 2018 are retained, and irrelevant columns are discarded for streamlined analysis.

Calculating Monthly Returns

Monthly returns are computed using the formula:

Return = (Close - Open + Dividends) / Open

This calculation is done for each month, incorporating dividends whenever applicable. For the S&P 500 index, dividends are included since they are already embedded in the data; for stocks, dividend data needs to be added explicitly. Using Excel, you can implement this formula across the dataset with relative cell references. For example, if Open prices are in column B, Close in column C, and Dividends in column D, the formula in cell E2 (for return) would be:

= (C2 - B2 + D2)/B2

Ensure to drag this formula down for all 60 months of data. The result is a series of monthly returns for each stock and the index.

Calculating Average Returns and Annualized Metrics

Once monthly returns are established, calculate the mean return for each asset using the AVERAGE function, such as:

=AVERAGE(range of monthly returns)

Convert the average monthly return into an Annual Percentage Rate (APR) by multiplying by 12, reflecting an annualized estimate. Express this as a percentage by formatting the cell accordingly. For example, if the average return is in cell F1, the formula would be:

=F1*12

This provides the expected annual return, a critical metric for investment decisions.

Estimating Risk: Standard Deviation

The volatility of each stock and the market is assessed via the standard deviation of monthly returns, computed using the STDEV.P function for population standard deviation:

=STDEV.P(range of monthly returns)

This measure indicates the degree of dispersion around the mean, offering insight into the investment’s risk profile.

Beta Calculation via Regression

Beta estimates the systematic risk relative to the market. In Excel, this can be derived using the SLOPE function, correlating stock returns with market returns:

=SLOPE(stock returns range, market returns range)

Alternatively, regression analysis with the LINEST function can provide beta, along with statistical significance measures. Using the S&P 500 as the market proxy, this procedure captures the covariance between the stock and the market, normalized by market variance. The resulting beta indicates how sensitive the stock’s returns are to market movements.

Estimating Required Return via CAPM

The CAPM formula used is:

Required Return = Risk-Free Rate + Beta*(Market Return - Risk-Free Rate)

Using a risk-free rate of 3% and a market return of 10%, the calculation for each stock is straightforward. For example, for Disney:

Required Return = 3% + Beta * (10% - 3%)

Compare this required return with the expected return derived from historical mean returns. If the expected return exceeds the CAPM-required return, the stock may be considered undervalued and a buy candidate; otherwise, it might be avoided or further scrutinized.

Decision and Investment Implications

Based on the calculations, if Disney’s average annual return is below its CAPM-based required return, investment may be deemed unattractive, signaling potential overvaluation or high risk. Conversely, if Netflix’s expected return exceeds its CAPM estimate, it could be a favorable investment. This analysis, integrating historical data, risk metrics, and valuation models, aids in making informed investment decisions.

Conclusion

Utilizing Excel’s formulas and functions facilitates precise and replicable computations of stock returns, risk measures, and valuation metrics. Incorporating dividends enhances accuracy, and regression tools enable robust beta estimation. This methodology provides a transparent framework for assessing stock performance and making rational investment choices rooted in empirical data.

References

  • Brealey, R. A., Myers, S. C., & Allen, F. (2017). Principles of Corporate Finance (12th ed.). McGraw-Hill Education.
  • Fama, E. F., & French, K. R. (1992). The Cross-Section of Expected Stock Returns. Journal of Finance, 47(2), 427–465.
  • Damodaran, A. (2012). Investment Valuation: Tools and Techniques for Determining the Value of Any Asset (3rd ed.). Wiley Finance.
  • Sharpe, W. F., & Alexander, G. J. (1990). Modern Portfolio Theory, 3rd Edition. Van Nostrand Reinhold.
  • Yahoo Finance. (2023). Historical Data for Stocks and Indices. https://finance.yahoo.com
  • Hull, J. C. (2018). Risk Management and Financial Institutions (5th ed.). Wiley.
  • Campbell, J. Y., Lo, A. W., & MacKinlay, A. C. (1997). The Econometrics of Financial Markets. Princeton University Press.
  • Benjamin, G., & Poon, J. (2013). Practical Financial Modelling with Excel. Wiley.
  • Fitzgerald, T., & Simser, C. (2020). Applying Excel for Financial Analysis and Modeling. Routledge.
  • StatSoft Inc. (2014). Statistica Data Analysis Software System. Tulsa, OK: StatSoft, Inc.

Related Assignments