G
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
=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