Calculating acruing interest

[DRKENNE]

I'm looking for a formula (simple I hope) that would calculate interest
that's been accruing at an annual rate of 10% on $10,500 that should have
been paid on 3/6/07 and no payment has been made. Since there is no end date
at this time, I'd be using the date (today) of the calculation.

 

[Joe User]

I'm looking for a formula (simple I hope) that would calculate interest
that's been accruing at an annual rate of 10% on $10,500 that should have
been paid on 3/6/07 and no payment has been made. Since there is no end date
at this time, I'd be using the date (today) of the calculation.

So many details are missing. I don't see how anyone can offer you a
formula, much less a simple one, unless they get lucky in their
ass-u-me-tions.

For starters, does interest compound? If so, what's the compounding
frequency?

You say that interest "has been accruing" on $10,500. When did interest
start accruing?

Finally, what type of investment is this? That might help in providing the
"simplest" solution.

You might take a look at the ACCRINT and ACCRINTM functions. But if you use
them blindly, their results might not be relevant. GIGO.

 

[Joe User]

PS....

So many details are missing. I don't see how anyone can offer
you a formula, much less a simple one, unless they get lucky in
their ass-u-me-tions.

Another potentially important question: is the 10% the simple (nominal)
annual interest rate, or is it the compounded annual rate (aka yield)?

Just to see how lucky I might get (usually not!)....

If $10,500 was invested on 3/6/2006 (a year before 3/6/2007, the date you
mention) and interest is paid annually at 10% compounded annually, the
following might estimate the interest accrued to date:

A1, number of full years:
=DATEDIF(DATE(2006,3,6),TODAY(),"y")

A2, accrued interest:
=FV(10%,A1,0,-10500)
* (1 + (DATE(2006+A1,3,6)-TODAY())*10%/365)
- 10500


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

 

[DRKENNE]

The money was due on 3/6/07 and the interest is compounded annually starting

 

[Joe User]

The money was due on 3/6/07 and the interest is
compounded annually starting on that date.

So I think my previous lucking guess will work for you, with one small
change, namely changing 2006 to 2007.

A1, number of full years:
=DATEDIF(DATE(2007,3,6),TODAY(),"y")

A2, accrued interest:
=FV(10%,A1,0,-10500)
* (1 + (DATE(2007+A1,3,6)-TODAY())*10%/365)
- 10500

If DATEDIF results in a #NAME? error and you cannot or do not want to load
the ATP, I believe the following alternative will work:

A1, number of full years:
=YEAR(TODAY()) - 2007
- (TODAY() < DATE(YEAR(TODAY()),3,6))


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