Thread: Help needed working out formula from data table

Hi,

I'd be grateful if anyone is able to point me in the right direction or help me work out a formula based on a data table and it's inputs please.

There are only two inputs to generate the numbers, and the inputs and results are shown in the table below:

........8%.....9%...10%
5.0% 0.429 0.347 0.287
7.5% 0.890 0.720 0.596
10.0% 1.494 1.209 1.000
12.5% 2.233 1.806 1.494
15.0% 3.100 2.508 2.075
17.5% 4.092 3.310 2.738
20.0% 5.203 4.209 3.482

Sorry I haven't been able to create a table here, so have formatted it as best I can. Please let me know if I can provide any more information to assist with this.

Many thanks!

Given a finite number of data points, there exist and infinite number of formulas that will produce that result. In this case, you have 21 data points in two variables. You can produce 21 equations from those and so can write a formula having 21 coefficients and solve for the coefficients. That would be if you require a formula that gives those results exactly. There are many ways to produce simpler formulas that come close to those results.

Looking at plots of this data (which you should always do) it looks like 2 variable quadratic would be adequate, and a linear fit not particularly good.

But without knowing what you are trying to do and why this is pure speculation.

If we knew what this data represented then we might be able to make sensible suggestions.

CB

Hi,

Im a friend of ghelliwell's (who posted the orginal post).

The table is based on a formula in Richard Simmons' book "Buffett step-by-step. an Investor's workbook. Unfortunately we haven't been able to lay our hands on the book to see what the formula is.
I've attached a screen pic of part of the table, if that helps.


cheers

Originally Posted by NonMathsGuru

Hi,

Im a friend of ghelliwell's (who posted the orginal post).

The table is based on a formula in Richard Simmons' book "Buffett step-by-step. an Investor's workbook. Unfortunately we haven't been able to lay our hands on the book to see what the formula is.
I've attached a screen pic of part of the table, if that helps.


cheers

As has already been said, without knowing the underlying theory all that can be done is a data fitting exercise and you have already been told that a two variable quadratic looks like it would be adequate.

Go to your library and get the book on interlibrary load.

CB

Thanks for your reply CaptainBlack,

Forgive my ignorance, but I have no idea what a two variable quadratic is. If anyone could help us apply that to the table of values we have, that would be great.

The table represents a multiplier to use to determine intrinsic value of a stock, where a company retains 100% of its earnings. The Top row is the Investor's Required Rate of Return, the Left Column is the stocks Return on Equity. The table is based on a formula used by Richard Simmons in his book "Buffett step-by-step, An Investor's Workbook" but the formula have been slightly modified to increase the "margin-of-safety".

The author of the table has said that he won't be giving out the formula for the table values, citing that "its not required". However we’d like to know what the formula is.

Hope that helps a bit more

Thanks
Mike

Hi,

Doesn't look like anyone has noticed my previous post. Can someone please explain what a 2 variable quadratic equation is? I need to find the formula for a curve.
I have the following x, y co-ordinates

x y
5 0.429
7.5 0.890
10 1.494
12.5 2.233
15 3.1
17.5 4.092
20 5.203

Cheers

Originally Posted by NonMathsGuru

Hi,

Doesn't look like anyone has noticed my previous post. Can someone please explain what a 2 variable quadratic equation is? I need to find the formula for a curve.
I have the following x, y co-ordinates

x y
5 0.429
7.5 0.890
10 1.494
12.5 2.233
15 3.1
17.5 4.092
20 5.203

Cheers

1. Plot the data.

2. You also need a theory to suggest the correct form of the relationship.

3. In this case you should be looking at a quadratic in a single variable.

You should always do 1. and without 2. most things are pseudoscience. having done 1. does it look like what you expect from 2

CB

Postscript: If you wonder why you are not getting more help it is because when someone walks into the office/lab and asks for help fitting some data collected from souce unknown you can be sure of wasting many hours if not days of work and the supplicant leaving dissatisfied. Been there done that. You need to know what you expect and what would be adequate. It would also help if you could demonstrate some familiarity with Google at least to the level of >>this<<

Hi Guys.
Firstly thanks for your help in advance.

I too am attempting to work out this formula, however I am far from a math boffin.

I'm hoping I can shed more light on how the table works. The table is actually only half of the picture, there are actually two tables. One table is based on the company paying no dividends and reinvesting all profit, the other table assumes all profit is paid to investors.

The theory behind it is that we have return on equity being divided by the investors required rate of return. This gives us a multiplier which we multiply by the company's total equity to give us an intrinsic value for which we can use as a guide to what to pay for a company. That is the principle used if the company pays all profit to the investor.

The table we need the formula for is the table used when a company retains all profit for reinvesting. As such, there is a compounding nature to the multiplier (this is my assumption based on elementary math knowledge). If a company can reinvest the profit at consistently high returns, the multiplier is much larger and as such the company is worth more than another company which cannot reinvest at high rates. The multiplier also drops if the investors required return is higher (more conservative).


The first table we use is easy and I have replicated in minutes using excel. The second table is much harder without the formula.

Im pretty sure the tables you are quoting are from Roger Montgomery's book "Value.able". I finally just managed to get a hold of it myself, then after reading the tables and wishing that there was a formula, tried to look it up on the net. I found this post instead with other people with the same question...
Ive been playing with the tables in excel and finally got the formula.

There are two tables, the first more simple one - to value a company when all earnings are payed out as dividends is
Dividend * Return on Equity (ROE)/Required Return (RR)

The other table is for valuing a company where earnings are retained.
Retained earnings * (ROE/RR)^1.8

Use both formulas together to get the overall valuation

Value = D * ROE/RR + (E-D) * (ROE/RR)^1.8

where
D= dividend
E= earnings
(E-D)= retained earnings
ROE= return on equity
RR= required return


so to answer the original question, take the number from the row, divide by number from the column, then raised to the power of 1.8

Hope this has helped
Steve

steventhompson'sformula is not correct.

Following the information presented by Montgomery, instead of

"Dividend * Return on Equity (ROE)/Required Return (RR)"

it should be

Value = Equity " ROE/RR

For the other table, the formula agrees with some of the table, but diverges considerably for large parts of the table. What I think steventhompson meant to write, i.e.instead of "Retained earnings * (ROE/RR)^1.8", it should have been
Value = Equity * (ROE/RR)^1.8

However, I expect that this formula might be the formula presented by Richard Simmons (see Montgomery's book). Montgomery wrote that he used that formula, "and made it more conservative to raise Graham's 'margin of error'". Consequently, the formula presented by steventhompson is not only wrongly interpreted, but would not reproduce the results of the table presented in the original post because of this unknown adjustment made by Montgomery.

However, if we use steventhompson's line of reasoning, the combined formula would not have been

"Value = D * ROE/RR + (E-D) * (ROE/RR)^1.8"

but rather

Value = (Equity * ROE/RR) * (Dividend/ROE) +(Equity * (ROE/RR)^1.8) * (1 - Dividend/ROE)

Not that this matters, because the underlying assumption or deduction is wrong.

I hope this helps, but I expect it will just confuse.

Good luck!