Calculating Finance Charge

[Guest]

Is there some function in Excel that will calculate the finance charge on a
credit card? I tried to use the IPMT but it didn't quite work. Here's my
situation, so maybe if there's not a function there's a formula:

I don't use my credit card for anything new, so the only things that post to
my account is the payment and the finance charge. I had set up 2 different
formulas so I could calculate the finance charge for the beginning balance
and the new balance after the payment was made. I kept coming up a little
short, and then I read in my terms that it adds the finance charge to the
beginning balance daily, so my calculations are a little off.

Since my payment date isn't the same every month (annoying) I would have to
change the formula every month to match the number of days before and after
the payment is made. If there is a function where I can just type in the
number of days and it will calculate it that would be ideal.

Does anyone know anything about this?

Thanks

 

[Fred Smith]

Calculating the finance charge is easy. It's:

=Amount*IntRate*#days/365

If this were me, I would work with dates rather than number of days, and create
the following columns:

Date, OpeningBalance, FinanceCharge, Payment, EndingBalance

Enter the Date and starting EndingBalance in Row 2.

For Row 3:
Date = entered
OpeningBalance =E2
FinanceCharge =B3*intRate%*(A3-A2)/365
Payment = entered
EndingBalance = B3+B4-B5

Copy down as far as you need.

In addition to doing your calculations, you have a history of your payment and
finance charges.