specific cell values in two ranges

[J]

I am trying to do is to take a set of investment returns
and a benchmark over the same time period and calculate
the investment's compound return when the benchmark was
up divided by the benchmark's compound return when the
benchmark was up. This will therefore measure the amount
of the upside that an investment is capturing of the
market.

Mathematically - it is easy to see at the following
website -
http://support.pertrac2000.com/statistics2000.asp
Click on the left side - up capture ratio under
benchmark ratios.

The problem has to do with creating a loop that can
compare CORRESPONDING values within two ranges (obviously
equally sized ranges).

The benchmark part of the calculation is easy as it is
calculating (1+BM) times itself for the length of the
range. The fund part of the calculation is more
difficult as it uses corresponding cells from both ranges
(1+invesment) for each time that the benchmark is up.

Here is what i have so far:

Public Function UPCAPTURE(FUNDRETURNS As RANGE, BMRETURNS
As RANGE) As Double
Dim FUNDAP As Double
Dim BMAP As Double
Dim FUNDRNGCELL As RANGE
Dim BMRNGCELL As RANGE

BMAP = 1
For Each BMRNGCELL In bmreturns
If BMRNGCELL.Value > 0 Then
BMAP = BMAP * (1 + BMRNGCELL.Value)
End If
Next BMRNGCELL

'HERE IS THE PART I CAN'T FIGURE OUT:
FUNDAP = ????

UPCAPTURE = (FUNDAP - 1) / (BMAP - 1)
End Function

Thank you for the help.

 

[Bob Kilmer]

J,
I believe this captures the essence of the up capture. Check my work before
you take it to the bank.

Sub Test()
Dim i As Integer
Dim T As Double, TD As Double
Dim r1 As Range, r2 As Range
Set r1 = Range("A1", "A10")
Set r2 = Range("B1", "B10")
T = 1: TD = 1
For i = 1 To r1.Cells.Count
If r2(i) >= 0 Then
T = T * (1 + r1(i))
TD = TD * (1 + r2(i))
End If
Next i
T = T - 1
TD = TD - 1
Debug.Print T
Debug.Print TD
Debug.Print T / TD
End Sub

Bob

 

[Bob Kilmer]

Public Function UPCAPTURE(FUNDRETURNS As Range, BMRETURNS As Range) As
Double
Dim FUNDAP As Double
Dim BMAP As Double
Dim FUNDRNGCELL As Range
Dim BMRNGCELL As Range
Dim i As Long

BMAP = 1: FUNDAP = 1
For i = 1 To BMRETURNS.Cells.Count
If BMRETURNS(i).Value > 0 Then
FUNDAP = FUNDAP * (1 + FUNDRETURNS(i).Value)
BMAP = BMAP * (1 + BMRETURNS(i).Value)
End If
Next i

UPCAPTURE = (FUNDAP - 1) / (BMAP - 1)
End Function