This is a slightly altered repost of my earlier question. I realized that the original title was misleading and that some clarifying text might help, but I was unable to edit the original. Also, the solution that was proposed doesn't seem to work for me.
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 dynamic limit by which interest is only compounded for a certain period of time, denoted in cell R4. Trying to limit the interest to three years, I tried:
=SUMPRODUCT(($S3:W3)*((1.1)^(MIN($R4,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, where R4=3, Min(3,{5,4,3,2,1})=1 as opposed to the array that I'm hoping for of {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^min(3,7), 0^min(3,6), 1^min(3,5), 0^min(3,4), 0^min(3,3), 0^min(3,2), 1^min(3,1)}
Thanks!
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 dynamic limit by which interest is only compounded for a certain period of time, denoted in cell R4. Trying to limit the interest to three years, I tried:
=SUMPRODUCT(($S3:W3)*((1.1)^(MIN($R4,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, where R4=3, Min(3,{5,4,3,2,1})=1 as opposed to the array that I'm hoping for of {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^min(3,7), 0^min(3,6), 1^min(3,5), 0^min(3,4), 0^min(3,3), 0^min(3,2), 1^min(3,1)}
Thanks!
