Getting MIN to work inside of an Array, or other solution

**djmarky** · 06-27-2011, 03:21 PM

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!

**DonkeyOte** · 06-28-2011, 03:02 AM

Welcome to the Board.

As you say you can't run a MIN within SUMPRODUCT (it's akin to an Array) you could however use either:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

the former is perhaps a little more elegant and avoids double evaluation of the COLUMN calc (and requirement for Array entry)

**djmarky** · 06-28-2011, 01:06 PM

Amazing, two for the price of none

Thanks!

To turn up the level of difficulty, can you think of a way of replacing the column functions with date functions? I would like to have row 1 contain dates and then to calculate the interest based on those dates (instead of assuming that the time between columns is constant). I tried yearfrac, but that function doesn't appear to allow arrays.

Thanks again!

**DonkeyOte** · 06-28-2011, 01:43 PM

Might be simplest to post a sample / proof of concept file to illustrate requirements (as before expected results help validate logic)

**djmarky** · 06-28-2011, 03:53 PM

I got it to work using Days360. The formula that eventually worked for me is below. It's worth noting that I changed the time periods to quarters and also added in a condition where interest does not accrue during the first year (so, only accruing in years 2-3).

{=SUM($B3:H3*(1.1^IF(((DAYS360($B2:H2,H2))/360)>$A4,$A4-1,IF((DAYS360($B2:H2,H2)/360)<$A3,0,((DAYS360($B2:H2,H2)/360)-1)))))}

Thanks again!