If you provide details on exactly how you move from 8% to zero, in what
stages over what time period, maybe someone can come up with a formula
to work out the surrender value.

--
Regards

Roger Govier


"Brad" wrote in message
...
Both of the below equations work and provide the correct answer

=SUMPRODUCT(--(($H$21-$F$10:$F$21)0),$M$10:$M$21,$C$10:$C$21)+SUMPRODUC T(--(($H$21-$F$10:$F$21)0),--(($H$21-$F$10:$F$21)<C21),($H$21-$F$10:$F$21),$M$10:$M$21)


=SUMPRODUCT(--(($H33-$F$10:$F33)0),$O$10:$O33,$C$10:$C33)+SUMPRODUCT(--(($H33-$F$10:$F33)0),--(($H33-$F$10:$F33)<C33),($H33-$F$10:$F33),$O$10:$O$33)


The problem is that I Column M and Column O hold essentially the same
information

Column M Column O
0.075 0.07
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.08 0.075
0.075
0.08
0.08
0.08
0.08
0.08
0.08
0.08
0.08
0.08
0.08
0.08

Column O essentially moves the numbers in column M down a 12 months
and
"prefills" the first twelve numbers with new percentages.

Any ideas how I could do this without having multiple columns of
surrender
charges?

More details - Everthing here is on a monthly basis.

Money is deposited monthly and for the first year the surrender charge
is
8.0%, the second year the surrender charge becomes 7.5% and eventually
becomes zero. However, the surrender charge does not decrease by 0.5%
each
year.

Assume 100 was deposited 1/1/xx

on 1/1/xx the surrender charge would be 8 = 100*.08
1/1/xx+1 the surrender charge would be 7.5 = 100*.075

but I have to keep track of each deposit separately