Max in Sumproduct

[djmarky]

I have a file that has, in the third row, cashflows on which interest is to be accrued (in this case, compounded at 10% annually). I am trying to show the cumulative value of those cashflows plus accrued interest, calculated in a single row. This formula is what I came up with:

=SUMPRODUCT(($S3:W3)*((1.1)^(COLUMN(W3)+1-COLUMN($S3:W3))))

Now, I need to introduce a limit by which interest is only compounded for a certain period of time. Trying to limit the interest to three years, I tried:

=SUMPRODUCT(($S3:W3)*((1.1)^(MIN(3,COLUMN(W3)+1-COLUMN($S3:W3)))))

However, the MIN function appears to be outside of the array as it keeps returning "1." So, I think what is happening is that I'm getting Min(3,{5,4,3,2,1})=1 where what I'm hoping for is an array of {min(5,3),min(4,3),min(3,3),min(2,3),min(1,3)} to multiply by the array of cashflows.

Just to be clear, the result that I'm looking for would look something like this:

Investment by Year: 1,0,1,0,0,0,1,... (it goes on for 50 or so columns)
Ending Value by Year: 1.1, 1.21, 2.43, 2.54, 2.66,2.66,3.76,...
Where the values are calculated as: 1.1^1, 1.1^2, 1.1^3 + 1.1^1, 1.1^3 + 1.1^2, 1.1^3 + 1.1^3,1.1^3 + 1.1^3, 1.1^3 + 1.1^3 + 1.1^1,...

Thanks!

P.S. - I hope my first post is worthy. I've used this board for some time and have generally been able to find an answer without posting. However, my search for 'Min and Sumproduct" was not fruitful.

 

[Andrew Poulsom]

Welcome to MrExcel.

Is it?

=SUMPRODUCT(($S3:W3)*((1.1)^((COLUMN($S3:W3)-COLUMN(S3)+1)*((COLUMN($S3:W3)-COLUMN(S3)+1)<=3))))

 

[djmarky]

Andrew,

Thanks for the suggestion. It doesn't quite work, though. I copied your formula and got the resulting array of sumproduct{1.1,0,1.331,0,0}, when what I'm hoping for is sumproduct{1.331,0,1.331,0,0}.

Any other ideas?

Thanks again,
David

 

[djmarky]

Sorry for posting a duplicate. Here is the clarification that was included in that new posting:

I have a file that has, in the third row, cashflows on which interest is to be accrued (in this case, compounded at 10% per period). I am trying to show the cumulative value of those cashflows plus accrued interest, calculated in a single row. So, for example, in 7th period the formula I came up with is:

=SUMPRODUCT(($B3:H3)*((1.1)^(COLUMN(H3)+1-COLUMN($B3:H3))))

Now, I need to introduce a dynamic limit by which interest is only compounded for a certain period of time, denoted in cell A4. Trying to limit the interest to three years, I tried:

=SUMPRODUCT(($B3:H3)*((1.1)^(MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)))))

However, the MIN function appears to be outside of the array as it keeps returning "1." So, I think what is happening is that I'm getting, where A4=3, Min(3,{7,6,5,4,3,2,1})=1 as opposed to the array that I'm hoping for of {min(7,3),min(6,3),min(5,3),min(4,3),min(3,3),min(2,3),min(1,3)} to multiply by the array of cashflows.

I'm not tied in any way to my original formula and would also appreciate wholesale new formulas that accomplish the same task as well. The main point of the formula is to be able to multiple one array of values by another array of time that puts a maximum on the amount of time that has elapsed since the date of that cashflow. Just to be clear, the result that I'm looking for would look something like this:

Investment by Year: 1,0,1,0,0,0,1,... (it goes on for 50 or so columns)
Ending Value by Year: 1.1, 1.21, 2.43, 2.54, 2.66,2.66,3.76,...
Where the calculation for the 7th data point should be 3.76 = sumproduct{1*1.1^min(7,3), 0*1.1^min(6,3), 1*1.1^min(5,3), 0*1.1^min(4,3), 0*1.1^min(3,3), 0*1.1^min(2,3), 1*1.1^min(1,3)}

Thanks!

 

[keithkemble]

incorrect post, apologies, should against bloated file