| Line 1: |
Line 1: |
| − | <div id="6SpaceContent" class="zcontent" align="left">
| + | =INTRATE(SettlementDate, MaturityDate, Investment, Redemption, Basis)= |
| | | | |
| − | '''INTRATE'''(SettlementDate, MaturityDate, Investment,Redemption,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>Investment</math> is the amount invested in the security or a bond |
| | + | *<math>Redemption</math> is the amount to be received at maturity of a bond |
| | + | *<math>Basis</math> is the type of day count basis to use |
| | | | |
| − | where,
| + | INTRATE() calculates the interest rate for a fully invested security. |
| | | | |
| − | '''SettlementDate''' - represents the settlement date.
| + | == Description == |
| | | | |
| − | '''MaturityDate''' - represents the maturity date.
| + | INTRATE(SettlementDate, MaturityDate, Investment, Redemption, Basis) |
| | | | |
| − | '''Investment''' - the amount to be invested. | + | *<math>SettlementDate</math> and <math>MaturityDate</math> 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> >= <math>MaturityDate</math>, Calci displays #N/A error message. |
| | + | *If <math>Investment</math> <=0 or <math>Redemption</math> <=0, Calci displays #N/A error message. |
| | + | *<math>Basis</math> value is optional. If omitted, Calci assumes it to be 0. |
| | + | Below table shows the use of <math>Basis</math> values: |
| | | | |
| − | '''Redemption''' - the amount to be received at maturity.
| + | {| class="wikitable" |
| | + | |- |
| | + | ! Basis !! Description |
| | + | |- |
| | + | | 0 || US (NASD) 30/360 |
| | + | |- |
| | + | | 1 || Actual/actual |
| | + | |- |
| | + | | 2 || Actual/360 |
| | + | |- |
| | + | | 3 || Actual/365 |
| | + | |- |
| | + | | 4 || European 30/365 |
| | + | |} |
| | | | |
| − | '''Basis''' - type of day count basis | + | *If <math>Basis</math> value is other than 0 to 4, Calci displays #N/A error message. |
| | + | *Formula: |
| | + | If 'B' is number of days in a year, and 'D' is number of days from 'settlement' to |
| | + | maturity', then INTRATE is calculated as - |
| | | | |
| − | '''Basis Day count basis'''
| + | INTRATE = <math>\frac{\(redemption-investment)}{investment} X \frac{\B}{DIM}</math> |
| | | | |
| − | 0 or omitted US 30/360
| + | == Examples == |
| | | | |
| − | 1 Actual/actual
| + | Consider the following example that shows the use of DURATION function: |
| | | | |
| − | 2 Actual/360
| + | <div id="2SpaceContent" class="zcontent" align="left"> |
| | | | |
| − | 3 Actual/365
| + | {| id="TABLE3" class="SpreadSheet blue" |
| | + | |- class="even" |
| | + | | class="sshl_f" | 11/1/2010 |
| | + | | class=" " | |
| | + | | class=" " | |
| | | | |
| − | 4 European 30/360
| + | |- class="odd" |
| | + | | class="sshl_f" | 2/20/2011 |
| | + | | class=" " |2/2/2014 |
| | + | | class=" " | |
| | | | |
| − | </div>
| + | |- class="even" |
| − | ----
| + | | class="sshl_f" | 500000 |
| − | <div id="1SpaceContent" class="zcontent" align="left">
| + | | class=" " |7000 |
| | + | | class=" " | |
| | | | |
| − | It returns the interest rate for a invested.
| + | |- class="odd" |
| | + | | class="sshl_f" | 800000 |
| | + | | class=" " |9000 |
| | + | | class=" " | |
| | | | |
| − | '''Formula''':-
| + | |- class="even" |
| | + | | class="sshl_f" | 4 |
| | + | | class=" " |1 |
| | + | | class=" " | |
| | | | |
| − | INTRATE =(Redemption-Investment)/Investment x( T/D)
| + | |- class="odd" |
| | + | | class="sshl_f" | |
| | + | | class=" " | |
| | + | | class=" " | |
| | | | |
| − | where,
| + | |} |
| − | | |
| − | T - number of days in a year, according on the year basis.
| |
| − | | |
| − | D - number of days from settlement to maturity.
| |
| − | | |
| − | </div>
| |
| − | ----
| |
| − | <div id="7SpaceContent" class="zcontent" align="left">
| |
| − | | |
| − | If Basis < 0 or Basis > 4 , INTRATE returns the #ERROR.
| |
| − | | |
| − | If Investment <= 0 or Redemption <= 0, it
| |
| − | | |
| − | returns the#ERROR.
| |
| − | | |
| − | If SettlementDate >= MaturityDate, INTRATE returns #ERRROR.
| |
| − | | |
| − | </div>
| |
| − | ----
| |
| − | <div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
| |
| | | | |
| − | INTRATE | + | =INTRATE(A1,A2,A3,A4,A5) displays ''1.981651376146789'' as a result. |
| | + | =INTRATE(DATE(2013,1,1),B2,B3,B4,B5) displays ''0.2622154311211943'' as a result. |
| | | | |
| − | </div></div>
| + | == See Also == |
| − | ----
| |
| − | <div id="8SpaceContent" class="zcontent" align="left">
| |
| | | | |
| − | Lets see an example in (Column1, Row6)
| + | *[[Manuals/calci/RATE | RATE]] |
| | | | |
| − | <nowiki>=INTRATE(R1C1, R2C1, R3C1, R4C1, R5C1)</nowiki>
| + | == References == |
| − | | |
| − | INTRATE returns 1.981651.
| |
| − | | |
| − | Consider another example
| |
| − | | |
| − | <nowiki>=INTRATE(date(1/1/2007),date(2/2/2008),5000,4000,5)</nowiki>
| |
| − | | |
| − | 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">
| |
| − | | |
| − | {| id="TABLE3" class="SpreadSheet blue"
| |
| − | |- class="even"
| |
| − | | class=" " |
| |
| − | | class=" " | Column1
| |
| − | | 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>Column2
| |
| − | | Column3
| |
| − | | Column4
| |
| − | |- class="odd"
| |
| − | | class=" " | Row1
| |
| − | | class=" " | 11/1/2007
| |
| − | | 0
| |
| − | | 0
| |
| − | | 0
| |
| − | |- class="even"
| |
| − | | class=" " | Row2
| |
| − | | class=" " | 2/20/2008
| |
| − | | 0
| |
| − | | 0
| |
| − | | 0
| |
| − | |- class="odd"
| |
| − | | Row3
| |
| − | | class=" " | 500000
| |
| − | | 0
| |
| − | | 0
| |
| − | | 0
| |
| − | |- class="even"
| |
| − | | Row4
| |
| − | | class=" " | 800000
| |
| − | | 0
| |
| − | | 0
| |
| − | | 0
| |
| − | |- class="odd"
| |
| − | | class=" " | Row5
| |
| − | | class="sshl_f " | 4
| |
| − | | 0
| |
| − | | 0
| |
| − | | 0
| |
| − | |- class="even"
| |
| − | | Row6
| |
| − | | class="sshl_f" | 1.981651
| |
| − | | 0
| |
| − | | 0
| |
| − | | 0
| |
| − | |}
| |
| | | | |
| − | <div align="left">[[Image:calci1.gif]]</div></div>
| + | *[http://en.wikipedia.org/wiki/Interest_rate_derivative Interest Rate Derivative] |
| − | ----
| + | *[http://en.wikipedia.org/wiki/Day_count_convention Day Count Basis] |