From Earnings Yield

Research on Safe Withdrawal Rates

Moderator: hocus2004

JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

From Earnings Yield

Post by JWR1945 »

From Earnings Yield

A straight line provides an excellent fit for a scatter plot of Historical Database Rates versus Earnings Yield (as measured by the inverse of P/E10). This allows us to calculate Safe Withdrawal Rates as a function of valuations.

The Portfolios

I have used the Retire Early Safe Withdrawal Calculator, Version 1.61, November 07, 2002 (with my modifications to make data reduction easier) to calculate Historical Database Rates for two portfolios. HDBR50 consists of 50% stocks and 50% commercial paper. HDBR80 consists of 80% stocks and 20% commercial paper.

Both portfolios were re-balanced annually. In both cases the Historical Database Rates were for a 30-year duration. In both cases the rates were determined with a precision of 0.1%. The rates are based upon a percentage of a portfolio's initial balance. A portfolio would have survived for the full 30 years at the Historical Database Rate, but it would have failed (i.e., the balance would have fallen to zero or become negative) at a withdrawal rate that was 0.1% higher. Withdrawal amounts were varied to match inflation (as measured by CPI-U). Expenses were set to 0.20% of the portfolio's current balance. I set the initial balance to $100K to minimize the effects of round off errors. Other calculator settings were left at their default values.

I have posted these Historical Database Rates. See the New HDBR Tables from Sunday, Jan 11, 2004 at 2:41 pm CST.
http://nofeeboards.com/boards/viewtopic.php?t=1962

I have restricted my investigations to portfolios beginning in the years 1921-1980. In fact, I have excluded the years 1921 and 1922 in most cases to get a better curve fit. Valuations were exceptionally low during those years. In the years prior to 1921, there is a qualitative difference in how Historical Database Rates and P/E10 behaved. Some of this can be traced to how commercial paper performed. In the late 1800s, for example, commercial paper by itself generally supported withdrawal rates of 6% or more.

The Plots

I have used Excel to make scatter plots of Historical Database Rates versus the Earnings Yield percentage 100/[P/E10], to calculate the best curve fits using the straight lines and to calculate R squared (i.e., the square of the correlation coefficient).

I used the equations for the straight lines to calculate standard deviations. I put the values of earning yield into the equations (of the straight lines), subtracted each Historical Database Rate for the same year (and the same earnings yield) and squared the difference. I added all of these values together and divided by the effective number of degrees of freedom. Finally, I took the square root.

I used the 90% confidence intervals appropriate for a Gaussian (or normal) distribution.

With the 50% stock portfolio, the Historical Database Rate (HDBR50) equation is HDBR50 = 0.3979x+2.6434%, where x = 100*(E10/P) or 100/[P/E10] = the earnings yield in percent and R squared equals 0.6975. When using this equation, the standard deviation of HDBR50 is 0.6178. The 90% confidence limits are plus and minus 1.01% of the calculated value.

With the 80% stock portfolio, the Historical Database Rate (HDBR80) equation is HDBR80 = 0.6685x+1.6424%, where x = 100*(E10/P) or 100/[P/E10] = the earnings yield in percent and R squared equals 0.7274. The standard deviation of HDBR80 using this formula is 0.9649. The 90% confidence limits are plus and minus 1.58%.

Statistical Considerations

I used 58 as the effective number of degrees of freedom (or 56 when data from 1921 and 1922 were excluded). The curve fit requires two degrees of freedom, one for the slope and the other for the intercept. (If only a mean were extracted from the data, the number of degrees of freedom would have been 59.)

Since there are 60 data points (or years for starting a retirement from 1921-1980), it is clear that I have treated each year as statistically independent. They are, in fact, very close to being independent. The fact that the sequences overlap strongly is only a secondary consideration. The reason is that the randomness comes from the percentage earnings yield, 100/[P/E10]. The earnings component of P/E10 is relatively stable because E10 is the average of ten years of (trailing, real) earnings. The randomness comes almost entirely from price fluctuations. In the very short-term, price fluctuations are very close to being entirely independent. It is only over longer periods that mean reversion (as properly defined and quantified by raddr) reduces the randomness.

It is necessary to use a confidence level lower than 100% whenever there is an element of chance. If I recall correctly, William Bernstein advocates a level around 80% to 85% for a variety of practical reasons. The bell curve (or the normal distribution or the Gaussian distribution) is an excellent approximation as long as you do not set the confidence levels too high. There are sound mathematical reasons for this. But whenever you look at confidence levels greater than 90% to 95% (i.e., 1.64 sigma or 2 sigma), the statistical assumptions fall apart.

If you talk to a professional statistician, he will tell you that using the bell curve is usually OK, but that the confidence levels of the actual distribution are almost certain to differ somewhat. When I calculate a 90% confidence level, the real number (in an idealized, theoretical sense and which I am unable to calculate) may be something like 82% or 93%. But the answer is likely to be good enough for our purposes.

Applications

These predictions contain an element of uncertainty, which I identify by using confidence limits. Each basic prediction has a 50% chance of being safe. At the lower confidence limit, at the calculated withdrawal rate a portfolio has a 95% chance of ending with a balance of zero or higher. At the upper confidence limit, at the calculated withdrawal rate a portfolio has only a 5% chance of ending with as much as a zero balance (and a 95% chance of running out of money before the 30 years are up).

We can use the equations directly. I have to introduce two new terms, the Zero Balance Rate and the Unsafe Withdrawal Rate. Zero Balance Rates are what the straight-line equations calculate. The Unsafe Withdrawal Rate corresponds to the higher of the 90% confidence limits. The more familiar term, the Safe Withdrawal Rate, corresponds to the lower of the 90% confidence limits. [Notice that the term Safe Withdrawal Rate is reserved for a calculated value derived from the historical data and presented in a statistical context.]

For example, with the 50% stock portfolio, the 90% confidence limits are plus and minus 1.01% of the calculated value. When the earnings yield is 2.5%, the Zero Balance Rate is 3.64% plus and minus 1.01%. The Safe Withdrawal Rate (at a 95% level of safety) is 2.63%. The Unsafe Withdrawal Rate (with a level of safety no greater than 5%) is 4.65%. A portfolio that withdraws at the Safe Withdrawal Rate or less has a 95% chance of having a balance of zero or greater after 30 years. A portfolio that withdraws at the Unsafe Withdrawal Rate or more has a 95% chance of running out of money before 30 years have ended.

An earnings yield of 2.5% corresponds to a P/E10 of 40. At the peak of the bubble P/E10 exceeded 40.

In 1929 the P/E10 was 27.0 and the calculated Zero Balance Rate for a 50% stock portfolio is 4.12% [or 4.1171037%] plus and minus 1.01%. The Safe Withdrawal Rate is 3.11%. The Unsafe Withdrawal Rate is 5.13%.

With the 80% stock portfolio, the 90% confidence limits are plus and minus 1.58%.

For 1929 with P/E10 equal to 27.0, the calculated Zero Balance Rate for an 80% stock portfolio is 4.12% [or 4.1183259%] plus and minus 1.58%. The Safe Withdrawal Rate is 2.54%. The Unsafe Withdrawal Rate is 5.70%.

Notice that the Safe Withdrawal Rate for 1929 was lower with the 80% stock allocation than with the 50% stock allocation. The Zero Balance Rates were almost identical. The Unsafe Withdrawal Rate was higher with 80% stocks. The actual 1929 Historical Database Rates of 4.4% (for 80% stocks) and 4.5% (for 50% stocks) fell within the confidence intervals.

Using the 90% confidence limits to isolate intervals, we can break the data into two or three distinct sections. Referring again to HDBR50 with 50% stocks and 50% commercial paper, when the earnings yield is 5%, then the Zero Balance Rate is 4.63% plus and minus 1.01% according to the curve. When the earnings yield is 10%, then the Zero Balance Rate is 6.62% plus and minus 1.01%. Those are two distinct sections. (P/E10 is 20 when the earnings yield is 5% and it is 10 when the earnings yield is 10%.)

When the earnings yield is 2.5%, the Zero Balance Rate (as projected) is 3.64% plus and minus 1.01%. When the earnings yield is 7.5%, the Zero Balance Rate is 5.63% plus and minus 1.01%. When the earnings yield is 12.5%, the Zero Balance Rate is 7.62% plus and minus 1.01%. (P/E10 is 40 when the earnings yield is 2.5%. P/E10 is 13.3 when the earnings yield is 7.5%. P/E10 is 8 when the earnings yield is 12.5%.)

These intervals can be applied in the opposite direction, going from Zero Balance Rates to their corresponding earnings yields. Breaking the Zero Balance Rates into two sections, a Zero Balance Rate of 4% requires that the earnings yield be constrained to the interval of 3.41% plus and minus 2.55%. A Zero Balance Rate of 6% requires that the earnings yield be constrained to 8.44% plus and minus 2.55%. (P/E10 is 29.3 when the earnings yield is 3.41%. P/E10 is 11.9 when the earnings yield is 8.44%.)

A Zero Balance Rate of 3% requires that the earnings yield be constrained to the interval of 0.90% plus and minus 2.55%. (The actual lower limit is zero.) A Zero Balance Rate of 5% requires that the earnings yield be constrained to the interval of 5.92% plus and minus 2.55%. A Zero Balance Rate of 7% requires that the earnings yield be constrained to the interval of 10.95% plus and minus 2.55%. (P/E10 is 111.1 when the earnings yield is 0.90%. P/E10 is 16.9 when the earnings yield is 5.92%. P/E10 is 9.1 when the earnings yield is 10.95%.)

These numbers can be used in design. For example, if I were planning to withdraw at a 5% rate, I should be talking about a P/E10 close to 16.9. If the earnings yield were 8.47% (= 5.92% + 2.55%) and P/E10 were 11.8, I would be highly conservative. If the earnings yield were 3.37% (= 5.92% - 2.55%) and the P/E10 were 33.4, I would be hopelessly reckless. Another way of saying almost the same thing is that you can withdraw 5% when valuations are normal. You can withdraw even more when P/E10 falls below 12. Closing your eyes and gritting your teeth won't hack it should you decide to withdraw 5% at today's valuations.

Have fun.

John R.
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

Hi John,

This looks interesting. :Up

I'm not a statistician, though and I don't understand what you are doing here:
JWR1945 wrote:I used the equations for the straight lines to calculate standard deviations. I put the values of earning yield into the equations (of the straight lines), subtracted each Historical Database Rate for the same year (and the same earnings yield) and squared the difference. I added all of these values together and divided by the effective number of degrees of freedom. Finally, I took the square root.
Is this how you derive the R^2?
Since there are 60 data points (or years for starting a retirement from 1921-1980), it is clear that I have treated each year as statistically independent. They are, in fact, very close to being independent. The fact that the sequences overlap strongly is only a secondary consideration. The reason is that the randomness comes from the percentage earnings yield, 100/[P/E10]. The earnings component of P/E10 is relatively stable because E10 is the average of ten years of (trailing, real) earnings. The randomness comes almost entirely from price fluctuations. In the very short-term, price fluctuations are very close to being entirely independent. It is only over longer periods that mean reversion (as properly defined and quantified by raddr) reduces the randomness.
I thought raddr showed that each adjacent year was in fact fairly closely related. Yes the price jumps or drops pretty much randomly, but it is rare that valuation will change from very high to very low in one year. He found the correlation tailed off after a few years, and then rose again after 15 or so, which he thought was because of mean reversion.

Thoughts? I can probably track down the post where he calculated those correlations.

Also, discarding the early years to get a better fit ... I understand a lot has changed since the first part of the 1900s, but it doesn't strengthen your argument to discard data.

Regards,
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

A straight line provides an excellent fit for a scatter plot of Historical Database Rates versus Earnings Yield (as measured by the inverse of P/E10)
When I looked at the bottom chart at:

http://www.retireearlyhomepage.com/pestudy1.html

...there seemed to be an inflection point around P/E 10 of 18 to 20 where the downward sloping red line should move an a slightly lesser angle. Of course, this is just an estimate based upon looking at the graph, not a rigorous calculation. Would it be possible to post your graph on NFB so that I can see it?
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

Mike wrote:When I looked at the bottom chart at:

http://www.retireearlyhomepage.com/pestudy1.html

...there seemed to be an inflection point around P/E 10 of 18 to 20 where the downward sloping red line should move an a slightly lesser angle.
It of course makes sense that the HDBR would go asymptotic approaching zero as PE-10 goes extremely high. Your maximum possible withdrawal rate cannot go negative without there being leverage employed.

Regards,
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

BenSolar, thank you for some excellent comments.

You are correct. There should be a correction to the number of degrees of freedom because of short-term correlations.

raddr calculated a series of autocorrelation functions of the stock market. They were positive with lags of one or two years. They were close to zero around three or four years and then they became negative and stayed negative thereafter. Positive correlation indicates that the variance is larger than calculated (i.e., before making any corrections to the number of degrees of freedom). Negative correlation indicates that the variance is smaller than calculated.

[The autocorrelation multiplies a function by itself after introducing a time lag and then sums all of the terms. That is, it is the sum of terms of the form f(t)*f(t+lag). It is an even function since the sum of f(t-lag)*f(t) is identical, provided that you place close attention to the details as specified by the mathematicians. The autocorrelation is largest when the lag equals zero. When the underlying function f(t) is entirely random, the autocorrelation at zero lag equals the variance. It is zero (theoretically, assuming an infinite amount of data) at all lags other than zero.]

If the returns of the first two years were always identical, the number of degrees of freedom would be cut in half and the variance would be twice the calculated value. If the first three years were always identical, the number of degrees of freedom would be cut to one-third and the variance would be three times the calculated value. This places an upper bound as to the size of the correction. In terms of standard deviations and confidence limits, these upper bounds are the square root of two or three.

From gummy's investigations, nominal prices are almost entirely random but real prices are not. That is why gummy uses actual inflation sequences in his Monte Carlo model. raddr's research used real prices. This means that inflation causes the short-term correlations in the data.

I do not know exactly what adjustment to make. Certainly, there is still a lot of randomness in prices from one year to the next, even in terms of real dollars. I simply identify the correction as less than a multiplicative factor of the square root of two.

I did not calculate R squared. Excel did. R squared tells you how much of the variance of the data can be explained by a curve. In the absence of a curve (actually, a straight line), the calculation would have consisted of the average of the Historical Database Rates and the spread about that average. The spread would be characterized by a variance (although its square root would usually be cited). The variance that I calculated is about 30% of that (since R squared is close to 70% for both HDBR50 and HDBR80). The straight-line equation explains 70% of the variation about the mean (in terms of the variance without a curve fit). The remaining 30% of the variation (as measured by the variance, not the standard deviation) is caused by random, unknown and other causes.

As for calculating variances, I take the sum of (measured data-theoretical result)^2 divided by the number of degrees of freedom. The theoretical result is what the curve says. For any given year, the measured data is the Historical Database Rate for that year. The theoretical result uses the earnings yield for that year and the equation for the straight line.

Excluding early years is appropriate because they are unlikely to be relevant to today's situation. Those earlier years were characterized by a steady decline in prices (usually as a result of improved productivity and technically not the same as deflation) based on a hard currency. The one thing that the Federal Reserve Board will not allow is prolonged periods of falling prices. They cite two reasons. First, they do not have reliable information about how best to react with declining prices. [Recent economic events have been far outside the range of almost all of their models. Safe Withdrawal calculations are far from unique in this respect.] The other is that they lose their ability to influence the economy when real interest rates fall below zero.

The years excluded would have allowed for safe withdrawals around 6% even without any kind of growth instrument. Commercial paper was sufficient all by itself. I doubt seriously that we will ever be able to withdraw anything close to 6% safely for thirty years from a money market account.

An additional note. The confidence limits around the Zero Balance Rates and the Earnings Yield percentages are related by the slope in the straight-line equations. In the equation for HDBR50, the slope was 0.3979 and the confidence limits were plus and minus 1.01%. The earnings yield confidence limits of plus and minus 2.55% were calculated using 1.01%/0.3979. [There is a round off error. The ratio is 2.54%. A more precise calculation produced 2.55%.] Similar calculations apply to HDBR80.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

When looking at the relationship between Historical Database Rates and P/E10, it is important to focus on the earnings yield (100/[P/E10] ) instead of P/E10.

There is a theoretical reason for doing so. Earnings yield support dividends and dividends support Safe Withdrawal Rates.

Doing this avoids all sorts of difficulties.

Have fun.

John R.
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

JWR1945 wrote:When looking at the relationship between Historical Database Rates and P/E10, it is important to focus on the earnings yield (100/[P/E10] ) instead of P/E10.

There is a theoretical reason for doing so. Earnings yield support dividends and dividends support Safe Withdrawal Rates.

Doing this avoids all sorts of difficulties.
While speaking in terms of earnings yield makes the relationship clearer, PE-10 and earnings yield are essentially the same, eh? :?

So where I talked about PE-10 going very high and theoretical SWR approaching zero, then equivalently I can as earnings yield approaches zero, so does SWR.

I do agree talking about earnings yield makes things clearer.
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

BenSolar wrote:So where I talked about PE-10 going very high and theoretical SWR approaching zero, then equivalently I can as earnings yield approaches zero, so does SWR.

I do agree talking about earnings yield makes things clearer.
My emphasis is in terms of asymptotic behavior and the like. Looking at P/E10, you have no clear-cut rationale when extrapolating to today's valuation levels. By thinking in terms of earning yield, you gain clarity and insight.

BTW, I did not appreciate the advantage of thinking in terms of earnings yield prior to the middle of February.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

This is in response to comments that should have been made on this board.

I have excluded the two most extreme outliers, which is not an uncommon procedure. They could have been left in. It does not make much of a difference. From a theoretical standpoint, those data points simply showed that the two most favorable valuations did not persist long enough to raise the Historical Database Rates higher. If your primary focus is directed to periods of high valuations, excluding a small part of the low valuation data is reasonable.

This reasoning is wrong.
The bottom line is that when each data series shares 29 out of 30 datapoints with its neighbor you don't get a Gaussian distribution as a result and descriptive statistics can't be applied with any precision.
What is relevant is the behavior of P/E10, not Historical Database Rates. It is true that P/E10 varies slowly. That is, valuations have changed gradually over time. Some of the scatter in the data is associated with price fluctuations as such. Some is the result of sampling as valuations vary.

As terms of making an adjustment, I have addressed this issue in my response to BenSolar's initial comments.

This issue is interesting since it may point us in the direction of greater predictability. There is a lot of short-term predictability for P/E10 multiples. Valuations change slowly over time.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Here are two more ways of looking at the degrees of freedom issue that BenSolar brought up in his initial post.

Overlapping Data

From Biggalloot's research as reported at the Motley Fool, Historical Database Rates are essentially independent after 7 years.

Let us assume that there are only 8 data points in the Historical Database Rates from 1921-1980. That is, 7 years per data point times 8 data points = 56 years of data, which is close to 60 years.

Then there are 7 values of earnings yield 100/[P/E10] to estimate each of these points. We lose one degree of freedom because we use the data to calculate (the equivalent of) a mean in each case. That leaves us with 6 degrees of freedom per independent Historical Database Rate.

The total number of degrees of freedom equals 8 (essentially) independent Historical Database Rates times 6 degrees of freedom in estimating each of these rates. This totals 8*6 = 48 degrees of freedom total instead of 58.

Discernible Change

An alternative way of looking at this is to look at how big a price swing it takes to make a discernible change on the curve. To reach a 1% change in the Zero Balance Rate curve (which corresponds to Historical Database Rates) takes an earnings yield change of 2.5% with the 50% stock portfolio HDBR50. (That is, 2.5% = 1/[the slope of 0.3979] ).

If earning yield were to increase from 4% (with P/E10 = 25) to 6.5% and if E10 remained the same, prices would have to fall to 4/6.5 times their original level. Typically, that would take two years although it could happen in one.

This approach would widen the confidence limits by something less 40% (i.e., a multiplier of less than the square root of two).

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

This allows us to calculate Safe Withdrawal Rates as a function of valuations.
Is the earnings yield SWR calculation line about the same as what the dividend yield plus a little bit model calculates when extended to the recent historically unprecedented valuation levels?
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Mike wrote:Is the earnings yield SWR calculation line about the same as what the dividend yield plus a little bit model calculates when extended to the recent historically unprecedented valuation levels?
The equation for HDBR50 (with 50% stocks and a 30-year duration) is HDBR50 = 0.3979x+2.6434%, where x = 100*(E10/P) or 100/[P/E10] = the earnings yield in percent. This is always positive and HDBR50 = 2.6434% when x = 0.

The equation for HDBR80 (with 80% stocks and a 30-year duration) is HDBR80 = 0.6685x+1.6424%, where x = 100*(E10/P) or 100/[P/E10] = the earnings yield in percent. This is always positive as well. HDBR80 = 1.6485% when x = 0.

Both formulas have Zero Balance Rates very close to 4% when the earnings yields are 4%. At earnings yields below 4% (and P/E10 levels above 25) the formulas produce Zero Balance Rates greater than the earnings yield. Dividend yields must be lower for dividends to be sustainable. [The answer to your question is YES.]

[The slopes are less than 1.0 in the equations. (The slopes are 0.3979 for HDBR50 and 0.6685 for HDBR80.) The Zero Balance Rates grow more slowly than the earnings yield. Since the earnings yield and Zero Balance Rates are (approximately) equal at 4%, the Zero Balance Rates are higher than the earnings yields when they are between 0% and 4%.]

The Historical Database Rates were very close to the earnings yield at the highest valuations before the bubble. That is, when P/E10 = 25 and 100/[P/E10] = 4%. Earnings yields were were a little bit higher than dividend yields in the late 1960s. [There were some dividend cuts during the Great Depression. That is what makes us focus on the quality and sustainability of dividends.]

Both equations project a Zero Balance Rate that is less than 3.33% when the earnings yield equals zero. Assuming a real interest rate of 0% (i.e., the interest matches inflation exactly), cash would provide 3.33% for 30 years all by itself.

We see from the equations that owning stocks decreases the Zero Balance Rate below that of (inflation matched) cash. Price fluctuations reduce a portfolio's life span (when earnings yields are very low). Owning more stock (e.g., owning 80% stock in HDBR80 versus 50% stock in HDBR50) magnifies this effect.

[Note: the straight line is the Zero Balance Rate. The lower confidence limit is the Safe Withdrawal Rate. The upper confidence limit is the Unsafe Withdrawal Rate.]

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thank you John. I will copy these formulas into my notebook for further pondering.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

It is a good idea for you to know how I came up with the formulas. The equations come from standard, least-squares curve fitting formulas. Excel calculates them for us automatically.

Here are the steps.

Put the Historical Database Rate data into Excel. They are listed in this thread.
http://nofeeboards.com/boards/viewtopic.php?t=1962

Insert a column for the earnings yield. [Click Insert and then Columns. The new column appears to the left of the cell that you have highlighted.]

Calculate the percentage earnings yield in the new column. In my case P/E10 was in column B, starting with cell B601. I highlighted cell C601 and typed in the formula =100/B601 into the formula bar. Then I clicked the check mark. Finally, I used the fill handle to drag the formula into the rest of column C.

Highlight the area of interest. In this case it extends from column C, which has the percentage earnings yield, into columns D and E with the rates for HDBR50 and HDBR80, respectively. Click the first cell in column C, press down on the Shift key and click the last cell in column E.

Bring up the Chart Wizard. Click Insert and then Chart. Select the XY (Scatter) plot. Name the two series (in the Chart Source Data step). Under Chart Options, name the X and Y-axes, include Major Gridlines for the X-axis as well as for the Y-axis and place the legend at the bottom of the chart. Specify the Chart Source. I prefer making a new sheet. Then click finish. You will now have a new chart to work on.

You will want to make many formatting changes to suit your own tastes. Point your mouse button at various areas and click the RIGHT mouse button. It will present Formatting options associated with where your mouse is pointed.

When your mouse arrow points to a data point, you will be able to Format Data Series, which is a good idea. You will also be able to Add Trendline. Click the Add Trendline option. This identifies six curve Types. Select the Linear curve type. It is the default option. Click the Options tab. Put a check mark in Display equation on chart and Display R-squared value on chart.

Excel calculates the best straight-line curve for you. It displays the equation. It displays the value of R-squared.

As a practical matter, you will want to enlarge the equations and place them in a good location. You can do this by clicking on the equation. A box will appear. Right click to Format Data Labels. (You can do both steps at once by making the right click initially.)

From now on, when somebody mentions a curve fit, you will be able to do it your own. Excel makes it easy. [Of course, you have to learn how to use Excel in the first place, which is not easy.]

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thank you John. I made my first fledgeling chart with yield versus HDR50 using data up to 1902. I couldn't figure out how to paste it onto NFB. Perhaps if I fire up the HTML editor included with Office. Anyway, I now know how to make charts with linear curve lines. My Excel skills are slowly improving.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

I converted it into a HTML document and 3 GIF images, but could not figure out how to paste it onto the message. Oh well, another day perhaps. Time to go and enjoy the 80 plus heat outside.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Mike, you are way ahead of me when it comes to the HTML editor.

I know that there are difficulties in posting any kind of graphics. Some, if not all, of the limitations involve the amount of storage available.

Be sure to check with ElSupremo. He can give you all of the instructions that you need. Just send him a private message.

Have lots of fun.

John R.
User avatar
ElSupremo
Admin Board Member
Posts: 343
Joined: Thu Nov 21, 2002 12:53 pm
Location: Cincinnati, Ohio

Post by ElSupremo »

Greetings Mike :)
I converted it into a HTML document and 3 GIF images, but could not figure out how to paste it onto the message.
John is right. Storing and linking to images here at the site is resource intensive. Most of us work around this by putting images on personal web pages or wherever else we can and linking to them there. If you can't work anything out let me know and I'll try to find an alternate solution for you. For your HTML you could run it locally and then cut and paste it here. HTH
"The best things in life are FREE!"

www.nofeeboards.com
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

For your HTML you could run it locally and then cut and paste it here.
Ok. I am just playing with my new toys for now. My ISP offers a web page that I have never gotten around to using. I may play with that next, and try the cut and paste route. No earth shaking charts to post, just my experiments to figure out how to use them.

While watching 24, I generated a chart of P/E 10 versus nominal 10 year return. Y= -.105x+2.8406 R^2=.3754 The paucity of positive return data points over P/E 18 is striking. I used a log chart, I don't know if that makes any difference. I will get back to helping Jack Bauer save the world now.
Last edited by Mike on Mon Apr 19, 2004 11:11 am, edited 1 time in total.
Shakespeare
* Rookie
Posts: 3
Joined: Wed Apr 14, 2004 4:40 am

Post by Shakespeare »

I used a log chart, I don't know if that makes any difference.
The theory of least squares - at least the equations put in Excel - assigns all the error to Y and assumes it is normally distributed. If you use a log chart, you are assuming a log-normal distribution.
Post Reply