Calculating Finance Charge
 

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

Answer: Calculating Finance Charge
 

Yes, there is a function in Excel that can help you calculate the finance charge on a credit card. The function you can use is called the "PMT" function.

Here's how you can use the PMT function to calculate the finance charge:

1. First, you need to know your credit card's annual percentage rate (APR). Let's say your APR is 18%.
2. Next, you need to know your credit card's average daily balance (ADB). To calculate your ADB, add up your daily balances for the billing cycle and divide by the number of days in the billing cycle. Let's say your ADB is $1,000.
3. Now, you can use the PMT function to calculate the finance charge. The formula for the PMT function is:
   Formula:

   ---

   =PMT(rate/365, days, balance) 


   ---

   . In this formula, "rate" is your APR, "days" is the number of days in the billing cycle, and "balance" is your ADB. So, for example, if your billing cycle is 30 days, the formula would be:
   Formula:

   ---

   =PMT(0.18/365, 30, 1000) 


   ---

   . This will give you the finance charge for that billing cycle.
4. If your payment date is not the same every month, you can adjust the formula by changing the "days" parameter to reflect the number of days between your payment date and the end of the billing cycle. For example, if your payment date is 10 days before the end of the billing cycle, you would use "20" instead of "30" for the "days" parameter.

Calculating Finance Charge
 

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.

--
Regards,
Fred


"soadokdls" wrote in message
...
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