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

|improve this question
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

|improve this answer
  • 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. – Nathan Taylor Sep 11 '13 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. – Dilip Sarwate Sep 11 '13 at 22:19
  • @DilipSarwate between your explanation and the updated example, now it makes sense. Thanks! – Nathan Taylor Sep 11 '13 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

|improve this answer
  • 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 Mar 16 '11 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. :] – Nathan Taylor Mar 17 '11 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.