5

I am trying to come up with the equation which will give me the total interest to be paid on something given a fixed interest rate with a fixed payment for a given period of time. The interest is applied to the remaining amount each month, after deducting any payments made.

I know how to work out the resulting total interest one month at a time, but I am unsure what the form of the equation would be given all of the known variables:

  • Total Due

  • Fixed Monthly Payment

  • Fixed Interest Rate (% of Remaining)

  • Number of Months

    = Total Interest

2 Answers 2

4

I am trying to come up with the equation which will give me the total interest to be paid on something given a fixed interest rate with a fixed payment for a given period of time.

given all of the known variables:

  • Total Due
  • Fixed Monthly Payment
  • Fixed Interest Rate (% of Remaining)
  • Number of Months

Total Interest paid = (fixed monthly payment * number of months) - Initial balance

Example:

  • Total Due $20,000 for a car loan
  • Fixed Monthly Payment: $572.85
  • Fixed Interest Rate (% of Remaining) 2.0%
  • Number of Months 36 (3 years)

Total interest paid = (572.85 * 36)-20,000 = 20,622.66 -20,000 = 622.66

Using the mehod using Impt in Excel and summing the results $622.66

3
  • I don't see how that is going to work. The interest paid is calculated per month based on the balance remaining; it is a linear formula. Commented Sep 11, 2013 at 21:26
  • @Nathan Taylor The interest paid each month calculation has been incorporated into the calculation of the monthly payment amount. In fact, we don't even need to know the exact number for the interest rate (as long as we trust that the monthly payment has been calculated correctly), and indeed not even an issue of trust is necessary. If a loan shark offered you a loan of $20,000 to be paid off in 36 monthly payments of $572.85 without revealing the interest rate (as loan sharks are apt not to do), you would still end up paying what mhoran_psprep's answer says as total interest. Commented Sep 11, 2013 at 22:19
  • @DilipSarwate between your explanation and the updated example, now it makes sense. Thanks! Commented Sep 11, 2013 at 22:44
3

Because the payments go down over time, and you pay a different amount (in interest) each month, it's typically easier to generate an Amortization schedule and then add up the interest paid each month to arrive at the total interest paid.

There's a good article related to them here, as well as template you can use as a starting point. http://www.vertex42.com/ExcelArticles/amortization-calculation.html

2
  • 1
    and if you don't want to get into details and just calculate it you can use IPMT function in excel for each month and just sum it up.
    – Vitalik
    Commented Mar 16, 2011 at 21:13
  • @Vitalik that is essentially what I have done already; I asked the question because it bugged me that I couldn't figure out a more direct route. :] Commented Mar 17, 2011 at 5:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .