I am trying to calculate the lifetime interest paid on a credit account. I know that I can enter it into a spreadsheet and take the sum of the interest, but is there a formula to calculate such a value when you know the balance, minimum, and interest rate?
I have done a good deal of searching and I know there is a formula that can make this calculation if you know how many payments you will make, but is there a formulaic way to determine the total interest paid without knowing the number of payments?
Example: If I owe 8975.64 on a Credit Card with 6.55% interest rate and a minimum monthly payment of 65.18, how much interest will I pay over the life of the repayment if I only pay the minimums? I can easily solve this on a spreadsheet, but is there a formula which fits this scenario?
If the interest rate is an effective annual rate $i = 0.0655$, and interest is compounded monthly, then the equation of value is $$8975.64 = 65.18 a_{\overline{n}\rceil j} = 65.18 \frac{1 - v^n}{j},$$ where $$j = \frac{i^{(12)}}{12} = (1 + i)^{1/12} - 1 \approx 0.00530102$$ is the effective monthly rate of interest and $$v = 1/(1+j) \approx 0.994727$$ is the monthly present value discount factor. This is assuming that payments are made at the end of each month. Solving for $n$, which represents the number of monthly payments, we get $$n = \frac{\log \left( 1 - \frac{8975.64}{65.18} j \right)}{\log v} = \frac{\log 0.270021}{\log 0.994727} \approx 247.636.$$ As the final payment must fully extinguish the loan, the total number of payments needed is $n = 248$, corresponding to $20$ years $8$ months. The total amount of interest paid is tricky to compute, because the final payment is a fraction of the level amount: to find this fractional amount, we need to know the difference in the accumulated value of the loan, less the accumulated value of the level payments (excluding the last payment), at the time that the last payment is made. Since we now know $n = 248$, this difference is $$8975.64(1+j)^{248} - 65.18 (1+j) s_{\overline{247}\rceil j} = 33304.5 - 65.18 (1+j) \frac{(1+j)^{247} - 1}{j} \\ = 33304.5 - 33087.7 \approx 41.48.$$ Thus, the total amount of interest paid is $$247(65.18) + 41.48 - 8975.64 = 7165.30.$$
As I noted, the above calculation applies for an effective annual rate of $i = 0.0655$. If, however, the nominal monthly rate is $i^{(12)} = 0.0655$, then the calculation proceeds with $j = 0.0655/12 = 0.00545833$. If you do it this way, you get the same $n$ as in your calculation, $n = 256$, with the last payment being $57.43$, and the total amount of interest paid $7702.69$.