Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Calculating a Geometric Mean with some negative values

  1. #1
    New Member
    Join Date
    Jun 2007
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculating a Geometric Mean with some negative values

    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.

  2. #2
    Board Regular
    Join Date
    May 2007
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,392
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  4. #4
    Board Regular facethegod's Avatar
    Join Date
    Aug 2006
    Location
    CHITOWN
    Posts
    767
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this


    Code:
    =PRODUCT(1+A1:A3)^(1/COUNT(A1:A3))-1
    Confirm with Ctrl + Shift + Enter

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,392
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Jun 2007
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Apr 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating a Geometric Mean with some negative values

    Quote Originally Posted by pgc01 View Post
    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!

  8. #8
    New Member
    Join Date
    Jul 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.5 0.235 1.235 1.104352 =GEOMEAN(D3,D4,D5,D6,D7,D8,D9,D10,D11,D12)
    33.6 0.336 1.336 using excel D3=1.235 D12=1
    -18.8 -0.188 0.812 B3=23.5 C3=0.235
    14.4 0.144 1.144
    -14.9 -0.149 0.851
    -1 -0.01 0.99
    3 0.03 1.03 Note: Add one to remove negative and subtract 1 from answer.
    96.2 0.962 1.962
    3.4 0.034 1.034
    0 0 1
    Geometric mean = 10.4% is the answer =10.4%














    Last edited by Mukundane; Jul 12th, 2015 at 07:19 PM. Reason: to make it clearer

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Calculating a Geometric Mean with some negative values

    Quote Originally Posted by thazan View Post
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •