Need Help With This Assignment?

Let Our Team of Professional Writers Write a PLAGIARISM-FREE Paper for You!

SUBMIT YOUR ASSIGNMENT

Accounting, Math/Economics/Statistics - Excel Based

Fair Value Analysis

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

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.

  1. 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.

  1. Construct a S&P 500 ‘fair value’ model. It needs to solve for the PV of the S&P 500 Index
  2. Construct a Word doc explaining your calculations and sources of information to arrive at your teams fair

Dropbox all of your documents and spreadsheet.