Changes

Jump to navigation Jump to search

Manuals/calci/MDURATION (view source)

Revision as of 23:01, 18 January 2014

642 bytes removed ,  23:01, 18 January 2014
no edit summary
Line 1: Line 1: −
<div id="6SpaceContent" class="zcontent" align="left">
+
=MDURATION(SettlementDate, MaturityDate, Coupon, Yield, Frequency, Basis)=
   −
'''MDURATION'''(SettlementDate, MaturityDate, Coupon,Yield, Frequency, Basis)
+
*Where <math>SettlementDate</math> is the security's settlement date (a date when coupon or a bond is purchased),  
 +
*<math>MaturityDate</math> is the security's maturity date (a date when coupon or a bond expires),  
 +
*<math>Coupon</math> is the security's annual coupon rate,  
 +
*<math>Yield</math> is the security's annual yield,  
 +
*<math>Frequency</math> is the number of coupon payments per year, and
 +
*<math>Basis</math> is the type of day count basis to use.
   −
where,
+
MDURATION() gives the modified Macauley duration of a security for an assumed par value of $100.
   −
'''SettlementDate''' - represents the settlement date.
+
== Description ==
   −
'''MaturityDate''' - represents the maturity date.
+
MDURATION(SettlementDate, MaturityDate, Coupon, Yield, Frequency, Basis)
   −
'''Coupon''' - the annual coupon rate.
+
MDURATION() or modified duration calculates the percentage derivative of price with respect to yield.  
 +
Formula:
   −
'''Yield''' - the annual yield.
+
<math>MDURATION = \frac{DURATION}{1 + \frac{Yield}{Frequency}}</math>
   −
'''Frequency''' - the number of coupon payment per year.
+
*<math>SettlementDate</math> and <math>MaturityDate</math> dates should be entered either in 'date format' or 'dates returned using formulas'. If dates are not valid, Calci displays #N/A error message.
 +
*If <math>SettlementDate</math> date ≥ <math>MaturityDate</math> date, Calci displays #N/A error message.
 +
*<math>Coupon</math> and <math>Yield</math> values must be greater than or equal to zero.
 +
*The values for <math>Frequency</math> should be 1,2 or 4.
 +
For Annual payment, frequency = 1,
   −
'''Payments              frequency'''
+
For Semi-annual payment, frequency = 2,
   −
annual                     1
+
For Quarterly payment, frequency = 4.
   −
semiannual               2
+
*<math>Basis</math> value is optional. If omitted, Calci assumes it to be 0.
 +
Below table shows the use of <math>Basis</math> values:
   −
quarterly                  4
+
{| class="wikitable"
−
 
+
|-
−
'''Basis''' - type of day count basis
+
! Basis !! Description
−
 
+
|-
−
'''Basis              Day count basis'''
+
| 0 || US (NASD) 30/360
−
 
+
|-
−
0 or omitted    US 30/360
+
| 1 || Actual/actual  
−
 
+
|-
−
1                     Actual/actual
+
| 2 || Actual/360
−
 
+
|-
−
2                     Actual/360
+
| 3 || Actual/365
−
 
+
|-
−
3                     Actual/365
+
| 4 || European 30/360  
−
 
+
|}
−
4                     European 30/360
  −
 
  −
</div>
  −
----
  −
<div id="1SpaceContent" class="zcontent" align="left">
  −
 
  −
It returns the Macauley duration for a security with an assumed par value of $100.
  −
 
  −
'''Formula''':-
  −
 
  −
MDURATION = duration /(1+(Market yield/coupon payemnt per year))
  −
 
  −
</div>
  −
----
  −
<div id="7SpaceContent" class="zcontent" align="left">
  −
 
  −
If Basis &lt; 0 or Basis &gt; 4 , MDURATION returns the #ERROR.
  −
 
  −
If Yield &lt; 0 or Coupon &lt; 0, it returns the #ERROR.
  −
 
  −
If SettlementDate &gt;= MaturityDate, MDURATION returns #ERRROR.
  −
 
  −
If Frequency other than 0 , 1 or 4 returns the #ERROR.
  −
 
  −
</div>
  −
----
  −
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
  −
 
  −
MDURATION
  −
 
  −
</div></div>
  −
----
  −
<div id="8SpaceContent" class="zcontent" align="left">
  −
 
  −
Lets see an example in (Column2, Row1)
  −
 
  −
<nowiki>=MDURATION(R1C1, R2C1, R3C1, R4C1, R5C1,R6C1)</nowiki>
     −
MDURATION returns 0.726144.
+
*If <math>Basis</math> value is other than 0 to 4, Calci displays #N/A error message.
   −
Consider another example
+
== Examples ==
   −
<nowiki>=MDURATON(date(1/1/2007),date(2/2/2008),"5%","4%",5,1)</nowiki>
+
Consider the following example that shows the use of MDURATION function:
   −
It returns #ERROR.
  −
  −
</div>
  −
----
  −
<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div>
  −
----
  −
<div id="4SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Remarks </div></div>
  −
----
  −
<div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div>
  −
----
  −
<div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div>
  −
----
   
<div id="2SpaceContent" class="zcontent" align="left">
 
<div id="2SpaceContent" class="zcontent" align="left">
    
{| id="TABLE3" class="SpreadSheet blue"
 
{| id="TABLE3" class="SpreadSheet blue"
 
|- class="even"
 
|- class="even"
 +
| class=" " | September 10, 2010
 +
| class=" " |
 
| class=" " |
 
| class=" " |
−
| Column1
+
 
−
| class="  " | Column2
  −
| Column3
  −
| Column4
   
|- class="odd"
 
|- class="odd"
−
| class=" " | Row1
+
| class="sshl_f" | September 10, 2014
−
| class=" " | 2/2/2008
  −
| class="sshl_f" | 0.726144
   
| class=" " |
 
| class=" " |
 
| class=" " |
 
| class=" " |
 +
 
|- class="even"
 
|- class="even"
−
| class=" " | Row2
+
| class=" " | 6%
−
| class=" " | 11/2/2008
  −
| class="  SelectTD ChangeBGColor SelectTD" |
  −
<div id="2Space_Handle" class="zhandles" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" class="zhandles" title="Click and Drag over to AutoFill other cells."></div><div id="2Space_Drag" class="zhandles" title="Click and Drag to Move/Copy Area.">[[Image:copy-cube.gif]]  </div>
   
| class=" " |
 
| class=" " |
 
| class=" " |
 
| class=" " |
 +
 
|- class="odd"
 
|- class="odd"
−
| Row3
+
| class=" " | 9.0%
−
| class=" " | 5%
  −
| class=" " |
   
| class=" " |
 
| class=" " |
 
| class=" " |
 
| class=" " |
 +
 
|- class="even"
 
|- class="even"
−
| Row4
+
| class=" " | 2
−
| class=" " | 8%
  −
| class=" " |
   
| class=" " |
 
| class=" " |
 
| class=" " |
 
| class=" " |
 +
 
|- class="odd"
 
|- class="odd"
−
| class=" " | Row5
+
| class=" " | 1
−
| class=" " | 4
+
| class=" " |  
−
| class=" " |
  −
| class=" " |
  −
| class=" " |
  −
|- class="even"
  −
| Row6
  −
| class="  sshl_f " | 1
  −
| class=" " |
  −
| class=" " |
   
| class=" " |
 
| class=" " |
 +
 
|}
 
|}
   −
<div align="left">[[Image:calci1.gif]]</div></div>
+
=MDURATION(A1,A2,A3,A4,A5,A6) ''displays 3.8035273385670787'' as a result.
−
----
+
=MDURATION(DATE(2013,6,1),DATE(2013,12,31),"8%","9%",1,1) ''displays 0.9174311926605504'' as a result.
 +
 
 +
== See Also ==
 +
 
 +
*[[Manuals/calci/DURATION | DURATION]]
 +
 
 +
== References ==
 +
 
 +
*[http://en.wikipedia.org/wiki/Bond_duration#Macaulay_duration Macaulay Duration]
 +
*[http://en.wikipedia.org/wiki/Bond_duration#Modified_duration Modified Duration]
 +
*[http://en.wikipedia.org/wiki/Day_count_convention Day Count Basis]
Swapna
writer
2,661

edits

Retrieved from "http://wiki.zcubes.com/Special:MobileDiff/197288"

Navigation menu