Calculating a Geometric Mean with some negative values

Marcus from Belmont

New Member
Joined
May 31, 2007
Messages
2
I am trying to work out how to calculate the geometric mean of a series of values, some of which are negative, ie. investment returns over a series of years with some negatives.

The Geomean function doesn't work because it only uses positive numbers.

Any help would be greatly appreciated.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

thazan

Board Regular
Joined
May 14, 2007
Messages
57
Can you just use the average function?

I don't think that any negative values can ever be used with the geometric mean.

I think this formula calculates the geometric mean.

=PRODUCT(A1:A6)^(1/COUNT(A1:A6))

I hope that helps.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
Hi Marcus
Welcome to the board

I never saw applications with geometric mean with negatives (doesn't mean it doesn't exist, of course).

Usually when you calculate the geometric mean of the returns of an investment over the years with positive and negative returns you are trying to find an equivalent average compounded return for the period.

The returns you usually use are not the percentages you add or subtract but the values you multiply to get the final amount.

Example
You invest 100
Year 1: return 20% (total: 120)
Year 2: return -5% (total: 114)

The geometric mean is calculated relative to the total (100%)

=((1+.2)*(1-.05))^(1/2)-1= 6.77%

This means that an investment with a constant return of 6.77% each period is equivalent to your investment.

You invest 100
Year 1: return 6.77% (total: 106.77)
Year 2: return 6.77% (total: 114)

Is this not your case? Please elaborate and post a sample with inputs, the logic and the expected results.

Kind regards
PGC
 

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
Try this


Code:
=PRODUCT(1+A1:A3)^(1/COUNT(A1:A3))-1

Confirm with Ctrl + Shift + Enter
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
P. S.
If you agree with my post you can use the Geomean as you originally wanted:

=Geomean(1+a1:a3)-1

Confirmed with Ctrl + Shift + Enter
 

Marcus from Belmont

New Member
Joined
May 31, 2007
Messages
2
Thanks to you all - have solved the problem which was a lack of thinking on behalf of the user. Was using negative percentages - what I should have been doing was using 100%=1 and -6%= 0.94.

Then I can use the simple GEOmean function as is.

Thank you all
 

kennyw

New Member
Joined
Apr 7, 2015
Messages
1
P. S.
If you agree with my post you can use the Geomean as you originally wanted:

=Geomean(1+a1:a3)-1

Confirmed with Ctrl + Shift + Enter

Another happy customer served! ;)

I vaguely remember some of this math from freshman college. But, its a stretch. And, that was all well before spreadsheets!
 

Mukundane

New Member
Joined
Jul 12, 2015
Messages
1
Re: Calculating a Geometric Mean with some negative values of Bank Investments

Geometric mean of negative and positive returns.23.5,33.6,-18.8,14.4,-14.9,-1,3,96.2,3.4,0
Calculating your bank returns for 10 years using Excel Spreadsheet.
%Add 1
23.50.2351.2351.104352=GEOMEAN(D3,D4,D5,D6,D7,D8,D9,D10,D11,D12)
33.60.3361.336using excel D3=1.235 D12=1
-18.8-0.1880.812B3=23.5 C3=0.235
14.40.1441.144
-14.9-0.1490.851
-1-0.010.99
30.031.03Note:Add one to remove negative and subtract 1 from answer.
96.20.9621.962
3.40.0341.034
001
Geometric mean =10.4%is the answer =10.4%
















<tbody>
</tbody>
 
Last edited:

Gregorio

New Member
Joined
Mar 9, 2018
Messages
3
Can you just use the average function?

I don't think that any negative values can ever be used with the geometric mean.

I think this formula calculates the geometric mean.

=PRODUCT(A1:A6)^(1/COUNT(A1:A6))

I hope that helps.


great help! =GEOMEAN(1+A1:A6)-1 is also working, thank you both
 

Haigts

New Member
Joined
Oct 13, 2021
Messages
1
Platform
  1. Windows
Hi Marcus
Welcome to the board

I never saw applications with geometric mean with negatives (doesn't mean it doesn't exist, of course).

Usually when you calculate the geometric mean of the returns of an investment over the years with positive and negative returns you are trying to find an equivalent average compounded return for the period.

The returns you usually use are not the percentages you add or subtract but the values you multiply to get the final amount.

Example
You invest 100
Year 1: return 20% (total: 120)
Year 2: return -5% (total: 114)

The geometric mean is calculated relative to the total (100%)

=((1+.2)*(1-.05))^(1/2)-1= 6.77%

This means that an investment with a constant return of 6.77% each period is equivalent to your investment.

You invest 100
Year 1: return 6.77% (total: 106.77)
Year 2: return 6.77% (total: 114)

Is this not your case? Please elaborate and post a sample with inputs, the logic and the expected results.

Kind regard
Re: Calculating a Geometric Mean with some negative values of Bank Investments

Geometric mean of negative and positive returns.23.5,33.6,-18.8,14.4,-14.9,-1,3,96.2,3.4,0
Calculating your bank returns for 10 years using Excel Spreadsheet.
%Add 1
23.50.2351.2351.104352=GEOMEAN(D3,D4,D5,D6,D7,D8,D9,D10,D11,D12)
33.60.3361.336using excel D3=1.235 D12=1
-18.8-0.1880.812B3=23.5 C3=0.235
14.40.1441.144
-14.9-0.1490.851
-1-0.010.99
30.031.03Note:Add one to remove negative and subtract 1 from answer.
96.20.9621.962
3.40.0341.034
001
Geometric mean =10.4%is the answer =10.4%















<tbody>
</tbody>
Hi if my growth value return is not less than 0 but it is1.65827674. Do i have to 1+ too?
 

Forum statistics

Threads
1,175,657
Messages
5,898,725
Members
434,724
Latest member
DJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top