Fair Value Analysis
Intrinsic Generator
| Inputs | Growth Choices | |||||||
| Enter current level of index | 1951.13 | Historical earnings growth rate (last decade) = | 4.27% | H | ||||
| Enter percentage change in aggregate earnings in the S&P 500 | 0% | Bottom up forecasted growth rate (next 5 years) = | 10.00% | BU | ||||
| What proportion of earnings can companies continue to return? | C | Top down forecasted growth rate (next 5 years) = | 6.30% | TD | ||||
| Enter the proportion of these earnings that will be returned to stockholders | 91.02% | Fundamental growth rate (based on current ROE) = | 1.46% | FC | ||||
| What expected growth rate do you want to use for the next 5 years? | TD | Fundamental growth rate (based on average ROE) = | 1.45% | FA | ||||
| Expected growth rate in earnings for the next 5 years = | 6.30% | |||||||
| Enter current long term risk free rate = | 2.27% | ERP Choices (for index intrinsic value) | ||||||
| If you want to compute an intrinsic value for index, what ERP would you like to use? | Direct | Historical Equity risk premium (US) = | 2.00% | HUS | ||||
| ERP used to compute fair value of index (you can override this number) = | 6.33% | Historical Equity risk premium (Global) = | 4.00% | HG | ||||
| Expected growth rate in the long term (after year 5) = | 2.27% | Average implied ERP (last decade) = | 3.00% | ERP10 | ||||
| Average implied ERP (1960-Current) = | 1.00% | ERPLong | ||||||
| Base numbers | Historical High ERP (1960-Current) = | 2.00% | ERPHigh | |||||
| Base year earnings (adjusted for percentage change in B2) | 111.60 | Direct Input | 6.33% | Direct | ||||
| Base year cash flows (based on proportion specified in B3) | 101.58 | Proportion of Cash Returned to Investors | ||||||
| Current level | 91.02% | C | ||||||
| Historical average (last 5 years) = | 73.68% | Cash5 | ||||||
| Historical average (last 10 years) = | 84.37% | Cash10 | ||||||
| Direct input = | 80.00% | Direct | ||||||
| Intrinsic Value Estimate (based on your choice of ERP) |
|
|||||||
| 1 | 2 | 3 | 4 | 5 | ||||
| Expected Dividends = | $ 107.98 | $ 114.78 | $ 122.01 | $ 129.70 | $ 137.87 | |||
| Expected Terminal Value = | $ 2,227.47 | |||||||
| Present Value = | $ 99.43 | $ 97.32 | $ 95.26 | $ 93.24 | $ 1,565.83 | |||
| Intrinsic Value of Index = | $ 1,951.08 | |||||||
| Intrinsic Trailing PE = | 17.48 | |||||||
| Intrinsic CAPE (based on inflation-adjusted ten year average earnings) = | 23.61 | |||||||
| Percentage change in index from current level = | 0.00% | |||||||
Dividend on Shares
| Year | Dividend Yield | Buybacks/Index | Gross Cash Yield | Payout | Return on Equity | Net Profit Margin |
| 2001 | 1.37% | 1.25% | 2.62% | 77.43% | 11.92% | 5.27% |
| 2002 | 1.81% | 1.58% | 3.39% | 64.78% | 13.61% | 6.82% |
| 2003 | 1.61% | 1.23% | 2.84% | 57.74% | 16.79% | 7.69% |
| 2004 | 1.57% | 1.78% | 3.35% | 59.99% | 18.43% | 8.59% |
| 2005 | 1.79% | 3.11% | 4.90% | 80.01% | 18.43% | 8.74% |
| 2006 | 1.77% | 3.39% | 5.16% | 83.40% | 19.36% | 9.21% |
| 2007 | 1.92% | 4.58% | 6.49% | 115.53% | 16.36% | 8.05% |
| 2008 | 3.15% | 4.33% | 7.47% | 136.37% | 9.35% | 4.75% |
| 2009 | 1.97% | 1.39% | 3.36% | 65.82% | 12.60% | 6.26% |
| 2010 | 1.80% | 2.61% | 4.42% | 66.28% | 16.31% | 8.70% |
| 2011 | 2.11% | 3.56% | 5.67% | 73.91% | 16.65% | 9.16% |
| 2012 | 2.19% | 3.13% | 5.32% | 78.39% | 15.79% | 8.86% |
| 2013 | 1.89% | 2.88% | 4.77% | 84.00% | 15.73% | 9.39% |
| 2014 | 1.92% | 3.03% | 4.95% | 87.80% | 16.23% | 9.96% |
| Average: Last 10 years = | 5.25% | 84.37% | 15.68% | 8.31% | ||
| Average: Last 5 years | 5.03% | 73.68% | 16.14% | 8.48% | ||
Index at the start of the current quarter =2063.11Base for normalized Cashflow; Updated at the start of every quarter
| Updated at the start of every quarter | Last updated | 01-Jul-15 | |||||||||||||||||||
| Year | Market value of index | Earnings | Dividends | Buybacks | Stock Issuances | Cash to equity | Dividend yield | Buyback yield | Gross Cash Yield | Net Cash Yield | Book Value of Equity at start of year | Sales per share | Inflation rate | Inflation adjusted Earnings | |||||||
| 2001 | 1148.09 | 38.85 | 15.74 | 14.34 | 30.08 | 1.37% | 1.25% | 2.62% | 325.8 | 736.88 | 1.60% | 50.62 | |||||||||
| 2002 | 879.82 | 46.04 | 15.96 | 13.87 | 29.83 | 1.81% | 1.58% | 3.39% | 338.37 | 674.59 | 2.48% | 58.54 | |||||||||
| 2003 | 1111.91 | 54.69 | 17.88 | 13.70 | 31.58 | 1.61% | 1.23% | 2.84% | 325.72 | 710.81 | 2.04% | 68.15 | |||||||||
| 2004 | 1211.92 | 67.68 | 19.01 | 21.59 | 40.60 | 1.57% | 1.78% | 3.35% | 367.17 | 788.17 | 3.34% | 81.61 | |||||||||
| 2005 | 1248.29 | 76.45 | 22.34 | 38.82 | 61.17 | 1.79% | 3.11% | 4.90% | 414.75 | 874.32 | 3.34% | 89.21 | |||||||||
| 2006 | 1418.30 | 87.72 | 25.04 | 48.12 | 73.16 | 1.77% | 3.39% | 5.16% | 453.06 | 952.51 | 2.52% | 99.84 | |||||||||
| 2007 | 1468.36 | 82.54 | 28.14 | 67.22 | 95.36 | 1.92% | 4.58% | 6.49% | 504.39 | 1025.08 | 4.11% | 90.24 | |||||||||
| 2008 | 903.25 | 49.51 | 28.45 | 39.07 | 67.52 | 3.15% | 4.33% | 7.47% | 529.59 | 1042.46 | -0.02% | 54.14 | |||||||||
| 2009 | 1115.00 | 56.86 | 21.97 | 15.46 | 37.43 | 1.97% | 1.39% | 3.36% | 451.37 | 908.4 | 2.81% | 60.47 | |||||||||
| 2010 | 1257.64 | 83.77 | 22.65 | 32.88 | 55.53 | 1.80% | 2.61% | 4.42% | 513.58 | 962.71 | 1.44% | 87.83 | |||||||||
| 2011 | 1257.60 | 96.44 | 26.53 | 44.75 | 71.28 | 2.11% | 3.56% | 5.67% | 579.14 | 1052.83 | 3.03% | 98.15 | |||||||||
| 2012 | 1426.19 | 96.82 | 31.25 | 44.65 | 75.90 | 2.19% | 3.13% | 5.32% | 613.14 | 1092.37 | 1.77% | 96.82 | |||||||||
| 2013 | 1848.36 | 104.92 | 34.90 | 53.23 | 88.13 | 1.89% | 2.88% | 4.77% | 666.97 | 1116.81 | 1.53% | 104.92 | |||||||||
| 2014 | 2058.90 | 116.16 | 39.55 | 62.44 | 9.58 | 101.98 | 92.40 | 1.92% | 3.03% | 4.95% | 715.84 | 1165.8 | Trailing four quarter data for now. Will be updated to actuals in April 2015 | ||||||||
| Trailing four quarters | 111.60 | 40.85 | 60.73 | 10.39 | 101.58 | 91.19 | 693.22 | 1165.8 | Updated at the start of every quarter | Last updated | 01-Apr-15 | ||||||||||
| Last quarter | 107.63 | 39.33 | 62.08 | 10.39 | 101.40 | 91.01 | Updated at the start of every month | Last updated | 01-Apr-15 | ||||||||||||
| Market Cap | Index level | Unit adjuster | Dividends | Buybacks | |||||||||||||||||
| 31/12/2014 | 18245 | 2058.9 | 0.112847355 | 362.02 | 538.12 | ||||||||||||||||
| 2013 | 16495 | 1848.36 | 0.112055774 | 311.77 | 475.59 | ||||||||||||||||
| 2012 | 12742 | 1426.19 | 0.111928269 | 280.69 | 398.91 | ||||||||||||||||
| 2011 | 11385 | 1257.6 | 0.110461133 | 240.20 | 405.08 | ||||||||||||||||
| 2010 | 11430 | 1257.64 | 0.110029746 | 205.83 | 298.82 | ||||||||||||||||
| 2009 | 9928 | 1115.00 | 0.112308622 | 195.61 | 137.65 | ||||||||||||||||
| 2008 | 7852 | 903.25 | 0.115034386 | 247.29 | 339.64 | ||||||||||||||||
| 2007 | 12868 | 1468.36 | 0.114109419 | 246.59 | 589.12 | ||||||||||||||||
| 2006 | 12729 | 1418.30 | 0.111422735 | ||||||||||||||||||
| Ratio | |||||||||||||||||||||
| Index units adjuster = | 0.112847355 | ||||||||||||||||||||
| Computing the trailing 12 month number (S&P Data updated every quarter on March 15, June 15, Sept 15 and Dec 15) | |||||||||||||||||||||
| Quarter | Earnings | Dividends | Buybacks | Issuances | |||||||||||||||||
| Last quarter | 228.36 | 93.55 | 144.13 | Updated July 1, 2015 | |||||||||||||||||
| 2 quarters ago | 237.14 | 92.8 | 132.63 | ||||||||||||||||||
| 3 quarters ago | 262.92 | 89.02 | 145.19 | ||||||||||||||||||
| 4 quarters ago | 260.5 | 86.65 | 116.17 | ||||||||||||||||||
| Sum | 988.92 | 362.02 | 538.12 | 84.87 | |||||||||||||||||
| S&P Capital IQ data (updated every month) | |||||||||||||||||||||
| Month | Earnings | Dividends | Buybacks | Issuances | |||||||||||||||||
| 7/1/15 | 953.729 | 348.51 | 550.08 | 92.07 | |||||||||||||||||
S&P
| This is what I get from S&P Capital IQ, if I use the raw data from individual companies. It does not match up to what S&P itelf reports once every quarter. Use it to look for trend lines. | |||||||||||||||||||||
| Aggregate (in US dollars) | In Index Units | ||||||||||||||||||||
| Month | S&P 500 Index | Market Cap | Dividends | Buybacks | Stock Issues | Net Income | Cash Return | Net Cash Return | Index Units | Market Cap | Dividends | Buybacks | Stock Issues | Net Income | Cash Return | Net Cash Return | Payout Ratio | Dividend Yield | Cash Return/Net Income | Net Cash Return/Net Income | |
| 01-Aug-15 | 2103.84 | 19,712,475 | 263808 | 423600 | 75426 | 923972 | $687,408 | $611,982 | 0.000106726 | 2103.84 | $28.16 | $45.21 | $8.05 | $98.61 | $73.36 | $65.31 | 28.55% | 1.34% | 74.40% | 66.23% | |
ORDER A PLAGIARISM-FREE PAPER HERE
We’ll write everything from scratch
Question 
Stock Index Fair Value Analysis
Many of topics in finance deal with individual security analysis, but there aren’t a lot of real robust forecasts about the fair value of market indices. 90% of returns in a portfolio come from asset allocation, or beta risk. Finding fair value is essential for any investor looking to value risk and expected return in the Stock market. Investing in a great company doesn’t make a lot of sense if the overall market is expected to fall, since usually all stocks fall when the stock market is overvalued. So, lets value the S&P 500 Index.
Fair Value Analysis
On 2/19/20 the S&P 500 closed at 3,386.
Because the index is composed of the largest companies in the U.S. with strict standards to be in the index, the S&P 500 is considered a perpetuity. The index will never go out of business because the S&P committee kicks out any declining stocks and replaces them with new profitable companies.
For the reasons the S&P 500 could be considered a growing perpetuity.
Growing Perpetuity Formula
P𝑃𝑃 = 𝐶𝐶1 (𝑟𝑟 − 𝑔𝑔)
PV = The present value of all future cash flows or the fair value for the investment
C1 = Next year’s cash flow – Use S&P operating earnings. Go to S&P 500 website for the Index earnings spreadsheet. Always use forward estimates.
r = required rate of return on S&P 500 – Your team will have to construct this rate using a risk-free rate and a estimated equity risk premium. Your team must show their work and sources for full credit.
g = Long term growth rate of C1 – Your team must come up with a best estimatie of the long term growth rate of the S&P 500 Index.
Using data from the S&P 500 website, especially the Index Earnings Spreadsheet, and market data, calculate the fair value of the S&P 500 index using the growing perpetuity formula.
- Make the model solve for the current S&P 500 price by modifying the inputs. As you may gather the long term data om the index you may have to use one or two plug figures.
Make sure that your inputs are all explained and the reasoning behind getting the data to populate the growing perpetuity model.
- Construct a S&P 500 ‘fair value’ model. It needs to solve for the PV of the S&P 500 Index
- Construct a Word doc explaining your calculations and sources of information to arrive at your teams fair
Dropbox all of your documents and spreadsheet.