|
Sumproduct question
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 |
Sumproduct question
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 |
Sumproduct question
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. "Roger Govier" wrote: 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 |
| All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com