Is this an error in Excel 2003 - on Modified Duration function

[Lucian7L]

Today I occasionally checked my spreadsheet and found a wierd result
in it...
A later maturity date with a shorter modified duration, ceteris
paribus...

U can check, for example, =MDURATION("04-25-08","04-25-2011",
0.0358,0.0358,4,3) and =MDURATION("04-25-08","04-26-2011",
0.0358,0.0358,4,3)....

Meanwhile, if u change the parameters to test, u will find it's a
random relust where it lies.

Any idea can help me out? Great thanks

 

[Dave Peterson]

First, I've never used =mduration() in any excel function until today!

But excel's help says that you should be entering those dates as real dates--not
strings.

Important Dates should be entered by using the DATE function, or as results of
other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day
of May, 2008. Problems can occur if dates are entered as text.

=MDURATION(DATE(2008,4,25),DATE(2011,4,25),0.0358,0.0358,4,3)
=MDURATION(DATE(2008,4,25),DATE(2011,4,26),0.0358,0.0358,4,3)

But even if that results in a problem, I'm not sure what "it's a random relust
where it lies" means.

Maybe you could explain the problem for the financial experts (not me!) who will
be reading your message.

 

[Nick]

Today I occasionally checked my spreadsheet and found a wierd result
in it...
A later maturity date with a shorter modified duration, ceteris
paribus...

U can check, for example, =MDURATION("04-25-08","04-25-2011",
0.0358,0.0358,4,3) and =MDURATION("04-25-08","04-26-2011",
0.0358,0.0358,4,3)....

Meanwhile, if u change the parameters to test, u will find it's a
random relust where it lies.

Any idea can help me out? Great thanks

I can't quite nail what it is doing (I'm different on 6th sig fig) but
the essential thing is that extending the maturity date allows an extra
coupon payment. Coupon dates are generated back from the Maturity date.

In this case it means getting the first coupon on the 26-04-08 (rather
than having just missed one). Having an extra coupon so close to
settlement more than counteracts the longer maturity.

 

[Dave Peterson]

And you did use the =date() function version, right?

Maybe the finance guys/gals will jump in with a good response.

 

[Lucian7L]

And you did use the =date() function version, right?

Maybe the finance guys/gals will jump in with a good response.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks guys all, yes, actually, in my oringinal spreadsheet, the value
day and the maturity day are all generated by date function and the
format is surely of "Date", so, it shouldn't be the format's
problem... Meanwhile, I run more test, I used the rand() to generate a
figure and then make it between 0~10000, use today() as start day,
today()+round(rand()*10000,0) as the maturity day, both in "Date"
format... then I found the problem rose randomly...

Thanks again, fellow.

 

[Lucian7L]

I can't quite nail what it is doing  (I'm different on 6th sig fig) but
the essential thing is that extending the maturity date allows an extra
coupon payment. Coupon dates are generated back from the Maturity date.

In this case it means getting the first coupon on the 26-04-08 (rather
than having just missed one). Having an extra coupon so close to
settlement more than counteracts the longer maturity.

Thank you, Nick. I'm thinking maybe it's the problem of the calendar
in Excel.....still didn't get answer yet, I've asked lots of my
frds...no idea at all.

 

[Nick]

Thank you, Nick. I'm thinking maybe it's the problem of the calendar
in Excel.....still didn't get answer yet, I've asked lots of my
frds...no idea at all.

Sorry I didn't explain myself very well, the string dates are
irrelevant. I did actually give you the real answer. I was only winging
about an error in the sixth sig fig which may be due to excel using an
imprecise iterative solution. It is probably best for you to ignore the
comment.

The trick to understanding is to consider what the actual bond looks
like. It will consist of a number of payments made at dates in the
future, we call these cashflows.

The easiest thing to do is consider a bond with only one or two
cashflows. You can actually do the calculations to price this bond in excel.

An annual bond with maturity in one year will have one cashflow at maturity.

An annual bond with maturity in one year and one day will have two
cashflows a coupon paid tomorrow and the cashflow at maturity.

I'm not sure what you understanding of modified duration is but having
the extra cashflow tomorrow will reduce the overall duration of the
bond, either as a weighted average of cashflows or as the derivative of
price/yield.

Is this OK?

 

[Lucian7L]

Sorry I didn't explain myself very well, the string dates are
irrelevant. I did actually give you the real answer. I was only winging
  about an error in the sixth sig fig which may be due to excel using an
imprecise iterative solution. It is probably best for you to ignore the
comment.

The trick to understanding is to consider what the actual bond looks
like. It will consist of a number of payments made at dates in the
future, we call these cashflows.

The easiest thing to do is consider a bond with only one or two
cashflows. You can actually do the calculations to price this bond in excel.

An annual bond with maturity in one year will have one cashflow at maturity.

An annual bond with maturity in one year and one day will have two
cashflows a coupon paid tomorrow and the cashflow at maturity.

I'm not sure what you understanding of modified duration is but having
the extra cashflow tomorrow will reduce the overall duration of the
bond, either as a weighted average of cashflows or as the derivative of
price/yield.

Is this OK?- Hide quoted text -

- Show quoted text -

Wow, Nick, thank you so much! Yep, your explanation is quite clear, I
did miss the effect of incoming cashflow. But, it seems it's only one
of the all reasons...coz, I ran lots of test, not only the days after
payment day is affected, but some irrelevant day can also have a same
duration as the day before that date...like =MDURATION("28-Apr-08","30-
Nov-13",0.03,0.03,4,3), the MD is same as on 29-Nov-13, actually, both
of the two adjacent dates are not a payment date...
BTW, I didn't clearly catch what you mean by "sixth sig fig", forgive
me, English is not my mother tongue...

 

[Lucian7L]

Wow, Nick, thank you so much! Yep, your explanation is quite clear, I
did miss the effect of incoming cashflow. But, it seems it's only one
of the all reasons...coz, I ran lots of test, not only the days after
payment day is affected, but some irrelevant day can also have a same
duration as the day before that date...like =MDURATION("28-Apr-08","30-
Nov-13",0.03,0.03,4,3), the MD is same as on 29-Nov-13, actually, both
of the two adjacent dates are not a payment date...
BTW, I didn't clearly catch what you mean by "sixth sig fig", forgive
me, English is not my mother tongue...- Hide quoted text -

- Show quoted text -

I checked again, most of the questions rises from the day (or/and the
second day) after a payment day, so, what Nick explained above
accounts for a lot. I didn't neglect sth. before ask such a
question....Thanks, I will go further on this question.