Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Compound Average Return (geometric mean)

Please help to find a single formula to calculate negative and positive stream of returns. The problem is that =geomean formula does not work with negative numbers.
Here's an example on how to calculate geometric mean with 5 numbers:
Stream of numbers: 0.5, -1.4, -6.5, 0.3, -2.7

First step: I have to add 1 to all numbers (they are positive now)
Second step: =Product (multiply all numbers)
Third step (result): find 5th root(there are 5 numbers) of their product (or raise it to 1/5 power)

I can substitute steps 2 & 3 with a formula =geomean() and the answer will be correct except it won't be negative...but i think there should be another solution, a single formula.
Any idea? Thank you so much!


Post your answer or comment

comments powered by Disqus
My task is to create a custom VBA function that calculates the geometric mean. I know there is a worksheet function already but this is for a VBA course. The following assumptions are:
Write a custom function to calculate the geometric average of the returns listed in cells B6:B15. Below is the formula for a geometric average:
[(1+R1 ) * (1+R2) *...*(1+Rt)]^(1/t)-1
There are three requirements for your function:
i. Your function should have a single input – the range of cells that contain the return series.
ii. Use a For/Next loop to cycle through the returns in the range provided by the user.
iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage point form.
ANY HELP PLEASE!!!
============================================================================================================================
Function MyGEO(Returns As Range)
Dim Varcount As Integer
Dim t As Integer
Dim mean As Double
Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1
For t = 1 To Varcount
mean = mean * (1 + Returns(t))

Next t
geomean = mean ^ (1 / Varcount) - 1
MyGEO = geomean
End Function


Hi Folks,

Basically what I am trying to do is get get a geometric mean for a series that includes negative numbers.

I know that the procedure to deal with this is to add a number (x) to the series to make them all positive, take the geometric mean, then subtract (x) from the series.

I know that it is done in an array format too.

However, I want to know how to find the ideal number that won't overstate the value.

Take for instance finding the return of this simple series:

(.25, -.25, and .25)

Conventional knowledge says that it would be roughly 5.43%

This is due to people adding 1 to the whole series, making it
(1.25, .75, and 1.25)

Taking the geometric mean, or
(1.25 * .75 * 1.25) ^(1/3)

Then subtracting 1 from the Geometric mean series
or in excel...
={GEOMEAN(1.25,.75,1.25) - 1}

Next, I tried adding 100 to the series. I the figured that the number should not have an effect, as it should give and take away by the same amount. I was wrong.

So the series becomes

(100.25, 99.75, 100.25)

and thus

={GEOMEAN(100.25,99.75,100.25) - 100}

But, the percentage ends up as 8.31%

As you can see, by adding a greater number, it adds more to the percentage.

Now I figured, why not use the smallest possible increment? I thought that if the number I added to the series was small enough, it should also be small enough to not affect my result.

So, I added .2500000000001 to the series.
This way, the series would be

.5000000000001, .0000000000001, .5000000000001

So, my formula ended up being

={GEOMEAN(.5000000000001, .0000000000001, .5000000000001) - .2500000000001 }

The percentage ended up being -25%.

My question to you then, is which one is the true answer? Why is it the true one? If none of these are correct how would I go about finding the true answer?

Maybe my finance math is just horrendous, but the number(x) I add to the series, in theory, should not affect the results to such a grand degree.

See the attached data for further clarificiation.

what is the best geometric mean formula for a short list of positive and negative returns? thank you!

The only formula they give is for positive data. I am trying to calculate the
geometric mean of returns for different portfolios with some negative data.

Greetings,

I have a very large database that I am trying to perform some
calculation on using Excel. I need to calculate geometric means on
subsets of the data (specifically, I need to calculate geomean for
several fields for each "site" within the dataset over a specific
period of time). I think that using pivot tables is the way to go, but
I can't quite figure it out. This is what my database spreadsheet looks
like:

site date a b c d etc.
1 6/1/06
6/2/06
6/3/06
etc.
2
3
etc.

where a, b, c, d, etc. are all my fields (there are about 100 of them).
Each site has been sampled on multiple dates. For most of the fields I
want to calculate the mean (average) for each site, which I can do
quite easily in a pivot table. Four of the fields need to be calculated
as geometric means, however. When I set up my pivot table it looks like
this:

site data total
1 mean a #
mean b #
mean c #
etc.
2
3
etc.

Is there a way to calculate the geomean? I tried using a calculated
field but I think you can only sum the values, which does not seem to
help (at least, I seem to come up with sums when I try it). In my
searching of the forums I get the sense that maybe a helper column is
the way to go, but I can't wrap my head around how to do it. Can anyone
help?

Also, within the database there are quite a few cells that contain "no
data" - this happens when sampling occurs on a given date but not at a
given site. Are these blank cells being included in the mean
calculations, or not? I definitely don't want them to be.

Thanks very much!

Carrie

--
Carrie

How can I return the min, mean, and max values in one column based on a
value (criterion) in another column. For example, how can I return the
mean price (col 2)for all SWE4s (col 1)? Thanks for your guidance!

Item Price
NETAD 3 $8,985.60
SWE 4 $10,368.00
NET 2 $7,142.40
AD A4 $4,867.20
LYST 1 $5,529.60
LYST 2 $5,184.00
SWE 4 $11,059.20
NET 3 $9,216.00
NET 4 $1,209.60
NET 4 $1,152.00
IA 4 $1,324.80
SE 4 $1,198.08
LYST 2 $6,946.56

--
stevenva
------------------------------------------------------------------------
stevenva's Profile: http://www.excelforum.com/member.php...o&userid=16100
View this thread: http://www.excelforum.com/showthread...hreadid=275496

Can someone help define the formula to calculate the compound average growth
rate (CAGR) starting with an initial amount which grows or shrinks depending
upon the earnings for the year? Is the formula included in the formulae
included in Excel?

That data is in columns:
A: Year
B: Year's average interest rate growth or loss
C: Value of investment, e,g. starting at $1,000 and changing each year by
the rate in column B.
D: The CAGR effective to that year.

Thank you for your assistance.

Pat

Hello

I have rows of data for which I am trying to find the average return in
%.
How can I do this correctly with an array formula? I need to take the
((exit minus the entry)/entry) (b-a)/a. for each row, then average
that number.

I've tried to use an array {=AVERAGE(B2:B3-A2:A3)/A2:A3}
but this seems to find: (average B-average A)/ averageA
This returns a slightly different (read: wrong) answer.
Any ideas. I think there is a quick solution involving arrays, but I
don't use them very often.
Thanks

Paul

--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=540360

Hello

I have rows of data for which I am trying to find the average return in %.
How can I do this correctly with an array formula? I need to take the ((exit minus the entry)/entry) (b-a)/a. for each row, then average that number.

I've tried to use an array {=AVERAGE(B2:B3-A2:A3)/A2:A3}
but this seems to find: (average B-average A)/ averageA
This returns a slightly different (read: wrong) answer.
Any ideas. I think there is a quick solution involving arrays, but I don't use them very often.
Thanks

Paul

Hi Guys,

I am trying to write this fuction for calculating annualized return
from the monthly returns I have. If I use, (1+Rm) as my range input, I
can calculate the annualized return. But when I am trying to add 1 to
monthly returns within the function and get the answer, I am getting an
error. (apparently I can not add 1 to each element in the input
array/range).
Can somebody please help! (thanks in advance)

Function ROR(d As Range, n As Integer)

Dim a() As Single
Dim f As Integer

'f = Application.Count(d)

c = d.Rows.Count
e = d.Columns.Count
If c > e Then
f = c
Else
f = e
End If

ReDim a(d.Cells.Count)
For i = 1 To UBound(a)
a(i) = d.Cells(i)
Next i

prod = Application.WorksheetFunction.Product(a())

k = n / f

ROR = prod ^ k - 1

End Function

how do you input the formulas in excel to calculate the geometric mean?

hi - from the following data set example, i would like to return the average of column C if the criteria meets columns A & B. for example. if column A = 7 and column B = 2011, provide the average return of column C. result should be 42.113.... please help. any and all is most appreciated

best regards

8 2011 39.49073466
8 2011 40.10209175
7 2011 41.21885669
7 2011 40.36205606
7 2011 39.45128469
7 2011 41.65746244
7 2011 41.20755325
7 2011 43.41200631
7 2011 44.34023394
7 2011 44.24648197
7 2011 43.49374838
7 2011 44.51996125
7 2011 40.87890875
7 2011 43.80680931
7 2011 42.25107419
7 2011 38.73625294
6 2011 42.39128759
6 2011 42.68405703
6 2011 43.03057088
6 2011 40.92321709

Hi Folks,

I have a series of numbers, some of them including negatives. I want to get the geometric mean of them, but ignore all the negatives in the series. How would I go about making this?

I have attached some sample data.

I am wondering what the difference is between using the geometric mean and the product formula when calculating investment returns?

Taking 3 monthly returns, adding 1 to each. I then use the geomean formula of that range less 1. Then do the same with the product formula and get a very different answer.

What's the right approach? I always thought geomean was correct for chain-linking investment returns but when I think about it, it's only returning the geometric mean-- ie the average distance around the mean.

When do you use geomean then?

For example, monthly returns are:

0.41%
0.41%
0.42%

geomean gives me 0.41%
product gives me 1.25%

May I suggest a small experiment because I cannot find a fully general growth function that suits my needs and this solution is not particularly precise.

In cells A1:A5 enter 100, 105, 110, 115, 120.

In A7 enter {=AVERAGE((A2:A5-A1:A4)/A1:A4)}, as recommended here,
in A8, {=GEOMEAN(A2:A5/A1:A4)-1} and
in A9, enter =(A5/A1)^(1/4)-1.
This last formula is a simple compounding growth rate where 4 is the number of year's growth.

In cells B2 enter =A1*(1+A7)
in B3, =B2*(1+$A$7)
in B4, =B3*(1+$A$7) and
in B5, =B4*(1+$A$7).

In C5 enter =A5-B5.

If the growth formula is accurate, B5 should = A5; i.e. C5=0. Use four or more decimals of precision because these numbers are not big--real world numbers can be five or more orders of magnitude bigger.

If you use the averaging formula (A7) in column B, B5 does not = A5; if you use the geometric mean (A8), it does; if you use the simple growth formula (A9), it also does.

Conclusion: using the arithmetic mean is not as precise as using the other two methods.

Problem: now change A5 to -20. The formulas collapse. The arithmetic mean gives an answer of -25.7710%, which cannot be right because it is asymptotic to zero, while the other two cannot be calculated. Yet the progression in column A, or its inverse, often arises in financial data (profits are not a bad example).

Question: what is to be done?

Regards, John.
jasharp@telstra.com

Hello Folks!

Can anyone suggest a formula (or is there already a spreadsheet floating around for download) that can perform compounding for return on investment?

My goal is to simply create THREE inputs:

1. Period of Years
2. Average Percent Return Per Year
3. Average Monthly Contribution

From those three inputs, let's say 30 years, 12% per year, and $500 per month investment, how much money would it be compounded at by the end of 30 years? I know how to code in Excel to the point of doing it by hand, but then the 30 years input wouldn't be as efficient. Is there a way to do this without writing out a return for each month?

Thanks so much! EDR

I have multiple sheets each containing multiple columns of numbers, and sometimes when the numbers are not present there is a text error string such as "#N/A N.A.", or "#N/A N Ap". An example of the data is as follows:

4.06
1.28
3.411
#N/A N.A.
2.627
1.157
4.9
4.9
2.963

I would like to put geometric averages of each column from each tab in a summary tab.

* I tried using =GEOMEAN(...), but it can not handle the quantity of data that I have nor can it take non-numeric fields.

* Also Tried something like this =EXP(AVERAGE(IF(ISNUMBER(C:C),LN(C:C)))) as an array, but unless cells are specified explicitly, ie c10:c300, I get an answer of 1. I can not specify the cells because I have a variable height of each column.

* Perhaps someone can show me a VBA solution

I have a data set of daily interest returns, but need weekly returns. As a matter of fact, there are public holidays, which means some weeks consist of only four or even less trading days. Luckly I have the date to each return. How can I turn it into weekly returns.

greetings

Mirko

Hi All,

I have one Row that houses numbers 80-90 in seperate cells (11 columns A20:
K20) - these are my Numeric Labels.

I then have several other Rows that span the same number of columns as the
Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric
Labels.

I would like to AVERAGE the Numeric Values in each Row and have a Numeric
Label (value) that corresponds to the calculated average returned as the
result.

Example: Sample Data
A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90
A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10

Based on the above data - the Expected Result is 81
Average = 6; Return Numeric Label = 81

The leading zeros shown in A21:K21 is for alignment purposes only.

I would appreciate two formulas:
1) Includes zeros to be averaged
2) Excludes zeros from being averaged

I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),
$A21:$K21,0)) but receive the #N/A error message.

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi All,

I have one Row that houses numbers 80-90 in seperate cells (11 columns A20:
K20) - these are my Numeric Labels.

I then have several other Rows that span the same number of columns as the
Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric
Labels.

I would like to AVERAGE the Numeric Values in each Row and have a Numeric
Label (value) that corresponds to the calculated average returned as the
result.

Example: Sample Data
A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90
A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10

Based on the above data - the Expected Result is 81
Average = 6; Return Numeric Label = 81

The leading zeros shown in A21:K21 is for alignment purposes only.

I would appreciate two formulas:
1) Includes zeros to be averaged
2) Excludes zeros from being averaged

I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),
$A21:$K21,0)) but receive the #N/A error message.

Thanks
Sam

--
Message posted via http://www.officekb.com

My task is to create a custom VBA function to calculate the geometric mean(i know there is already a worksheet function for this but we need to make out own) with the following assumptions:

Write a custom function to calculate the geometric average of the returns listed in cells B6:B15.
Below is the formula for a geometric average:
[(1 + R1) * (1 + R2) * ... * (1 + Rt)]^(1/t) - 1
There are three requirements for your function:
i. Your function should have a single input – the range of cells that contain the return series.
ii. Use a For/Next loop to cycle through the returns in the range provided by the user.
iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage
---------------------------------------------------------------------------------------------------------------
This is my function so far and I keep getting a #NAME error. ANY HELP WOULD BE GREATLY APPRECIATED
Function MyGEO(Returns As Range)
Dim Varcount As Integer
Dim t As Integer
Dim mean As Double
Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1
For t = 1 To Varcount
mean = mean * (1 + Returns(t))

Next t
geomean = mean ^ (1 / Varcount) - 1
MyGEO = geomean
End Function

I'd like to see if an array formula can save a few columns on a spreadsheet.
I work at a sewage treatment plant. For regulatory purposes we need to
report the highest 7-day moving average concentration for a number of
variables in our effluent. We also need to report the highest 7-day
geometric mean for coliform bacteria. All within a given month. (The bosses
won't be interested in seeing the highest seven-day average that occurred
within the first six days of the month.)

For years we simply configured a column holding 7-day averages (or 7-day
geomean) next to the respective columns holding daily data and used the MAX
function to find the highest running average.

For most of the reports we have monthly data with dates in column A. I've
tried setting up an array formula using the offset function with the
reference set one row above the start of the data and using DAY($a$11:$a41)
to pluck out the row offset. I haven't had much luck.

Similarly, I'd like to convert the files to year-long files (they're
currently monthly). Would there be an array formula that could be used to
pick out the highest seven day average all held within the same month for a
filtered spreadsheet?

Thanks,
EQC

Sorry if I missed the answer to this with my google searches.

I have a list of data that I want to average the number in column B when there is a match in column A

COLUMN A | COLUMN B
alpha | 100
beta | 200
gamma | 300
alpha | 400
beta | 500

What I'm looking to do is return the MEAN of values in column B where column A = alpha. Though, I would settle for returning a list of the values that match the criteria. Index/Match combinations don't seem to be helping me because they only return the first match, I'd like to return all matches.

What direction would you point me to accomplish this? Thanks!

Hi all,

I have a real beast of a query. I have an array of numbers, the array consists of lots of 0s with some positive numbers interspersed. I need to find all the possible 'pairs' of positive numbers and calculate an average of these numbers and those in between. I need these averages returned to a new column, with the distances over which the average was calculated returned in the column next to it.

I've attached a book to show what I mean that I hope makes it a great deal clearer than this description.

In reality, the arrays I'll be looking at will be a lot longer than this example.

Please shout if this and the example book doesn't make things clear


No luck finding an answer? You could always try Google.