Remove ads

Introducing the LemonFools Personal Finance Calculators

More fun with percentages

Discussions regarding financial software
MDW1954
Lemon Slice
Posts: 418
Joined: November 4th, 2016, 8:46 pm
Has thanked: 78 times
Been thanked: 48 times

More fun with percentages

#168598

Postby MDW1954 » September 24th, 2018, 1:52 pm

I posted a request a few months ago about hard-coding a particular percentage calculation, and received some useful validation.

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.

uspaul666
Lemon Pip
Posts: 72
Joined: November 4th, 2016, 6:35 am
Has thanked: 27 times
Been thanked: 17 times

Re: More fun with percentages

#168600

Postby uspaul666 » September 24th, 2018, 1:55 pm

Geometric mean ?
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.

MDW1954
Lemon Slice
Posts: 418
Joined: November 4th, 2016, 8:46 pm
Has thanked: 78 times
Been thanked: 48 times

Re: More fun with percentages

#168603

Postby MDW1954 » September 24th, 2018, 1:59 pm

uspaul666 wrote:Geometric mean ?


Well that's an interesting (an unexpected) reply! But maths-wise, that's way above my pay grade.

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

uspaul666
Lemon Pip
Posts: 72
Joined: November 4th, 2016, 6:35 am
Has thanked: 27 times
Been thanked: 17 times

Re: More fun with percentages

#168605

Postby uspaul666 » September 24th, 2018, 2:00 pm

Sorry, see the edit and use =GEOMEAN()

MDW1954
Lemon Slice
Posts: 418
Joined: November 4th, 2016, 8:46 pm
Has thanked: 78 times
Been thanked: 48 times

Re: More fun with percentages

#168613

Postby MDW1954 » September 24th, 2018, 2:20 pm

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

mc2fool
Lemon Quarter
Posts: 1054
Joined: November 4th, 2016, 11:24 am
Has thanked: 4 times
Been thanked: 192 times

Re: More fun with percentages

#168626

Postby mc2fool » September 24th, 2018, 2:56 pm

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.

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).

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.

MDW1954
Lemon Slice
Posts: 418
Joined: November 4th, 2016, 8:46 pm
Has thanked: 78 times
Been thanked: 48 times

Re: More fun with percentages

#168638

Postby MDW1954 » September 24th, 2018, 3:31 pm

mc2fool wrote:
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.


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.

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

IrishIceHawk
Posts: 1
Joined: November 5th, 2016, 3:37 pm
Been thanked: 1 time

Re: More fun with percentages

#168662

Postby IrishIceHawk » September 24th, 2018, 5:47 pm

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..


Your conventional multiplication is using the wrong figure for -10%
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.

MDW1954
Lemon Slice
Posts: 418
Joined: November 4th, 2016, 8:46 pm
Has thanked: 78 times
Been thanked: 48 times

Re: More fun with percentages

#168702

Postby MDW1954 » September 24th, 2018, 9:27 pm

IrishIceHawk wrote:
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..


Your conventional multiplication is using the wrong figure for -10%
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.


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).

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

modellingman
2 Lemon pips
Posts: 184
Joined: November 4th, 2016, 3:46 pm
Has thanked: 33 times
Been thanked: 60 times

Re: More fun with percentages

#170925

Postby modellingman » October 2nd, 2018, 9:27 pm

As I got a mention earlier in the thread then somewhat belatedly, I concur.

MDW1954
Lemon Slice
Posts: 418
Joined: November 4th, 2016, 8:46 pm
Has thanked: 78 times
Been thanked: 48 times

Re: More fun with percentages

#177255

Postby MDW1954 » October 30th, 2018, 9:50 pm

modellingman wrote:As I got a mention earlier in the thread then somewhat belatedly, I concur.


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.

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


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 3 guests