Prev: Tabbing through input worksheet
Next: Using Cartesian Cell Names
From: Russell Dawson on 4 Apr 2010 11:03
Remember to make your questions clear in future by setting out what you
really want. I answered your question quite bluntly and I had the idea that
your question was not complete.

To get a full response to questions you must state exactly what you are
trying to achieve, e.g. you didn't specify that you wanted the monthly
charges.

Just watch out for the over judicious use of UPPER CASE.

Regards
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Trainee" wrote:

> I guess this is the formula I'm looking for, probably just need to divide
> it for 12(months) to get the monthly FINANCE CHARGES IN C1
>
>
> "Russell Dawson" wrote:
>
> > If A1 contains 1500.00 and B1 the 12.99 in C1 input =A1*B1%
> >
> > --
> > Russell Dawson
> > Excel Student
> >
> > Please hit "Yes" if this post was helpful.
> >
> >
> > "Trainee" wrote:
> >
> > > Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE
> > > CHARGES on XL?
> > > TX.
From: Fred Smith on 4 Apr 2010 11:45
a1: Annual interest rate (12.99%)
b1: Balance
c1: =b1*a1/12

Regards,
Fred

"Trainee" <Trainee(a)discussions.microsoft.com> wrote in message
news:AAA4DE00-F6FA-4732-B5BB-A653902B4680(a)microsoft.com...
>
> The monthly finance charges in C1
> "Niek Otten" wrote:
>
>> What do you want to know?
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>> Microsoft MVP - Excel
>>
>> "Trainee" <Trainee(a)discussions.microsoft.com> wrote in message
>> news:F246DE4B-EE72-4015-8D50-B4ADCA3E6BCC(a)microsoft.com...
>> > Let's say a balance of 1500.00 at 12.99% how do we calculate the
>> > FINANCE
>> > CHARGES on XL?
>> > TX.
>>

From: Stan Brown on 4 Apr 2010 13:04
Sun, 4 Apr 2010 06:37:01 -0700 from Trainee
<Trainee(a)discussions.microsoft.com>:
>
> Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE
> CHARGES on XL?
> TX.

It's really, really, really hard: multiply the two and divide by 12.

This isn't an Excel problem, it's a problem in business arithmetic.
You'd probably have learned more if you'd studied the examples in
your textbook.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...
From: Joe User on 4 Apr 2010 13:11
"Trainee" <Trainee(a)discussions.microsoft.com> wrote:
> I guess this is the formula I'm looking for, probably just need to divide
> it for 12(months) to get the monthly FINANCE CHARGES IN C1

But I hope you understand that that is only an estimate of the monthly
finance charges.


You wrote previously:
> Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE
> CHARGES on XL?

Generally, for credit card accounts in the US, you cannot use the balance at
the end of the billing cycle as basis for the calculation -- although the
degree of the inaccuracy depends on how you use your credit card event.

The details are in the disclosure statement (and updates) that the credit
card company sent to you.

Generally, interest is calculated by multiplying the daily interest rate
(usually a variable annual rate as of the first day of the billing cycle
divided by 365) by the average daily balance (ADB) during the billing cycle,
then multiplied by the number of days in the billing cylce.

So if the balance of your account was $10,000 for 30 days, but zero on the
31st day, your finance charge would not be zero (0 * 12.99%/12). Instead,
it would be about $106.77 -- (30*10000/31) * (12.99%/365) * 31.

And of course, an account balance is normally not constant throughout the
billing cycle (less one day, in my example). Instead, the ADB is calculated
by AVERAGE(A1:A31), where A1:A31 is the balance at the end of each day of
the billing cycle.

Needless to say, that is too detailed for most of us to calculate.

Alternatively, assuming that your account grows daily (except for the
payment for the previous billing period, usually received in the middle of
the period), I would estimate the ADB by taking the ending balance (or the
largest daily balance in my example above) divided by the total days in the
billing cycle (i.e. the average amount added to the balance daily), multiply
by the sum of the days, then divide by the total days. For my example:
(10000/31)*31*(31+1)/2 / 31. Algebraically, this can be reduced to
10000*(31+1)/2/31.

Thus, the finance charge in my example would be estimated by the following,
making some algebraic simplifications: (10000*(31+1)/2) * (12.99%/365) --
about $56.94.

(BTW, that can be written more succinctly as 12.99%*10000*(31+1)/2/365.)

As you can see, that is significantly less than the original estimate of
$106.77, and less than 10000*12.99%/12 ($108.25).


----- original message -----

"Trainee" <Trainee(a)discussions.microsoft.com> wrote in message
news:FD5E4A39-24BC-4287-BBFF-0AAAB593788F(a)microsoft.com...
>I guess this is the formula I'm looking for, probably just need to divide
> it for 12(months) to get the monthly FINANCE CHARGES IN C1
>
>
> "Russell Dawson" wrote:
>
>> If A1 contains 1500.00 and B1 the 12.99 in C1 input =A1*B1%
>>
>> --
>> Russell Dawson
>> Excel Student
>>
>> Please hit "Yes" if this post was helpful.
>>
>>
>> "Trainee" wrote:
>>
>> > Let's say a balance of 1500.00 at 12.99% how do we calculate the
>> > FINANCE
>> > CHARGES on XL?
>> > TX.

From: Joe User on 4 Apr 2010 13:23
I wrote:
> BTW, that can be written more succinctly as 12.99%*10000*(31+1)/2/365.

I should have noted that 31 is an example, not a constant, just as 12.99%
and 10000 are examples. Instead of writing 31, you would calculate it by
B1-A1, where A1 and B1 are the starting and ending dates of the billing
cycle respectively.

In contrast, 365 is a constant, at least for my credit card accounts. But
some credit card companies might use 366 in leap years.


----- original message -----

"Joe User" <joeu2004> wrote in message
news:%230EYvnB1KHA.5828(a)TK2MSFTNGP02.phx.gbl...
> "Trainee" <Trainee(a)discussions.microsoft.com> wrote:
>> I guess this is the formula I'm looking for, probably just need to divide
>> it for 12(months) to get the monthly FINANCE CHARGES IN C1
>
> But I hope you understand that that is only an estimate of the monthly
> finance charges.
>
>
> You wrote previously:
>> Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE
>> CHARGES on XL?
>
> Generally, for credit card accounts in the US, you cannot use the balance
> at the end of the billing cycle as basis for the calculation -- although
> the degree of the inaccuracy depends on how you use your credit card
> event.
>
> The details are in the disclosure statement (and updates) that the credit
> card company sent to you.
>
> Generally, interest is calculated by multiplying the daily interest rate
> (usually a variable annual rate as of the first day of the billing cycle
> divided by 365) by the average daily balance (ADB) during the billing
> cycle, then multiplied by the number of days in the billing cylce.
>
> So if the balance of your account was $10,000 for 30 days, but zero on the
> 31st day, your finance charge would not be zero (0 * 12.99%/12). Instead,
> it would be about $106.77 -- (30*10000/31) * (12.99%/365) * 31.
>
> And of course, an account balance is normally not constant throughout the
> billing cycle (less one day, in my example). Instead, the ADB is
> calculated by AVERAGE(A1:A31), where A1:A31 is the balance at the end of
> each day of the billing cycle.
>
> Needless to say, that is too detailed for most of us to calculate.
>
> Alternatively, assuming that your account grows daily (except for the
> payment for the previous billing period, usually received in the middle of
> the period), I would estimate the ADB by taking the ending balance (or the
> largest daily balance in my example above) divided by the total days in
> the billing cycle (i.e. the average amount added to the balance daily),
> multiply by the sum of the days, then divide by the total days. For my
> example: (10000/31)*31*(31+1)/2 / 31. Algebraically, this can be reduced
> to 10000*(31+1)/2/31.
>
> Thus, the finance charge in my example would be estimated by the
> following, making some algebraic simplifications: (10000*(31+1)/2) *
> (12.99%/365) -- about $56.94.
>
> (BTW, that can be written more succinctly as 12.99%*10000*(31+1)/2/365.)
>
> As you can see, that is significantly less than the original estimate of
> $106.77, and less than 10000*12.99%/12 ($108.25).
>
>
> ----- original message -----
>
> "Trainee" <Trainee(a)discussions.microsoft.com> wrote in message
> news:FD5E4A39-24BC-4287-BBFF-0AAAB593788F(a)microsoft.com...
>>I guess this is the formula I'm looking for, probably just need to divide
>> it for 12(months) to get the monthly FINANCE CHARGES IN C1
>>
>>
>> "Russell Dawson" wrote:
>>
>>> If A1 contains 1500.00 and B1 the 12.99 in C1 input =A1*B1%
>>>
>>> --
>>> Russell Dawson
>>> Excel Student
>>>
>>> Please hit "Yes" if this post was helpful.
>>>
>>>
>>> "Trainee" wrote:
>>>
>>> > Let's say a balance of 1500.00 at 12.99% how do we calculate the
>>> > FINANCE
>>> > CHARGES on XL?
>>> > TX.
>

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Tabbing through input worksheet
Next: Using Cartesian Cell Names