Product Calculation in an Access Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
Unfortunately, the values are not always positive as there are periods where
negative returns have been realized.
 
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
..
 
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
..
 
Back
Top