I only have to do question number FOUR of the word problem.The question has to be answered in excelThe excel attached are the parts and data that my peers have been using. im sending it in case its need it.
The Fed Model Late afternoon on Jan. 6th, 2022, Mr. Javier Rodriguez, a former Florida International University MSF graduate and now a portfolio manager was sitting on the beach near Mar-a-Lago and reading the WSJ. Javier found that the U.S. 10-year Treasury note yield is 1.721%, and forward earnings yield of S&P 500 index is 3.38% on Jan 6th, 2022. Javier was thinking about the information content of the Fed Model and whether it helps predict bond yields, stock returns, and earnings growth rates. Fed Model The so-called Fed Model is a valuation methodology that links the forward earnings yield (E/P) of the stock market (typically the S&P 500 index) and the 10-year Treasury note yield (Y10). The original use of Fed Model is not clear. I/B/E/S has been publishing the relationship between the forward P /E of the S&P500 and the yield on 10-year notes since 1986 and calls such relationship the I/B/E/S Equity Valuation Model. It has been widely used by practitioners and academia to study the equity valuation and the comovement between the bond market and the stock market. MoneyWeek (April 28, 2010) reports that "The Fed model matters because important people use it. Analysts across the board, from JP Morgan, to ING, to Prudential – I could go on and on – use the Fed model in their calculations. ... The fact is, influential market players embrace the Fed model. Whether they truly believe in it or not is immaterial. The net result is that the Fed model is a significant valuation tool which prominent investors use to check whether they should be buying stocks or bonds. And when those inflection points come about, markets move. Because when the advocates say it's time to buy, a wave of trading orders are placed." The financial economist and analyst at Deutsche Morgan Grenfell, Dr. Ed Yardeni, is probably the first to name the relationship the Fed’s Stock Valuation Model (see Yardeni (1997, 1999)). It is interesting that the Fed model is never officially endorsed by the Fed; however, in the Humphrey–Hawkings report of July 22, 1997, the Fed states that “...the ratio of prices in the S&P500 to consensus estimates of earnings over the coming twelve months has risen further from levels that were already unusually high. Changes in this ratio have often been inversely related to changes in long-term Treasury yields ...” The Humphrey–Hawkings report also features a graph depicting the close relationship between the earning yield and 10-year Treasury note yield during the 1982–1997 period (see Figure 1). The former Fed chairman Alan Greenspan (2007) also seems to make reference to it in his memoir: “The decline of real (inflation adjusted) long-term interest rates that has occurred in the last two decades has been associated with rising price-to-earnings ratios for stocks, real estate, and in fact all income-earnings assets.” Equity Valuation and Long-Term Interest Rate Figure 1 Note: Earnings-price ratio is based on the I/B/E/S International, Inc. consensus estimate of earnings over the coming 12 months. All observations reflect prices at mid-month. Source: Board of Governors of the Federal Reserve System Monetary Policy Report to the Congress Pursuant to the Full Employment and Balanced Growth Act of 1978; July 22, 1997 http://www.federalreserve.gov/boarddocs/hh/1997/july/FullReport.pdf Pros and Cons The Fed Model is very simple and easily applicable. It is widely used by practitioners, academia, and policy makers. Figure 1 tends to support that earnings yield and long-term bond yield are highly correlated. Many empirical studies have been conducted by both practitioners and academia. However, the empirical evidence seems mixed (e.g., Yardeni, 1997 and 1999; Thomas and Zhang, 2008; Maio, 2013; Asness, 2003; Estrada, 2006 and 2009; among others). Arguments for the Fed Model are following: 1. Competing assets 2. Present value 3. Empirical evidence Arguments against the Fed Model are following: 1. Lack of theory 2. Lack of predictive power 3. Inconsistent empirical evidence P/Es & Yields on Major Indexes P/E RATIO DIV YIELD 12/31/21† YEAR AGO† ESTIMATE^ 12/31/21† YEAR AGO† Russell 2000 Index 663.37 n.a. 31.14 1.16 1.19 NASDAQ 100 Index 39.63 39.45 30.25 0.63 0.75 S&P 500 Index 29.33 40.40 22.82 1.26 1.60 † Trailing 12 months ^ Forward 12 months from Birinyi Associates; updated weekly on Friday. P/E data based on as-reported earnings; estimate data based on operating earnings. Source: Birinyi Associates WSJ Dec. 31, 2021 Questions: 1. Describe and discuss the Fed Model and provide supports on its pros and cons. 2. Provide the information about earnings yield of S&P 500 Index and 10-year U.S. Treasury note yield on March 31 2022, what is your predictions on the future earnings yield, 10-year U.S. Treasury note yield, stock market return, and aggregate earnings in the end of the year if you believe the Fed Model? Provide your nationale. 3. Collect annual frequency data on stock price (P) and earnings (E) of S&P 500 index, and long-term Treasury note yield (Y10) from 1871 to 2020 via Robert Shiller’s webpage, http://www.econ.yale.edu/~shiller/data/chapt26.xlsx and http://www.econ.yale.edu/~shiller/data/ie_data.xls. Estimate the mean, standard deviation, median, maximum, minimum, and first order autocorrelation for E/P, E10/P, Y10, S&P 500 Index return (R), EPY (E10/P-Y10), earnings growth rate () for the full sample, subsamples from 1871-1926, 1927-1952, 1953-2008, 2009-2020, respectively.[footnoteRef:1] Discuss the results. [1: E10 denotes the 10 year moving average earnings.] Table 1 Descriptive Statistics Sample Variable Mean STD Median MAX MIN Autocorr. 1871-2020 E/P E10/P Y10 R (E10/P)-Y10 Y10 1871- 1926 E/P E10/P Y10 (E10/P)-Y10 R Y10 1927-1952 E/P E10/P Y10 (E10/P)-Y10 R Y10 1953-2008 E/P E10/P Y10 (E10/P)-Y10 R Y10 2009-2020 E/P E10/P Y10 (E10/P)-Y10 R Y10 4. Compute the correlation between earnings yield (E/P and E10/P) and 10-year Treasury note yield (Y10). Treasury note yield (Y10) for the full sample, subsamples from 1871-1926, 1927-1952, 1953-2008, and 2009-2020, respectively. Discuss the results you find. Does the Treasury note yield positively or negatively correlated with earnings yield? How do we understand the relation? Corr. (sample period) E/P E10/P Y10 E10/P Y10 5. Run the following forecast egressions: Where is the continuously compounded market return, measured over k years in the future, where k = 1, 2, 5, and 10 years respectively. x is E/P, YG or yg constructed in Maio (2013). Report the estimated slope coefficient, t-statistic, adjusted R square for each regression for the full sample, subsamples from 1871-1926, 1927-1952, 1953-2008, and 2009-2020, respectively. Design a Table similar to Table 1 and report the results. Discuss the empirical results. What do we learn from the empirical results? Sample period Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) 6. Run the following forecast egressions: Where is the continuously compounded earnings growth, measured over k years in the future, where k = 1, 2, 5, and 10 years respectively. x is E/P, YG or yg constructed in Maio (2013). Report the estimated slope coefficient, t-statistic, adjusted R square for each regression for the full sample, subsamples from 1871-1926, 1927-1952, 1953-2008, and 2009-2020, respectively. Design a Table similar to Table 1 and report the results. Discuss the empirical results. What do we learn from the empirical results? Sample period Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) 7. Run the following forecast egressions: Where is the change of note yield (Y10,t+k), measured over k years in the future, where k = 1, 2, 5, and 10 years respectively. x is E/P, YG or yg constructed in Maio (2013). Report the estimated slope coefficient, t-statistic, adjusted R square for each regression for the full sample, subsamples from 1871-1926, 1927-1952, 1953-2008, and 2009-2020, respectively. Design a Table similar to Table 1 and report the results. Discuss the empirical results. What do we learn from the empirical results? Sample period Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) Adj. R2 Adj. R2 (t-statistics) (t-statistics) Note: All groups are required to work on question 1, 2, 3, and 4. Group 1 and 2 work on question 5, group 3 and 4 work on question 6, and group 5 and 6 work on question 7. The report is required using Excel. Students may consider using each Sheet for each questions. The first sheet should contain group name and group member names. References: Asness, C., 2003. Fight the FED