sd wrote...
....
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

What you seem to want to do is theoretically flawed. First off, discounting
amortized interest is theoretically meaningless on its own. Second, if
you're making monthly payments, then the interest paid in each monthly
payment should be discounted at the effective interest rate appropriate for
that month. Summing interest paid during a year then applying a different
annual intererst rate to discount those payments produces a thoroughly
meaningless result.

Better to consider using

NPV((1+$B$14)^(1/12)-1,
IPMT($B$2/12,ROW(INDIRECT("1:"&$B$13*12)),$B$1,-$B$3))