Calculate compound interest

[vinman]

Hello, I was wondering if anyone could help with this problem. I a
trying to write a sheet to calculate the total compounded interest ONL
for my credit card transactions. A1 will be used for the transactio
date, B1 will be info on the transaction, C1 will be the amount of th
transaction, and (what I need help with!), I would like D1 to show th
TOTAL interest accrued on that transaction from the date purcased in A
to present (based on a daily compound rate of 0.05178%). I'm sort of
rookie at Excel, but I couldn't find a function that would allow me t
do this. Any and all help would be appreciated. Thank you.

Vinma

 

[Kevin Stecyk]

vinman wrote...

Hello, I was wondering if anyone could help with this problem. I am
trying to write a sheet to calculate the total compounded interest ONLY
for my credit card transactions. A1 will be used for the transaction
date, B1 will be info on the transaction, C1 will be the amount of the
transaction, and (what I need help with!), I would like D1 to show the
TOTAL interest accrued on that transaction from the date purcased in A1
to present (based on a daily compound rate of 0.05178%). I'm sort of a
rookie at Excel, but I couldn't find a function that would allow me to
do this. Any and all help would be appreciated. Thank you.

Vinman,

Enter this formula in D1
=((1+ 0.05178%)^(TODAY()-A1)-1)*C1

Let's try to understand the equation.

First, we are simply getting the accumulated interest rate
= ( 1+i ) ^ days_of_compounding - 1
=(1+ 0.05178%)^(TODAY()-A1)-1

Next, we multiply the accumlated interest rate by the amount of the purchase
=((1+ 0.05178%)^(TODAY()-A1)-1)*C1

Hope this helps.

Best regards,
Kevin

 

[vinman]

I'll check it out, THANK YOU!!!! I've been muddling around trying t
find the equation, only to get errors all over the place. Let you kno
how it works, trying right no

 

[vinman]

THAT"S IT!!!!!!!! Now I've forgotten how to shift the formula to th
other cells, but I'll figure how I got there before. Thank you so much
I would have spent days to come up with a really ugly version of wha
you just gave me! ... and it was probably a no-brainer for you ... yo
made my day, I can't thank you enough. YEEEEEEEEEE-HAWWWWWWWWW Hell
Cleveland!


Thanks, ever so much, Vinman

 

[vinman]

... and I think I just figured out the equation as well, in Excel term
I mean, you broke it down to a newbie's level, thanks.


Vinma

 

[vinman]

.... remembered how to paste the equation now, you're godsent ....


Vinma

 

[Kevin Stecyk]

Vinman,

You are most welcome. Glad the solution worked for you.

Best regards,
Kevin

 

[Guest]

----- Kevin Stecyk wrote: ----

vinman wrote..

Hello, I was wondering if anyone could help with this problem. I a
trying to write a sheet to calculate the total compounded interest ONL
for my credit card transactions. A1 will be used for the transactio
date, B1 will be info on the transaction, C1 will be the amount of th
transaction, and (what I need help with!), I would like D1 to show th
TOTAL interest accrued on that transaction from the date purcased in A
to present (based on a daily compound rate of 0.05178%). I'm sort of
rookie at Excel, but I couldn't find a function that would allow me t
do this. Any and all help would be appreciated. Thank you

Vinman

Enter this formula in D
=((1+ 0.05178%)^(TODAY()-A1)-1)*C

Let's try to understand the equation

First, we are simply getting the accumulated interest rat
= ( 1+i ) ^ days_of_compounding -
=(1+ 0.05178%)^(TODAY()-A1)-

Next, we multiply the accumlated interest rate by the amount of the purchas
=((1+ 0.05178%)^(TODAY()-A1)-1)*C

Hope this helps

Best regards
Kevi

 

[Guest]

I need an Excel formula that will compound interest daily, monthly or yearly. I would greatly appreciate some help.

----- Kevin Stecyk wrote: -----

vinman wrote...

Hello, I was wondering if anyone could help with this problem. I am
trying to write a sheet to calculate the total compounded interest ONLY
for my credit card transactions. A1 will be used for the transaction
date, B1 will be info on the transaction, C1 will be the amount of the
transaction, and (what I need help with!), I would like D1 to show the
TOTAL interest accrued on that transaction from the date purcased in A1
to present (based on a daily compound rate of 0.05178%). I'm sort of a
rookie at Excel, but I couldn't find a function that would allow me to
do this. Any and all help would be appreciated. Thank you.

Vinman,

Enter this formula in D1
=((1+ 0.05178%)^(TODAY()-A1)-1)*C1

Let's try to understand the equation.

First, we are simply getting the accumulated interest rate
= ( 1+i ) ^ days_of_compounding - 1
=(1+ 0.05178%)^(TODAY()-A1)-1

Next, we multiply the accumlated interest rate by the amount of the purchase
=((1+ 0.05178%)^(TODAY()-A1)-1)*C1

Hope this helps.

Best regards,
Kevin

 

[Norman Harker]

Hi Byron!

The main difficulty of calculating compound interest is the
calculation of the appropriate rate for the period of compounding.

For example, banks (usually) quote interest using nominal rates
compounded monthly or by quoting the annual effective interest rate.
However, interest is charged at the *equivalent* daily effective rate
on the daily outstanding balance. The quoted rate basis has to be
converted to the daily effective equivalent.

Interest conversions use the following formula of equivalence:

Where Nomx and Nomy are nominal rates compounded (respectively) at
frequencies Freqx and Freqy:

(1+Nomx/Freqx)^Freqx=(1+Nomy/Freqy)^Freqy

You should note that Nomx/Freqx is the effective rate for the
frequency used. Also, if the frequency is 1 times per year, then the
nominal rate is the annual effective rate.

So to calculate compound interest daily, monthly or yearly we need to
ensure that we have the daily monthly or annual effective rate of
interest to put into the basic compound interest formula

=PV*(1+rate)^n
where:
PV is the amount to be compounded;
and rate is the effective rate per period of time "counted" by n.

If it the interest we are wanting then we use:

=PV*(1+rate)^n-PV

We can use the PV function to calculate the accumulation. However,
note that the function "knows" nothing about the nature of the period
counted; they are just periods of time. Rates used in PV functions
must be the effective rate per period of time "counted" by the nper
argument.

You'll find these issues covered in any financial maths text, or in an
Excel context you'll find them covered in John Walkenbach's Excel 2003
Formulas (don't worry about the 2003 bit because changes between
versions are clearly flagged).

I've also got some tutorial type files if you send a direct email to
the address below.