ZCubes Wiki

Difference between revisions of "Manuals/calci/MDURATION"

(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> '''MDURATION'''(SettlementDate, MaturityDate, Coupon,Yield, Frequency, Basis) where, '''SettlementDate''' - r...")
 
 
(9 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
<div id="6SpaceContent" class="zcontent" align="left">
+
<div style="font-size:30px">'''MDURATION(Settlement, Maturity, Coupon, Yield, Frequency, Basis)'''</div><br/>
  
−
'''MDURATION'''(SettlementDate, MaturityDate, Coupon,Yield, Frequency, Basis)
+
*Where <math>Settlement</math> is the security's settlement date (a date when coupon or a bond is purchased),
 +
*<math>Maturity</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.
 +
**MDURATION(), returns the Macauley modified duration for a security with an assumed par value of $100.
  
−
where,
+
== Description ==
  
−
'''SettlementDate''' - represents the settlement date.
+
MDURATION(Settlement, Maturity, Coupon, Yield, Frequency, Basis)
  
−
'''MaturityDate''' - represents the maturity date.
+
*MDURATION() or modified duration calculates the percentage derivative of price with respect to yield.  
 +
*Formula-
 +
<math>MDURATION = \frac{DURATION}{1 + \frac{Yield}{Frequency}}</math>
  
−
'''Coupon''' - the annual coupon rate.
+
*<math>Settlement</math> and <math>Maturity</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>Settlement</math> date ≥ <math>Maturity</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,
  
−
'''Yield''' - the annual yield.
+
For Semi-annual payment, frequency = 2,
  
−
'''Frequency''' - the number of coupon payment per year.
+
For Quarterly payment, frequency = 4.
  
−
'''Payments              frequency'''
+
*<math>Basis</math> value is optional. If omitted, Calci assumes it to be 0.
 +
Below table shows the use of <math>Basis</math> values:
  
−
annual                     1
+
{| class="wikitable"
−
 
+
|-
−
semiannual               2
+
! Basis !! Description
−
 
+
|-
−
quarterly                  4
+
| 0 || US (NASD) 30/360
−
 
+
|-
−
'''Basis''' - type of day count basis
+
| 1 || Actual/actual  
−
 
+
|-
−
'''Basis              Day count basis'''
+
| 2 || Actual/360
−
 
+
|-
−
0 or omitted    US 30/360
+
| 3 || Actual/365
−
 
+
|-
−
1                     Actual/actual
+
| 4 || European 30/360  
−
 
+
|}
−
2                     Actual/360
 
−
 
 
−
3                     Actual/365
 
−
 
 
−
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.
 
  
−
Consider another example
+
*If <math>Basis</math> value is other than 0 to 4, Calci displays #N/A error message.
  
−
<nowiki>=MDURATON(date(1/1/2007),date(2/2/2008),"5%","4%",5,1)</nowiki>
+
== Examples ==
  
−
It returns #ERROR.
+
Consider the following example that shows the use of MDURATION function:
  
−
</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.4357631246138873'' as a result.
−
----
+
=MDURATION(DATE(2013,6,1),DATE(2013,12,31),"8%","9%",1,1) ''displays 0.9174311926605504'' as a result.
 +
 
 +
==Related Videos==
 +
 
 +
{{#ev:youtube|hZMo7jVhfaY|280|center|Modified Duration}}
 +
 
 +
== 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]
 +
 
 +
 
 +
 
 +
*[[Z_API_Functions | List of Main Z Functions]]
 +
 
 +
*[[ Z3 |  Z3 home ]]

Latest revision as of 17:19, 22 August 2018

MDURATION(Settlement, Maturity, Coupon, Yield, Frequency, Basis)


  • Where is the security's settlement date (a date when coupon or a bond is purchased),
  • is the security's maturity date (a date when coupon or a bond expires),
  • is the security's annual coupon rate,
  • is the security's annual yield,
  • is the number of coupon payments per year, and
  • is the type of day count basis to use.
    • MDURATION(), returns the Macauley modified duration for a security with an assumed par value of $100.

Description

MDURATION(Settlement, Maturity, Coupon, Yield, Frequency, Basis)

  • MDURATION() or modified duration calculates the percentage derivative of price with respect to yield.
  • Formula-

 

  •   and   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   date ≥   date, Calci displays #N/A error message.
  •   and   values must be greater than or equal to zero.
  • The values for   should be 1,2 or 4.

For Annual payment, frequency = 1,

For Semi-annual payment, frequency = 2,

For Quarterly payment, frequency = 4.

  •   value is optional. If omitted, Calci assumes it to be 0.

Below table shows the use of   values:

Basis Description
0 US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360
  • If   value is other than 0 to 4, Calci displays #N/A error message.

Examples

Consider the following example that shows the use of MDURATION function:

September 10, 2010
September 10, 2014
6%
9.0%
2
1 
=MDURATION(A1,A2,A3,A4,A5,A6) displays 3.4357631246138873 as a result.
=MDURATION(DATE(2013,6,1),DATE(2013,12,31),"8%","9%",1,1) displays 0.9174311926605504 as a result.

Related Videos

Modified Duration

See Also

References


Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/MDURATION&oldid=211175"