sumproduct help

[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.

 

[Harlan Grove]

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))

 

[sd]

Thanks Harlan Grove for the reply
The reason I discount the amortized Interest payment is to compute the PV of
tax savings that accrue year over year. The discount rate applied is the
rate that an alternative investment would earn otherwise and would very well
be different from the interest rate on the loan.

 

[hrlngrv]

The reason I discount the amortized Interest payment is to compute the PV of
tax savings that accrue year over year. The discount rate applied is the
rate that an alternative investment would earn otherwise and would very well
be different from the interest rate on the loan.

....

If you're comparing opportunity costs of buying a house with a mortgage
vs renting and investing what would have been your equity in the house,
then the correct way to do that is to compare the present value of the
principal and interest payments on the mortgage *PLUS* the expected net
resale value of the house at the end of the period in question against
the present value of rental costs, invenstment income and the expected
principal value of the investment at the end of the period in question.
Set up both as monthly cashflows, and calculate their PVs using the NPV
function with the same *monthly* effective discount rate.

The main flaw in your original formula was accumulating monthly
interest payments by year, then discounting those annualized amount as
if they occurred at the end of each year. That would significantly
overstate the discount on your interest payments since you'd discount
all monthly payments as if they were made at 12 months rather than 11
of them 1, 2, 3, . . ., 11 months sooner, especially since standard
amortization means the interest portion of constant payments decreases
over time (since the principal balance decreases, and amortized
interest is based on the principal balance at the time of the payment).