S
sd
I have a sumproduct formula with 2 arrays
the formula for first array returns 12 values per year whereas the second
array returns just 1 value per year
I get an error as the size of the 2 arrays are not the same.
A part of the formula formula looks as
SUMPRODUCT(((IPMT($B$2/12,ROW(INDIRECT("1:"&$B$13*12)),$B$1,-$B$3))),(1/((1+$B$14))^ROW(INDIRECT("1:"&$B$13)))
the first part of array returns the interest paid during a particular period
(cell B13 holds say 5 years, therefore 12 * 5 = 60 months) and returns 60
values instead of 5 that I am looking for.
and the second part computes the discount factor for each year. I can
multiply the second array by 12 to make it of the same size as first but
that is NOT what I need.
I need the the sum of 12 months for each year to be multiplied so that the
formula would look something like this
=sumproduct((1200,1145,1135,1130,,,,),(.90,.85,.82,.80,,,,,,)
where on first array 1200 is the sum of months 1 thru 12 , 1145 the sum of
month 13 thru 24 etc etc.
and on second array 0.90 is the discount factor for year 1, 0.85 for year 2
etc.
Any help will be greatly appreciated preferably without the use of any
helper column.
the formula for first array returns 12 values per year whereas the second
array returns just 1 value per year
I get an error as the size of the 2 arrays are not the same.
A part of the formula formula looks as
SUMPRODUCT(((IPMT($B$2/12,ROW(INDIRECT("1:"&$B$13*12)),$B$1,-$B$3))),(1/((1+$B$14))^ROW(INDIRECT("1:"&$B$13)))
the first part of array returns the interest paid during a particular period
(cell B13 holds say 5 years, therefore 12 * 5 = 60 months) and returns 60
values instead of 5 that I am looking for.
and the second part computes the discount factor for each year. I can
multiply the second array by 12 to make it of the same size as first but
that is NOT what I need.
I need the the sum of 12 months for each year to be multiplied so that the
formula would look something like this
=sumproduct((1200,1145,1135,1130,,,,),(.90,.85,.82,.80,,,,,,)
where on first array 1200 is the sum of months 1 thru 12 , 1145 the sum of
month 13 thru 24 etc etc.
and on second array 0.90 is the discount factor for year 1, 0.85 for year 2
etc.
Any help will be greatly appreciated preferably without the use of any
helper column.