Which trendline to use?

[Newroad]

Morning All.

A question for mathematicians and similar out there.

I unitise my portfolios twice monthly (mid month then end month) and am trying to figure out which trendline is the most appropriate to use in Excel to represent them. The choices are here



If your answer is "Polynomial", the Order can be 2-6. If you answer is Moving Average, the Periods can be any number of months.

Any guidance appreciated.

Regards, Newroad

[dealtn]

A question for mathematicians and similar out there.

It depends what you are measuring by the points. I assume one axis is time but what is the other? Price? Total Return? Portfolio value.

What are you trying to capture with your "trendline"?

It's not a simple answer without knowing what you are wanting. If it were me and I was simply measuring the value of a portfolio over time, with the caveat I wasn't adding to it (new capital), or subtracting from it (drawing an income), then assuming it was linear scaled axis I would want an exponential trendline to reflect the expected compounding.

I would more likely have a non-linear axis and reflect that compounding with a "linear" trendline.

[doug2500]

I'd imagine it's unit value, that's what I use for my unitised portfolio.

I'm interested in the answer because I have a similar graph but no trend lines.

[GoSeigen]

If the portfolio were a bond with a fixed return then exponential would fit the growth curve exactly. I don't see why exponential should not also be used as an approximation for a more diversified portfolio.

GS

[Newroad]

Hi Dealtn et al.

It is a set of unitised portfolios, but for purposes of discussion, you may consider it as a single one.

New money does go in, but at the prevailing unit value as per normal accumulation unit process. So, time on the x-axis and unit price/value on the y-axis.

I hope that clarifies adequately?

Regards, Newroad

[dealtn]

Hi Dealtn et al.

It is a set of unitised portfolios, but for purposes of discussion, you may consider it as a single one.

New money does go in, but at the prevailing unit value as per normal accumulation unit process. So, time on the x-axis and unit price/value on the y-axis.

I hope that clarifies adequately?

Regards, Newroad

My answer stands in that case.

[Newroad]

Thanks Dealtn et al.

That all makes sense - please see below the outcome



It looks a lot like a line of best fit - let's hope it one day again looks more like the exponential curve hoped for That said, the trend from mid 2022 seems OK'ish.

On an unrelated note, it is interesting to see the performance of my experimental pesudo-HYP (P-HYP in the chart) trailing off.

Regards, Newroad

[servodude]

Thanks Dealtn et al.

That all makes sense - please see below the outcome



It looks a lot like a line of best fit - let's hope it one day again looks more like the exponential curve hoped for That said, the trend from mid 2022 seems OK'ish.

On an unrelated note, it is interesting to see the performance of my experimental pesudo-HYP (P-HYP in the chart) trailing off.

Regards, Newroad

One thing I like to do in this kind of thing is look at how the trend changes.

In Excel you can use a "spinner" to increment a field.
Use this field to extract a subset of your data: e.g 12 months
Plot this on top your your data and apply the trendline of your choice to it
Then use the spinner to move that window and watch your trend change and see the parts of the data it is using

It can help highlight and quantify "periods of interest"

This page https://www.pk-anexcelexpert.com/fully-dynamic-chart-in-excel-with-scroll-bar-and-spin-button/ seems to cover the form tools used

[Lanark]

A moving average over 3 or 4 points can be useful, but excel will not give a future forecast from a moving average for the good reason that you cannot predict the future by looking out the rear window. Imagine driving your car by looking backwards and holding the steering wheel behind your back, it may seem fine for a little while but eventually you will meet a sharp corner or a T-junction.

None of the others are appropriate for stock prices because even though they may give the appearance of it, they don't follow any strict mathematical pattern.

Any option where Excel offers a future forecast should be ringing a warning bell that this is not a mathematically valid analysis for what is a very unpredictable set of data.

[MDW1954]

Any guidance appreciated.

Regards, Newroad

You have participated in a similar discussion before, although with a different objective.

Given what you have said, I would select "logarithmic".

See:

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=33722&hilit=llls

Basically, as you know, a logarithmic trendline will tell you how far you are deviating from a consistent percentage y-o-y growth rate.

MDW1954

[Newroad]

Thanks for reminding me, Malcolm.

I had forgotten. As you say, a slightly different goal, but with some similarities.

I started thinking about this standalone, mainly because the line of best fit didn't seem that useful to me.

Regards, Newroad

[Newroad]

Hi Servodude.

That spinner/scroll-bar combination is interesting and likely helpful if it would work as hoped - which from your experience suggests so.

I watched the video - I'll need a little time to get my head around it (and it appears that one needs to build it up from scratch - doesn't seem, easily at least, to be able to be retrofitted to an existing chart ).

Regards, Newroad

[1nvest]

Thanks for reminding me, Malcolm.

I had forgotten. As you say, a slightly different goal, but with some similarities.

I started thinking about this standalone, mainly because the line of best fit didn't seem that useful to me.

Regards, Newroad

Roughly approximating your data series and log linear regression (LLR) =linest(ln(a1:a46)) suggests a -0.135% slope whilst CAGR suggests -0.038%. The y=mx+c slope closer fits with that LLR value

Code: Select all

100   
100.7   
100.7   
102.31   
103.78   
102.58   
101.61   
102.3   
103.1   
105.64   
103.91   
104.22   
105.91   
101.66   
100.26   
99.48   
97.75   
97.45   
100.88   
99.36   
98.47   
95.32   
96.85   
93.49   
92.24   
95.89   
97.75   
100.73   
97.45   
96.43   
93.45   
92.91   
94.65   
97.31   
97.77   
96.12   
96.12   
97.56   
99.53   
101.46   
98.3   
97.23   
98.53   
99.35   
98.8   
98.29   
   
-0.135%   =LINEST(LN(B1:B46))
-0.038%   CAGR


[servodude]

Hi Servodude.

That spinner/scroll-bar combination is interesting and likely helpful if it would work as hoped - which from your experience suggests so.

I watched the video - I'll need a little time to get my head around it (and it appears that one needs to build it up from scratch - doesn't seem, easily at least, to be able to be retrofitted to an existing chart ).

Regards, Newroad

OK as I find myself with nothing to do at the moment lets try an example:

I've mocked up some simple data and plotted it in the image below

I have also made an extra short series using indirect to extract a window of that data
Cell G3 contains

Code: Select all

=INDIRECT("a" & ($F3 + $H$1))

and H3 contains

Code: Select all

=INDIRECT("b" & ($F3 + $H$1))

these are then "filled down" beside the 1..12 numbers beside them in column F
(Those numbers are really just there to make the building of the indirect string as obviously done as possible)
it is just concatenating the letter with the result of that addition (incase anyone is not aware $ is used to keep that part of the cell reference constant)

Cell H1 is where I have told the scroll bar (via its "properties") to put its value (for the purposes of the calculations though this could just be a cell you type in)

I then select that window worth of data (both columns) and "paste special"->"new series" over the existing graph

Then add a trendline - extending by X days in to the future

And I can now drag that bar to see where the trend was for any 12 consecutive samples



I hope that makes sense

-sd

[Newroad]

Thanks, ServoDude.

I won't be able to try it in anger for about two weeks due to travels, but will report back when I do.

In the meantime, any chance you could add a second column of example data, if not too onerous? That closer mirrors what I need, i.e. multiple columns of data represented on a single graph, but with the trend line only on one.

Regards, Newroad