Array Math in UDF

[stephen.h.dow]

Here's a tricky one that I just can't seem to wrap my head around...

I'm looking at recovery rates (could be for chemical reactions,
finanical transaction, etc) over time. In each period I "lose" a
certain quantity, but know that I will recover a predetermined portion
(say 45%) over a predetermined recovery curve (4.5% in current period,
9%, 18%, 4.5%, and 9% in period +4). I'm trying to create a UDF that
will calculate the total recovered for a given period. The recovery
for a given period will include the 4.5% of the "loss" in the current
period plus and recovered amounts from prior period, if applicable.

A simplified example is:
Period | Loss | Recovered Amount
1 | 100 | 4.5
2 | 200 | 18 (9%*100 + 4.5%*200)
3 | 100 | 40.5
4 | 0 | 49.5
5 | 0 | 36
6 | 0 | 22.5
7 | 0 | 9

So, the total recovered amount is 180, which is 45% of the 400 that
was lost.

If anyone help me get started or point me to something that solved for
a similar problem, I would greatly appreciate it.

Thanks,
Steve

 

[stephen.h.dow]

I seem to have answered my own problem, so thought I'd post the
solution:

Function Recovery(RecovArray, LossArray, PeriodArray, Period As
Single) As Double
Dim Element As Variant
Dim SumRecov As Double
Dim Iteration As Integer
Dim LossOffset As Integer
Dim CalcPeriod As Integer

Iteration = 0
LossOffset = Period - 1

Do
SumRecov = SumRecov + RecovArray(Period - Iteration) *
LossArray(Period - LossOffset)
Iteration = Iteration + 1
LossOffset = LossOffset - 1
CalcPeriod = CalcPeriod + 1
Loop Until CalcPeriod = Period

Recovery = SumRecov

End Function