Interest Calculation - Please Help!

[Jim]

I am trying to write a formula that will calculate the
interest accrued at a given date.

Assume:
$1000 Investment
10% Interest Rate Compounded Annualy
360 Day Year
Interest Accrues daily

At the end of year 1, the accrued interest should be 10.0
At the end of year 2, the accrued interest should be 21.0
At the end of year 3, the accrued interest should be 33.1

I have tried using the ACCRINTM function but I am not
getting the correct results.

Remember, I need to be able to figure out the accrued
interest at any given date, not just at the end of each
year.

Thanks for your help!

 

[Norman Harker]

Hi Jim!

You've made a mistake in your calcs but try:

With 12-Jan-2001 in A1 and 12-Jan-2004 in B1

=1000*(1+((1+10%)^(1/360)-1))^DAYS360(A1,B1)-1000
Returns: 330.999999999989

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jim]

That works!
I was trying to use...
1000*(1+10%)^((B1-A1)/365)-1000

Can you explain the formula to me and how it works?

Thanks.
Jim

 

[Norman Harker]

Hi Jim!

Re:
=1000*(1+((1+10%)^(1/360)-1))^DAYS360(A1,B1)-1000

You quoted a rate of 10% compounded annually and a 360 day count
basis.

So we need to get the daily effective equivalent:

=(1+10%)^(1/360)-1

We need the number of days using the DAYS360 function.

=DAYS360(A1,B1) [I've assumed that NASD method is used]

Conversion factor can now be found:

=1+((1+10%)^(1/360)-1))^DAYS360(A1,B1)

To calculate the interest. Multiply the amount by the conversion
factor and deduct the amount.

=1000*(1+((1+10%)^(1/360)-1))^DAYS360(A1,B1)-1000

There were a few things wrong with your approach.

You were effectively using a 365 day assumption for your equivalent
rate. Also you were using actual days rather than 360 day year
assumption.

But comments on use of a 360 day assumption these days are best
written down and sent in a plain brown envelope. It was a method
designed for ease of use in days when calculation tools were more
primitive.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jim]

Thank You...
This was very helpful!

 

[Norman Harker]

Hi Jim!

Never any problem in these newsgroups to explain why / how something
works. It's not just a case of providing a quick fix. Anyways, I'm a
hero <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.