Product Calculation in an Access Query

[Guest]

We have a table of Index Returns for every month end starting in 1992 to
current date. In access I am trying to calculate the Annual compound return
for 10 yrs, 5 yrs, 3 yrs and 1 yr.

Originally this calculation was done in excel and each index would have a
worksheet sheet with all the monthly values and a column called Return. In
the column called return there is a formula as follows

=PRODUCT(I44:I163)^(1/10)-1 (for 10 yr)
=PRODUCT(I92:I163)^(1/5)-1 (for 5 yr)
=PRODUCT(I128:I163)^(1/3)-1 (for 3 yr
=PRODUCT(I152:I163)-1 (for 1 yr)

Every year, we would redefine the 10 yr, 5 yr 3yr and 1 yr range to include
the proper dates for the formulas above.

My question is: how do I do this calculation in Access? There isn't a
product function. I have a table with all of the Monthly Index values since
1992 to current date. The fields are:

VLDT, Index Code, Index Name and Return. So, for the 10 yr calculation I
can put in a date range of ie: Between #31/12/2006# and #31/12/2006#, I
would need to either group by Index Code OR Index Name but here's the kicker,
it doesn't appear that there is a product function in access. So, any pearls
of wisdom on how I would do this product calculation above? I am so stumped
here!

 

[Michel Walsh]

If the values are strictly positives, you can EXP(SUM(LOG( fieldName))) to
get the same result as, in Excel, PRODUCT( column_range )

That comes from the mathematical fact that:

a^(b+c) == a^b * a^c




Vanderghast, Access MVP

 

[Guest]

Unfortunately, the values are not always positive as there are periods where
negative returns have been realized.

 

[John Spencer]

Add the log of the numberfield together and then change that back to a
number

Exp(Sum(Log([number]))) AS product

That does rely on all the values being greater than zero.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

I have this note in my things to look at pile. I don't know if it will
work for you or not.

Per Brian Camire

If "value" contains only positive numbers(no nulls, no negatives, no zero
value), try:
Exp(Sum(Log([t2.value])))

Otherwise, try using the following expression:

IIf(Sum(IIf([Your Field]<0,1,0)) Mod
2=1,-1,1)*Sgn(Min(Abs([YourField])))*Exp(Sum(Log(Abs(IIf([YourField]=0,1,[YourField]))))

For reference:

1. The expression "IIf(Sum(IIf([Your Field]<0,1,0)) Mod 2=1,-1,1)"
evaluates to -1 if there are an odd number of negative values, or 1
otherwise.

2. The expression "Sgn(Min(Abs([Your Field])))" evaluates to 0 if one of
the values is zero, or 1 otherwise.

3. The expression "Exp(Sum(Log(Abs(IIf([Your Field]=0,1,[Your Field]))))"
returns the product of the absolute values, substituting values of 0 with 1.
Substituting values of 0 with 1 is simply a "trick" to avoid the "Invalid
procedure call error". If there is a value of zero, the result of this
expression doesn't matter anyway, since it will be multiplied by the result
of the second expression, which will be zero.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..