# Amount Paid as Interest

#### eforti

##### Board Regular
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.

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.

Try this:

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

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

Actually, you can do this like this:

=Payment * Term - PV

will give you the total interest paid...

(anyway, both formulas produce the same result)

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.

eforti said:
{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?

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

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

Replies
4
Views
528
Replies
6
Views
883
Replies
1
Views
898
Replies
4
Views
1K
Replies
9
Views
672

1,211,848
Messages
6,104,357
Members
447,902
Latest member
chriswebs23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back