Sumproduct question

[Guest]

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)+SUMPRODUCT(--(($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

 

[Roger Govier]

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.

 

[Guest]

What I have been told is that the surrender charge scale used has to be
totally variable.

What I did was to build a surrender charge scale for the maximum occurrences
- which ended at U69 and then grabbed the preceeding N values

=SUMPRODUCT(--(($H45-$F$10:$F45)>0),(INDIRECT("U"&70-B45)):$U$69,$C$10:$C45)+SUMPRODUCT(--(($H45-$F$10:$F45)>0),--(($H45-$F$10:$F45)<C45),($H45-$F$10:$F45),(INDIRECT("U"&70-B45)):$U$69)

However, making the above change has given me the correct results for the
first three years - I need to investigate the remaining years - don't know if
there are any problems in future years.