Summing / cumulative loan interest

R

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
 
S

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

shg

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 t
2:13 to 3:14, ... (which may be exactly what you want).

If not, you can use,

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

or

=SUMPRODUCT(IPMT(0.08 / 12, {1,2,3,4,5,6,7,8,9,10,11,12}, 240, 100000
0, 0)
 
J

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

shg

joeu2004 said:
Or more simply:
=SUMPRODUCT(IPMT(0.08/12,ROW($1:$12),240,100000,0,0))
May be more simple, but it too will change if you insert rows within o
above the range.
=SUMPRODUCT(IPMT(8%/12,12*ROW(A1)-ROW($1:$12)+1,240,100000))
So will that.
Or more simply, relying on the Analysis ToolPak:

=CUMIPMT(8%/12,240,100000,12*ROW(A1)-11,12*ROW(A1),0)
And so will that if you insert rows above row 1
 
J

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top