Please check my use of the ACCRINTM function.

[Nth]

Hi,
I'm having trouble with an Excel financial function mainly because I don't
really understand the financial jargon that is being used to explain it.
I want to check that
a) I am using the right function and then
b) that I'm using it correctly.

I'll try to explain the situation in layman's terms :

My friend, Mr X, placed some money in a current account in the bank where
his money gains no interest.

I told him he'd be better off using a savings account where
- even if the interest is small -
he would make a serious benefit in the long term because
the amount in there is quite important and
it stays in over a long period.
So now I'm trying to prove this to him using Excel.

The interest is calculated from day to day.
So I'm using the ACCRINTM function calculating (or so I hope)
the interest accrued between two movements.

Let's say the savings account pays an annual rate of 3.5% and is
1. Opened on Jan 1st with 1000 Euros.
2. On Jan 15 , I pay a bill for 500 Euros.
3. On Jan 30th, I receive a cheque for 400 Euros.

I'm recording each movement on a row by row basis,
using for "issue" the date of the movement,
using for "settlement" the date of the next movement,
using for "par" the balance remaining in the account between the two
movements,
and on the last row, for the "settlement" I use todays date.

ACCRINTM ( Date(2008; 01;01) ; Date(2008; 01;15) ; 3.5% ; 1000 ; 4 )
ACCRINTM ( Date(2008; 01;15) ; Date(2008; 01;30) ; 3.5% ; 500 ; 4 )
ACCRINTM ( Date(2008; 01;30) ; Date(... Now ) ; 3.5% ; 900 ;
4 )

Then I sum all the results and I tell Mr X that that's the amount he lost!
But am I right?

TIA

Nth

 

[Fred Smith]

No, you're not.

ACCRINTM is used to calculate the accrued interest owing when you purchase a
bond. Suppose you buy a bond today that last paid interest on June 15th. You
will receive the full semi-annual payment on December 15th, but you're not
entitled to this full amount, because you didn't own the bond for the entire
period.. Therefore, you owe the seller the interest earned between June 15th
and now (the "accrued interest"). In your formulas, rather than calculating
15 days interest, Accrintm is calculating 168 days interest.

To do what you want, you don't need to use functions at all. The formula you
need for interest earned during the period is:

=IntRate*(EndDate-StartDate)/365

Regards,
Fred.

 

[Nth]

Thanks Fred,
But I don't see the Amount in your formula.

So should that not be
=IntRate*(EndDate-StartDate)/365 * Amount ?

Nth

 

[Nth]

So should that not be

=IntRate*(EndDate-StartDate)/365 * Amount ?

No Ive just pasted that in and its obviously not right either. ......
Nth

 

[Nth]

I've posted an image here to show what I'm doing in my table.
http://www.fransysco.net/interest/interests.gif

Ther's examples in the rows.
It seems to make sense.
-------------------------------------------------------
Microsoft says :

a.. ACCRINTM is calculated as follows:


Which is pretty much the same as what your wrote, except that the par
(Amount) is included.)

where:

a.. A = Number of accrued days counted according to a monthly basis.
For interest at maturity items, the number of days from the issue date to
the maturity date is used.

D = Annual Year Basis.

Example


1
2
3
4
5
6
A B
Data Description
April 1, 2008 Issue date
June 15, 2008 Maturity date
10.0% Percent coupon
$1,000 Par value
3 Actual/365 basis (see above)
Formula Description (Result)
=ACCRINTM(A2,A3,A4,A5,A6) The accrued interest for the terms
above (20.54795)


Nth

 

[Nth]

Well, pasting a snippet form a MS help page was not a good idea.
The page is illegible and there seems to be an attached file include which
will maybe scare off some XL gurus.
No harm intended, I assure you.

Nth

 

[Nth]

Pls check this out
http://www.fransysco.net/interest/interests.gif

Looks like your suggestion and the ACCRINTM function do the same thing.
They come up with the same results.

Nth

 

[Fred Smith]

That's because you're using intervals that are exactly 6 months apart, which
happens to match the bond payment period.

Try them with any other period, like 15 days which you used in your initial
example.

Regards,
Fred.

 

[Nth]

Try them with any other period, like 15 days which you used in your
initial
example.

Hi Fred,
Nice to see you back,
There you go .... a new pic with irregular dates,
http://www.fransysco.net/interest/interests_irregular_dates.gif

Theres a difference between the
ACCRINT function - and the -
ACCRINTM function.

ACCRINT requires intervals.
But I'm using ACCRINTM which has a maturity ( = settlement ?) date.
So there's an issue date and a settlement date, but no (other) periods.

Nth

 

[Fred Smith]

You're right. ACCRINTM should give the same results as my formula. I, of
course, was thinking of ACCRINT, which is substantially different.

Sorry for leading you astray.

Fred.

 

[Nth]

Ouf !
Now Mr X is going to have to sit up and take notice !
Bye and thanks.
Nth