 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Is this an error in Excel 2003 - on Modified Duration function
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 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. > Today I occasionally checked my spreadsheet and found a wierd result > in it... [quoted text clipped - 9 lines] > > Any idea can help me out? Great thanks  SignatureDave Peterson > Today I occasionally checked my spreadsheet and found a wierd result > in it... [quoted text clipped - 9 lines] > > 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. And you did use the =date() function version, right? Maybe the finance guys/gals will jump in with a good response. > > Today I occasionally checked my spreadsheet and found a wierd result > > in it... [quoted text clipped - 17 lines] > than having just missed one). Having an extra coupon so close to > settlement more than counteracts the longer maturity. SignatureDave Peterson > And you did use the =date() function version, right? > [quoted text clipped - 28 lines] > > - 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. > Lucia...@gmail.com wrote: > > Today I occasionally checked my spreadsheet and found a wierd result [quoted text clipped - 18 lines] > 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. >> Lucia...@gmail.com wrote: >>> Today I occasionally checked my spreadsheet and found a wierd result [quoted text clipped - 18 lines] > 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? > Lucia...@gmail.com wrote: > >> Lucia...@gmail.com wrote: [quoted text clipped - 46 lines] > > - 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... On Apr 27, 12:15 pm, Lucia...@gmail.com wrote: > > Lucia...@gmail.com wrote: > > >> Lucia...@gmail.com wrote: [quoted text clipped - 58 lines] > > - 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.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.