Please answer question 1 with specific formulas for each cell :)

A Dividend Yields An investor wants to use historic data to calculate a reasonable 'Required Rate of Return" for his or her valuation calculations. The investor decides to use historical data for the S&P 500 Index over the last 50 years. The dates used were 12/31/1968 through 12/31/2018. Download the file "SP 500 History", and find the closing value of the SP 500 on these two dates. Then, using what you learned in module 3. calculate the average annual compounding rate of growth for the S&P 500 over that time period. 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 Closing Index value at 12/31/1968: Closing Index value at 12/31/2018: Number of years elapsed: Compounding Average Annual Growth Rate (from module 3): 50 2 The compounding average annual growth rate does not include any dividends paid out by the stocks of the S&P 500. To the right are the dividend yields (dividends / average stock price) for the S&P 500 stocks for the same years. Find the average dividend yield, and add it to the annual growth rate (which represents capital gains only) to get the APR 'Total Return'. Then calculate the periodic Total Return. 2009 2007 2006 2005 2004 2003 2002 2001 2000 1999 1998 1997 1996 1995 1994 1993 1992 1991 1990 1989 1988 Average dividend yield: Total Return (APR): Because dividends are paid quarterly, m = : Total Return (Periodic): 2.09% 1.84% 2.03% 2.11% 1.92% 194% 2.20% 2.13% 1.83% 2.024 3.23% 1.87% 1.76% 1.76% 1.62% 1.61% 1.79% 1.37% 1.22% 1.17% 1.36% 1.61% 2.00% 2.24% 2.89% 2.70% 2.84% 3.14% 3.68% 3.17% 3.53% 3.66% 3.33% 3.81% 4.58% 4.31% 4.93% 5.36% 4.61% 5.24% 5.28% 4.98% 3.874 4.15% 5.37% 3.57% 2.68% 3.10% 3.49% 3.47% 2.88% 3 The investor is estimating the value of a share of EXCO-a dividend-paying corporation. The investor decides to use the average annual Total Return for the S&P 500 as a valid estimate of the Required Rate of Return. Calculate the estimated price of Exco using the non-growth assumption at 111/2019. 1987 1.82 The most recent quarterly dividend (paid 12/31/18) was: Estimated Price: 4 Calculate the estimated price of this stock using the constant growth assumption 3.6% Assumed annual dividend growth rate: Assumed periodio dividend growth rate: Next dividend: Estimated Price: 1986 1985 1984 1983 1982 1981 1980 1979 1978 1977 1976 1975 1974 1973 1972 1971 1970 1969 1968 5 This investor is just beginning to save for retirement, planning to invest $500 per quarter, beginning today in an 'index fund' that provides the same total return as the SP 500 calculated above. If the investor plans to retire 40 years from today, how much money will he or she have at that time? 40 500.00 4 Balance at Retirement: 6 This investor wants more money than that at retirement! If the desired balance at retirement is $1,250,000, how much must the investor invest in the SP 500 index fund each quarter, beginning today, until retirement 40 years from today? 401 1,250,000 4 Required quarterly investment: A Dividend Yields An investor wants to use historic data to calculate a reasonable 'Required Rate of Return" for his or her valuation calculations. The investor decides to use historical data for the S&P 500 Index over the last 50 years. The dates used were 12/31/1968 through 12/31/2018. Download the file "SP 500 History", and find the closing value of the SP 500 on these two dates. Then, using what you learned in module 3. calculate the average annual compounding rate of growth for the S&P 500 over that time period. 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 Closing Index value at 12/31/1968: Closing Index value at 12/31/2018: Number of years elapsed: Compounding Average Annual Growth Rate (from module 3): 50 2 The compounding average annual growth rate does not include any dividends paid out by the stocks of the S&P 500. To the right are the dividend yields (dividends / average stock price) for the S&P 500 stocks for the same years. Find the average dividend yield, and add it to the annual growth rate (which represents capital gains only) to get the APR 'Total Return'. Then calculate the periodic Total Return. 2009 2007 2006 2005 2004 2003 2002 2001 2000 1999 1998 1997 1996 1995 1994 1993 1992 1991 1990 1989 1988 Average dividend yield: Total Return (APR): Because dividends are paid quarterly, m = : Total Return (Periodic): 2.09% 1.84% 2.03% 2.11% 1.92% 194% 2.20% 2.13% 1.83% 2.024 3.23% 1.87% 1.76% 1.76% 1.62% 1.61% 1.79% 1.37% 1.22% 1.17% 1.36% 1.61% 2.00% 2.24% 2.89% 2.70% 2.84% 3.14% 3.68% 3.17% 3.53% 3.66% 3.33% 3.81% 4.58% 4.31% 4.93% 5.36% 4.61% 5.24% 5.28% 4.98% 3.874 4.15% 5.37% 3.57% 2.68% 3.10% 3.49% 3.47% 2.88% 3 The investor is estimating the value of a share of EXCO-a dividend-paying corporation. The investor decides to use the average annual Total Return for the S&P 500 as a valid estimate of the Required Rate of Return. Calculate the estimated price of Exco using the non-growth assumption at 111/2019. 1987 1.82 The most recent quarterly dividend (paid 12/31/18) was: Estimated Price: 4 Calculate the estimated price of this stock using the constant growth assumption 3.6% Assumed annual dividend growth rate: Assumed periodio dividend growth rate: Next dividend: Estimated Price: 1986 1985 1984 1983 1982 1981 1980 1979 1978 1977 1976 1975 1974 1973 1972 1971 1970 1969 1968 5 This investor is just beginning to save for retirement, planning to invest $500 per quarter, beginning today in an 'index fund' that provides the same total return as the SP 500 calculated above. If the investor plans to retire 40 years from today, how much money will he or she have at that time? 40 500.00 4 Balance at Retirement: 6 This investor wants more money than that at retirement! If the desired balance at retirement is $1,250,000, how much must the investor invest in the SP 500 index fund each quarter, beginning today, until retirement 40 years from today? 401 1,250,000 4 Required quarterly investment