Help w/ IPMT differences

[Lewis Drakker]

I've searched the archives of the group and looked at A LOT of posts
regarding the IPMT function, but I'm just not seeing a concrete answer
to this very simple problem..

I am doing basic interest calculations on loan amounts to compare
various options, but the values I'm coming up with are different than
what I get with an amortization schedule.

For example, a $100,000 loan, 5.5% per year, 180 total payments - what
is the cumulative interest at the end of the first year? According to
my amortization schedule it is $5389.81. However, if I use the
function:

=IPMT(0.055,1,180,100000) the result is $5500.00

Its a minor difference, but I can't figure out how to get these
numbers to reconcile. Can anyone tell me where I am going wrong and
what method/formula to use to make them equal?

Thanks very much.

 

[Ron Rosenfeld]

I've searched the archives of the group and looked at A LOT of posts
regarding the IPMT function, but I'm just not seeing a concrete answer
to this very simple problem..

I am doing basic interest calculations on loan amounts to compare
various options, but the values I'm coming up with are different than
what I get with an amortization schedule.

For example, a $100,000 loan, 5.5% per year, 180 total payments - what
is the cumulative interest at the end of the first year? According to
my amortization schedule it is $5389.81. However, if I use the
function:

=IPMT(0.055,1,180,100000) the result is $5500.00

Its a minor difference, but I can't figure out how to get these
numbers to reconcile. Can anyone tell me where I am going wrong and
what method/formula to use to make them equal?

Thanks very much.

You are using the wrong formula. The IPMT formula says you are making a SINGLE
payment at the end of ONE year at 5.5% interest. But this is not how you are
paying off the loan.

Your amortization table has you making monthly payments. Each monthly payment
also pays down the amount of principal so you are not paying the 5.5% interest
on the full amount for the full year.

The function you want to use is CUMIPMT. Don't forget that the interest for
each payment is 5.5%/12. See HELP for further info.


--ron