Amount Paid as Interest

eforti

Board Regular
Joined
Aug 15, 2005
Messages
220
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.
 
Upvote 0
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. :)
 
Upvote 0
Actually, you can do this like this:

=Payment * Term - PV

will give you the total interest paid...

(anyway, both formulas produce the same result)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
cumipmt is exactly what I was looking for! Thank you to everyone who contributed.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top