Need Help with LOGEST problem

[KathyC]

Hi.

My spreadsheet has an embedded chart that shows a stock's price over time. So the X axis shows dates and the Y axis shows price. Series 1 is Price. I let the user add other series of price CAGR (compounded annual growth rate); there's High/Low/Current/Other1/Other2/AVG. And AVG is the problem.

I'm using LOGEST(known Ys, [known Xs], [const], [stats])
like this:
AVG=LOGEST($U$23:INDIRECT($AF$32),$W$23:INDIRECT($AF$35),TRUE,FALSE)-1

which gives me the AVG cagr (which I guess is really slope of line? When it comes to statistics/math, I'm horrible, sorry). Known Ys = start:stop price and known Xs = start:stop yearfrac. I originally tried using the date column for known Xs, but got a flat line.

And then to calculate each point on that line, I use:

start price * (1+CAGR) ^time passed since start date
=INDIRECT(AF$32)*(1+$F$8)^$W4

I have quadruple checked all the values in the cells and they are correct.

Attached is a screen shot of a chart. The CAGR lines are color coordinated with the data on the left side, so AVG is yellow.

So what's hapening? The AVG looks nowhere near correct does it? On some stocks it turns out LOWER than the Low CAGR. Now, I realize that the user entered hi/low may not actually be the statistically correct hi/low, but should it be that far off?

Does the fact that the time passed since the first date = 0 screw things up? If so, anyone know if it's valid statistically to skip that first month and start the AVG calculation from the next month?

I've also wondered if it's statistically valid to just add hi/low together and divide by 2. I coded it as the ALT CAGR button, and while that's much, much closer to looking right, it still doesn't look perfect.

Thanks for any help!
Kathy

[MrShorty]

It's kind of hard to pick out exactly what you are doing. I know next to nothing about business math, and your screenshot is too small to read the numbers effectively. One somewhat obvious "error" in your approach that might explain what you are seeing.

LOGEST returns two values (m,b) for the equation y=b*m^x. The equation uses a least squares algorithm to determine m and b that give the best fit of the input data (couldn't understand whether you were using the entire data set as input data, or a limited selection of the data set). When used as you're using, all you are returning is the m parameter.

Then, when caculating the line, you use known start price instead of LOGEST's "calculated" best fit for the start price.

I think this oversight might explain the problems you've had (including why using the actual dates gave you a flat line. In this case, the calculated start price=the hypothetical price the stock would have had in 1900 if using the 1900 date system).

How best to correct the oversight depends on exactly what you want to do. Based on your calculation for the line, it appears that the equation you really want from LOGEST is y=knownstartprice*m^x. To get this, you need to rearrange the equation (y/knownstartprice=m^x). Known_y's need to be (y/startprice), known x's as you have them, and put FALSE in for the third argument.

Alternatively, use LOGEST as an array function as it was intended (see Excel Help for LOGEST), and use the "best fit" start price instead of the actual start price when calculating the line.

[KathyC]

Hi MrShorty!

Thanks for responding...my apologies for the delayed response.

I had to reduce the screenshot that much to get it down to an upload-able size. Rats! I had hoped it would be more useful.

I am using a subset of the available data, but only the start date/price will change. The range will extend to the most current date.

Currently I'm using "m", which I understand to be the slope of the line, as the growth rate. Maybe I am misunderstanding that? Very possible. I didn't think I needed "b".

All these CAGR lines I'm plotting must start at the same date/price to have a valid comparison so using a different start price would not be correct for this.

Using your suggestion of dividing known Ys by start price:
=LOGEST((U23:INDIRECT(AF32)/INDIRECT(AF32)), T23:INDIRECT(AF31),FALSE)

I get a result that's just as whacky as my first one...instead of being under the Low CAGR (14.10%), this is over the High (56.17%) at 100.01%. So that can't be right either.

It's very frustrating!

You know, when I add an exponential trendline to a partial set of data, I get a very appropriate line.

I thought about temporarily creating a trendline, trying to grab that formula and use it (somehow, cause I really don't know/understand what that formula is!) to create the AVG line (which would then have individual data points which the trendline doesn't). But when I search around for how to get the formula, the responses seem to all say "Why don't you use LINEST or LOGEST?"

Any other ideas??

Thanks,
Kathy

[MSP77079]

Hi Kathy,

Before diving into the math, my question would be ... what is it you really want to depict?

In my pea-sized brain, the average growth rate is something like the baby-blue or pink lines. In other words, yes the growth was very high for several decades (I assume those are years on the x-axis), but it was also negative for a time, and if a person purchased the stock in year zero and held it until today, their average performance would be the blue or pink line. No?

So, if that is not the average you seek, then what is? Once I know what you are looking for, I am pretty sure that we can get it.

I would not plot the series, though. I agree with you that what the user wants is "m", not "b". But, I also agree with Mr Shorty ... what the LOGEST is returning does not ignore "b" ... so, by calculating "m" using LOGEST, then forcing "b" to be the starting price of the stock (which will, by definition, be lower than the average price of the stock, unless this stock is a total loser), you are forcing a line that runs very much lower than the stock's actual performance.

In other words, just look at the data ... if you "eyeball" an average line through those data, it will most obviously not cross the x-axis at the same place that the other curves do.

[KathyC]

Hi MSP!

Thanks for chiming in, esp. if it's as nice a day there as it is here (70s in NJ in Jan!) and you'd rather be outside!

If your brain is pea-sized I'd hate to describe mine <groan>!

Anyway, my problem with this AVG is that it plots lower than the LOW (pink line). Now, I selected that low point, but there's no lower slope for that range. So how could AVG be lower? It just doesn't make sense to me.

When I created a separate chart with just the selected data and then ran a trendline through that, it looks great. Now, I need it to start at the actual start date/price. If I were to swing the bottom of the line down/right to the start point, the top of the line would swing left and give the angle I expected.... I think <grin>.

So maybe that's the part "b" plays? I don't know. My mind just swirls around this since I have no math background... I was an English major for god's sake!

Kathy

[MSP77079]

Forget the math for a moment. Think about this as a communication issue, but you need to communicate non-verbally. Indeed, graphically. (I recognize that there was no verb in that there sentance.)

What I am suggesting is that the line should be the pink line. That is the average yield for this stock. That is the business case. If you disagree, please explain why.

That is easy to graph, you have already done it. It is the pink line. Now all you need to do is to back-calculate the "m" for that line.

Here is the math:
firstPrice(m^years)=lastPrice
m^years = lastPrice/firstPrice
years*ln(m) = ln(lastPrice/firstPrice)
ln(m) = [ln(lastPrice/firstPrice)]/years
m = exp([ln(lastPrice/firstPrice)]/years)

Mr. Shorty can tell us if I made a mistake there.

__________ added later __________
An example:
firstPrice 1
lastPrice 100
years 50
m 1.096478196

This says that the average yield for the stock over 50 years was 9.6% per year.

I named the cells containing the variables and used this formula in the cell for m:
=EXP((LN(lastPrice/firstPrice))/years)

[KathyC]

OK, so the slope of the line is correct from LOGEST because it's not supposed to be in the middle (like an average or median); it's taking into account all the negative growth cagrs. And the more negative growth time periods there are, the lower the growth rate and the lower the line appears. Which is why on a very steady upward-moving stock, like MMM, the LOGEST line actually goes through the data a lot more.

Seems simple enough, but I somehow just kept fixating on the positive CAGRs...If hi=56 and low=14, then AVG had to be in the middle somewhere, not lower than 14...I was discounting negative growth rates in the calculation.

And just to confuse myself even more, on some charts like MSFT, I was plotting a Hi CAGR that really wasn't near the high at all in order to cut out the tech bubble. Now I knew that the AVG was using ALL the price points ... really I did...but there was some disconnect in my brain that still said the yellow line needed to be in the middle between my hi/low lines. Of course that was never going to happen.

So now what I've decided to do is leave the lovely LOGEST alone and display it as the default and offer a command button that toggles over to a simple AVERAGE for those instances where you're ignoring so much history that LOGEST just doesn't make sense.

MSP I hope I've got it right now. Thanks so much for taking the time to help me understand.

MRSHORTY Thanks to you to.

Kathy