Amount Paid as Interest

[eforti]

Hello all,

I found the function "IPMT()" explained on a website very well. Here is my question. This function determines how much money you will be paying on a loan during a given month of your loan. i.e. during the 7th month of a 48 month loan.

I would like to know how much money I will be paying overall during my loan. Before you say the obvious answer and reply "just multiply your loan by the interest rate..." realize that as you pay off your loan you owe less money to the financial institution and therefor the interest rate is multipltying itself by a lower amount of money. So if halfway through my loan I have paid off $5,000 of a $10,000 loan, I am now only accruing interest on $5,000 as opposed to $10,000.

Anyone know a function, or solution, that would apply to this?

Thanks in advance for any help

 

[Legacy 51064]

I would make an amoritization table and then you can sum up the total interest and principle that is paid over the life of the loan.

 

[eforti]

As much sense as that makes I have all of my data in a worksheet right now that looks great and I don't want to have to resort to another sheet/table. I was really hoping for part of the equation that would do this. There must be some type of function to do this.

Thank you for the response though.

 

[Juan Pablo González]

Try this:

=SUMPRODUCT(IPMT(Rate,ROW(A1:INDIRECT("A"&Term)),Term,PV))

replace Rate, Term and PV with the correct cell references.

 

[Juan Pablo González]

Actually, you can do this like this:

=Payment * Term - PV

will give you the total interest paid...

(anyway, both formulas produce the same result)

 

[eforti]

Thank you for the reply Juan. I am a little bit confused so far.

=SUMPRODUCT(IPMT(Rate,ROW(A1:INDIRECT("A"&Term)),Term,PV))

What is A1:indirect("A"&Term) reffering to? I know I need to replace Term with the cell refferencing the term of the loan. But what is the refference to row A all about?

I am getting different values with the two equations you provided. Let me know if I need to provide more specific information.

 

[tusharm]

{snip}
I am getting different values with the two equations you provided. Let me know if I need to provide more specific information.

Without worrying about what the SUMPRODUCT blah, blah, blah does, just look at JPG's formula. Payment * Term defines the total amount you paid. PV represents the loan amount you received and must be paid back. What does the difference between the two represent?

 

[Chitosunday]

You can use cumipmt (but you need to add-in analysis toolpack installed)

 

[eforti]

cumipmt is exactly what I was looking for! Thank you to everyone who contributed.