Weighted Geometric Mean with both positive and negative values

[LoperK]

Hi all, this is my first thread in the excel forum. So there was another thread that talked about the weighted geometric mean and it was solved perfectly. My only question is:

How would this be done with both negative and positive values?

For example, if I was trying to find the weighted geometric mean of various monthly returns an investment strategy gave a client, how do I take into account the negative returns without getting an error?

Thanks for your help!

P.S. this is the thread in which it was solved for positive values: http://www.excelforum.com/excel-form...-in-excel.html

[LoperK]

I tried adding one before taking the natural log, then subtracting one at the end, that didn't work out though.

[MoneyMaker]

Hello

Is it possible for you to attach an Excel workbook with sample raw data for which you wish to compute the weighted geometric mean

And if I understand correctly you are using Excel on Mac, is that correct?

[LoperK]

Hello

Is it possible for you to attach an Excel workbook with sample raw data for which you wish to compute the weighted geometric mean

And if I understand correctly you are using Excel on Mac, is that correct?

weighted geo mean example.xlsx

The information is confidential in nature. But I created a spreadsheets with hypothetical weights and their respective returns. Yes I am using Mac 2011 but I also have the Windows version. Thanks for the help.

[shg]

What is the significance of the weighted geometric mean? Why not just the weighted average?

[LoperK]

Sorry but maybe I should have been more specific. I am trying to get these monthly returns and annualized them using a geometric mean which unlike the arithmetic mean takes into account compounding returns. This is necessary for financial analysis.

[MoneyMaker]

The information is confidential in nature. But I created a spreadsheets with hypothetical weights and their respective returns. Yes I am using Mac 2011 but I also have the Windows version. Thanks for the help.

Firstly, correct me if I am wrong but it seems your weights are the probabilities of respective returns.

Secondly, it is not possible to calculate geometric rate of return (geometric average) when one or more values are either 0 or negative

In my opinion, what you are seeking is an expected return and standard deviation of a portfolio

Excel does not have a function to find expected value but one can use SUMPRODUCT function to achieve the goal

If you wanted to know the standard deviation of portfolio then you would have to either write a VBA function or to look for 3rd party solutions

There is a new Excel add-in under development called njsXL that offers both expected value and standard deviation of the random variable which in this case is the returns

Let me know if you seek further guidance

[shg]

I tried adding one before taking the natural log, then subtracting one at the end, that didn't work out though.

Like this?

Please Login or Register  to view this content.

I don't understand the significance of the answer, so don't know if it's correct. It does not return the arithmetic mean if the weights are equal, which makes me suspicious.

[MoneyMaker]

Please Login or Register  to view this content.

I am sorry but formula for weighted geometric mean does not allow for negative values

e^ [ΣWln(A)/ΣW]

Log of a negative number is undefined

[shg]

Thank you. I think I learned that either in Fields and Waves as a college freshman or in Algebra I in ninth grade, can't remember which.

A loss of 5% can be regarded as a return of 95%, and, not knowing the purpose of the calculation, was following her lead.

But thank you again.

[MoneyMaker]

A loss of 5% can be regarded as a return of 95%, and, not knowing the purpose of the calculation, was following her lead.

I guess I learned something new,

I suppose I should have never dropped out of college but it wasn't my choice...

[LoperK]

I know there is a way. I'm not sure if you found it shg. Trying to verify now. Thanks for the effort regardless.

edit:

Sorry my brain has been so fried lately. Like I mentioned earlier. This is not only to find the geometric mean, but the annualized geometric mean of all the months together. If that makes sense...

[MoneyMaker]

I know there is a way. I'm not sure if you found it shg. Trying to verify now. Thanks for the effort regardless.

edit:

Sorry my brain has been so fried lately. Like I mentioned earlier. This is not only to find the geometric mean, but the annualized geometric mean of all the months together. If that makes sense...

If I followed shg correctly then the returns with a 1 added to them may be used in calculating geometric rate of return

0.95 1.03 1.02 1.04 0.99 1.025 1.017

Here is the formula

GRR = Π [ Vi / Vi-1 ]^[1 / t-1]

[MoneyMaker]

I know there is a way. I'm not sure if you found it shg. Trying to verify now. Thanks for the effort regardless.

edit:

Sorry my brain has been so fried lately. Like I mentioned earlier. This is not only to find the geometric mean, but the annualized geometric mean of all the months together. If that makes sense...

For returns without the weights I get the following GRR using tadGRR function


=tadGRR( {0.950, 1.030, 1.020, 1.040, 0.990, 1.025, 1.017} )
GRR = 1.142%

For returns with weights, I get the following GRR using tadGRR function

=tadGRR( { 0.19, 0.103, 0.153, 0.052, 0.099, 0.2255, 0.18306 } )
GRR = -0.618%