Summing / cumulative loan interest

[rom8v2]

I am using the IPMT function to return a the interest paid in one period
(month). I need help in obtaining (hopefully in a single formula) the sum of
periods 1 -12. I would rather not write out IPMT(.08/12,1,240,100000,0,0)+
IPmt period 2 + Ipmt period 3, + IPmt period N.

I was hoping I could get some kind of function that would return the sum of
periods 1-12 (for year 1), 13-24 (for year 2), etc. I can do this on the HP
12c calculator.... need help in Excel.

Thanks
JP

 

[ShaneDevenshire]

Hi,

This should do it:

=SUMPRODUCT(IPMT(0.08/12,ROW(1:12),240,100000,0,0))

If this helps, please click the Yes button.

 

[rom8v2]

Thanks!

 

[rom8v2]

Thanks!

Hi,

This should do it:

=SUMPRODUCT(IPMT(0.08/12,ROW(1:12),240,100000,0,0))

If this helps, please click the Yes button.

 

[joeu2004]

You can use
=SUMPRODUCT(IPMT(0.08/12,ROW(1:12),240,100000,0,0))
Be aware that if you copy it down it will change from months
1:12 to 2:13 to 3:14, ... (which may be exactly what you want).

I doubt it.

If not, you can use,
=SUMPRODUCT(IPMT(0.08 / 12, ROW(INDIRECT("1:12")), 240, 100000, 0, 0))

Or more simply:
=SUMPRODUCT(IPMT(0.08/12,ROW($1:$12),240,100000,0,0))

Since the OP expressed interest in computing the cumulative interest
for periods 1-12, 13-24, etc, the OP might do the following in one
cell and copy down through 19 cells:

=SUMPRODUCT(IPMT(8%/12,12*ROW(A1)-ROW($1:$12)+1,240,100000))

Or more simply, relying on the Analysis ToolPak:

=CUMIPMT(8%/12,240,100000,12*ROW(A1)-11,12*ROW(A1),0)

 

[joeu2004]

May be more simple, but it too will change if you insert rows within or
above the range.

Good point! I knew there is a reason why I prefer self-referential
formulas. My point was: the OP wanted a formula that could be used
for the periods 1-12, 13-24, etc. One way to rewrite the SUMPRODUCT
formula is (put the first instance in A1 and copy down):

=SUMPRODUCT(IPMT(8%/12,
12*(ROW(A1)-ROW($A$1))+ROW(INDIRECT("1:12")), 240,100000))

And that might be a lot easier than the equivalent CUMIPMT formula.
One way to rewrite that is (put the first instance in B1 and copy
down):

=-CUMIPMT(8%/12,240,100000,
12*(ROW(B1)-ROW($B$1))+1, 12*(ROW(B1)-ROW($B$1))+12, 0)

 

[joeu2004]

Errata....

And that might be a lot easier than the equivalent CUMIPMT formula.

Oops: I wrote that before I simplified the formula. Personally, I
think the CUMIPMT formula is just as easy and more intuitive. But
there might be reasons that the OP would want to avoid relying on the
ATP.