More fun with percentages
-
- Lemon Quarter
- Posts: 2223
- Joined: November 4th, 2016, 8:46 pm
More fun with percentages
I posted a request a few months ago about hard-coding a particular percentage calculation, and received some useful validation.
https://www.lemonfool.co.uk/viewtopic.php?f=27&t=11210
So I thought I'd crave your indulgence again...
Briefly, I'm trying to automate a routine for calculating the overall percentage rise (or fall) over those five-year time series that are common in the world of investing -- ie, an investment trust's percentage perfomance over (say) 2013, 2014, 2015, 2016, and 2017.
If the annual outcomes are all positive, the calculation is trivially easy. An IT delivering a consistent 10%, say, would have delivered an accumulatated 61.051% over the five years.
The problem comes when some annual outcomes are negative. Here, calculations involving multiplication produce "silly" results with the wrong sign.
If the sixth year is not +10% but minus 10%, for instance, the conventional multiplication (1 x 1.1 x 1.1 x 1.1 x 1.1 x 1.1 x (-1.1)) produces -1.771561 (wrong sign), ie 1.61051 x -1.1, rather than 1.61051 minus (1/10th of 1.61051 = 0.161051) = 1.449459.
So before I code something really clutzy, I thought I'd ask here to see if there's any more elegant solution. As ever, I'm aware that these calculations are not without their hidden dangers, and so I could be barking up the wrong tree altogether. So either way, validation or correction will be useful.
MDW1954
PS I've deliberately chosen to show the calculation above in its "long hand" form for clarity. I'm aware that (where the growth each year is always the same) I could have raised to the nth power, instead.
https://www.lemonfool.co.uk/viewtopic.php?f=27&t=11210
So I thought I'd crave your indulgence again...
Briefly, I'm trying to automate a routine for calculating the overall percentage rise (or fall) over those five-year time series that are common in the world of investing -- ie, an investment trust's percentage perfomance over (say) 2013, 2014, 2015, 2016, and 2017.
If the annual outcomes are all positive, the calculation is trivially easy. An IT delivering a consistent 10%, say, would have delivered an accumulatated 61.051% over the five years.
The problem comes when some annual outcomes are negative. Here, calculations involving multiplication produce "silly" results with the wrong sign.
If the sixth year is not +10% but minus 10%, for instance, the conventional multiplication (1 x 1.1 x 1.1 x 1.1 x 1.1 x 1.1 x (-1.1)) produces -1.771561 (wrong sign), ie 1.61051 x -1.1, rather than 1.61051 minus (1/10th of 1.61051 = 0.161051) = 1.449459.
So before I code something really clutzy, I thought I'd ask here to see if there's any more elegant solution. As ever, I'm aware that these calculations are not without their hidden dangers, and so I could be barking up the wrong tree altogether. So either way, validation or correction will be useful.
MDW1954
PS I've deliberately chosen to show the calculation above in its "long hand" form for clarity. I'm aware that (where the growth each year is always the same) I could have raised to the nth power, instead.
Last edited by MDW1954 on September 24th, 2018, 1:56 pm, edited 2 times in total.
-
- 2 Lemon pips
- Posts: 219
- Joined: November 4th, 2016, 6:35 am
Re: More fun with percentages
Geometric mean ?
And make +10% be 1.1 and make -5% be 0.95
And make +10% be 1.1 and make -5% be 0.95
Last edited by uspaul666 on September 24th, 2018, 2:01 pm, edited 2 times in total.
-
- Lemon Quarter
- Posts: 2223
- Joined: November 4th, 2016, 8:46 pm
Re: More fun with percentages
Well that's an interesting (an unexpected) reply! But maths-wise, that's way above my pay grade.uspaul666 wrote:Geometric mean ?
As modellingman knows, my maths is always quicker in the summer, because I don't have to first take my shoes off in order to gain access to my toes...
MDW1954
-
- 2 Lemon pips
- Posts: 219
- Joined: November 4th, 2016, 6:35 am
Re: More fun with percentages
Sorry, see the edit and use =GEOMEAN()
-
- Lemon Quarter
- Posts: 2223
- Joined: November 4th, 2016, 8:46 pm
Re: More fun with percentages
Aha! That's interesting (and obvious in retrospect!). Yes, 0.95 for minus 5% is the way to do it.
I'll read up on geometric means, but with (say) 0.95 instead of 1.05, the conventional multiplication method should work.
MDW1954
I'll read up on geometric means, but with (say) 0.95 instead of 1.05, the conventional multiplication method should work.
MDW1954
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: More fun with percentages
And presumably you only have the discrete annual % changes available? And not, say, the prices at the beginning of 2013 and end of 2017? (although for TR you'd need dividend payouts too, if it isn't an Acc type investment).MDW1954 wrote:Briefly, I'm trying to automate a routine for calculating the overall percentage rise (or fall) over those five-year time series that are common in the world of investing -- ie, an investment trust's percentage perfomance over (say) 2013, 2014, 2015, 2016, and 2017.
BTW, yes, making -5% be 0.95 is the way to go, but the start-to-finish rise/fall you are calculating is not a mean, geometric or otherwise.
The geometric mean would be the annualisation of the start-to-finish rise/fall, which is done by taking the nth root of it.
So, e.g., in your example of 1 x 1.1 x 1.1 x 1.1 x 1.1 x 1.1 x 0.9 = 1.449459 the geometric mean would be 1.449459^(1/6) = 1.06382. I.e. a 44.9% rise over 6 years annualises to 6.4%pa.
-
- Lemon Quarter
- Posts: 2223
- Joined: November 4th, 2016, 8:46 pm
Re: More fun with percentages
Yes, having considered things, I've also come to the conclusion that the geometric mean approach is not what want. What I want is the overall outcome of the various annual percentage changes.mc2fool wrote:BTW, yes, making -5% be 0.95 is the way to go, but the start-to-finish rise/fall you are calculating is not a mean, geometric or otherwise.MDW1954 wrote:Briefly, I'm trying to automate a routine for calculating the overall percentage rise (or fall) over those five-year time series that are common in the world of investing -- ie, an investment trust's percentage perfomance over (say) 2013, 2014, 2015, 2016, and 2017.
The geometric mean would be the annualisation of the start-to-finish rise/fall, which is done by taking the nth root of it.
So, e.g., in your example of 1 x 1.1 x 1.1 x 1.1 x 1.1 x 1.1 x 0.9 = 1.449459 the geometric mean would be 1.449459^(1/6) = 1.06382. I.e. a 44.9% rise over 6 years annualises to 6.4%pa.
Interestingly, I already have a calculation that, when given an overall increase and the requisite number of years, calculates the annual percentage growth that results in that overall increase. I just didn't realise it was a geometric mean! (Undergraduate stats was a long, long time ago... so long ago that we used slide rules for the exams.)
MDW1954
-
- Posts: 5
- Joined: November 5th, 2016, 3:37 pm
Re: More fun with percentages
Your conventional multiplication is using the wrong figure for -10%MDW1954 wrote: If the sixth year is not +10% but minus 10%, for instance, the conventional multiplication (1 x 1.1 x 1.1 x 1.1 x 1.1 x 1.1 x (-1.1)) produces -1.771561 (wrong sign), ie 1.61051 x -1.1, rather than 1.61051 minus (1/10th of 1.61051 = 0.161051) = 1.449459..
If you have 100 and lose 10% in a year, you have 90, not -110 so -10% multiplier is .9
(In general the multiplier is (1 +/- % value) so +10% is (1+ 0.1) -10% is (1 - 0.1)
100% increase is (1 +1)=2, ie. Double money, -100% is (1- 1) =0, wipeout.
M.
-
- Lemon Quarter
- Posts: 2223
- Joined: November 4th, 2016, 8:46 pm
Re: More fun with percentages
Yep, all understood now. Manually, I was getting the correct answer (and have done for years), but in the way I described, ie, manually deducting the negative growth (because multiplication didn't work).IrishIceHawk wrote:Your conventional multiplication is using the wrong figure for -10%MDW1954 wrote: If the sixth year is not +10% but minus 10%, for instance, the conventional multiplication (1 x 1.1 x 1.1 x 1.1 x 1.1 x 1.1 x (-1.1)) produces -1.771561 (wrong sign), ie 1.61051 x -1.1, rather than 1.61051 minus (1/10th of 1.61051 = 0.161051) = 1.449459..
If you have 100 and lose 10% in a year, you have 90, not -110 so -10% multiplier is .9
(In general the multiplier is (1 +/- % value) so +10% is (1+ 0.1) -10% is (1 - 0.1)
100% increase is (1 +1)=2, ie. Double money, -100% is (1- 1) =0, wipeout.
M.
I'll get the code to check for negative values, and apply the correct transform. I'm grateful to everyone who has taken the time to respond.
MDW1954
-
- Lemon Slice
- Posts: 563
- Joined: November 4th, 2016, 3:46 pm
Re: More fun with percentages
As I got a mention earlier in the thread then somewhat belatedly, I concur.
-
- Lemon Quarter
- Posts: 2223
- Joined: November 4th, 2016, 8:46 pm
Re: More fun with percentages
But you wouldn't have replied otherwise, eh? Anyway, the calculation was duly coded, tested to oblivion, and works well. Should be a good time-saver.modellingman wrote:As I got a mention earlier in the thread then somewhat belatedly, I concur.
But yet again, I find myself bitterly regretting my school's decision, circa 1965, to start teaching maths using the School Mathematics Project syllabus and textbooks. 1960s liberalism at its worst. I've been doing an online Bayesian statistics course (great fun -- not!) and one of last week's quizzes involved two calculus questions. Totally useless for someone who never did calculus.
Seriously, though, that "it's 0.95" insight is so similar to the percentage calculation shortcut that you introduced me to in 1977 that it's scary.
MDW1954