writer
2,661
edits
Changes
Jump to navigation
Jump to search
Line 1:
Line 1: − <div id="6SpaceContent" class="zcontent" align="left">+ − + + + + + + + − where,+ − '''SettlementDate''' - represents the settlement date.+ − '''MaturityDate''' - represents the maturity date.+ − '''Rate''' - represents the rate.+ − '''Price''' - represents the price per $100 face value.+ + + + − '''Redemption''' - is the redemption value per $100 face value.+ − + + + + + + − '''Payments frequency'''+ − annual 1+ − semiannual 2+ + − quarterly 4+ + + + + + + + + + + + + + − '''Basis''' - type of day count basis+ − '''Basis Day count basis'''+ − 0 or omitted US 30/360+ − 1 Actual/actual+ − 2 Actual/360+ + + + − 3 Actual/365+ + + − 4 European 30/360+ + + − </div>+ − + − <div id="1SpaceContent" class="zcontent" align="left">+ − It returns the yield that pays periodic interest.+ + + − '''Formula''':-+ + + − If 1 coupon period or less than Redemption+ − + − YIELD={[(Redemption/100)+(Rate/Frequency)]-+ − + − [(Par/100)+((B/C) x (Rate/Frequency))]} / [(Par/100)+((A/C) x (Rate/Frequency))] x( Frequeny x C)/N − − where, − − B - number of daysfrom the beginning of the period to the settlement date. − − C - number of days in the coupon period. − − N - number of days from settlement to redemption value. − − </div> − ---- − <div id="7SpaceContent" class="zcontent" align="left"> − − If Basis < 0 or Basis > 4 , YIELD returns the #ERROR. − − If Rate < 0 or Price <= 0 or Redemption <=0, it returns the #ERROR. − − If SettlementDate >= MaturityDate, YIELD returns #ERROR. − − If Frequency other than 1, 2 or 4 returns the #ERROR. − − </div> − ---- − <div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left"> − − YIELD − − </div></div> − ---- − <div id="8SpaceContent" class="zcontent" align="left"> − − Lets see an example in (Column2, Row1) − <nowiki>=YIELD(R1C1, R2C1, R3C1, R4C1, R5C1,R6C1,R7C1)</nowiki>+ − YIELD returns 47.950201.+ − Consider another example+ − + − <nowiki>=YIELD(date(2007,1,1), date(2008,2,20), "15%", 89.25,100,1,5)</nowiki> − − It returns #ERROR(Basis=5). − − </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=" " | − <div id="2EditDiv" class="tab active"></div><div id="2Space_Copy" class="zhandles"></div> − | class=" " | Column1 − | class=" " | Column2 − | Column3 − | Column4 − |- class="odd" − | class=" " | Row1 − | class=" " | 1/2/2008 − | class="sshl_f" | 47.950201 − | class=" " | − | class=" sshl_f " | − |- class="even" − | class=" " | Row2 − | class=" " | 2/20/2008 − | class=" sshl_f " | − | class=" " | − | class=" " | − |- class="odd" − | Row3 − | class=" " | 12% − | class=" " | − | class=" SelectTD SelectTD ChangeBGColor" | − <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="even" − | Row4 − | class=" " | 56.268 − | class=" " | − | class=" " | − | class=" " | − |- class="odd" − | class=" " | Row5 − | class=" " | 100 − | class=" " | − | class=" " | − | class=" " | − |- class="even" − | class="sshl_f" | Row6 − | class=" " | 1 − | class=" " | − | class=" " | − | class=" " | − |- class="odd" − | class="sshl_f" | Row7 − | class="sshl_f " | 4 − | class=" " | − | class=" " | − | class=" " | − |} − <div align="left">[[Image:calci1.gif]]</div></div>+ − ----+ +
no edit summary
=YIELD(settlement, maturity, rate, price, redemption, frequency, basis)=
'''YIELD'''(SettlementDate, MaturityDate, Rate, Price, Redemption, Frequency, Basis)
*where <math>settlement</math> is security's settlement date (a date when coupon or a bond is purchased),
*<math>maturity</math> is security's maturity date (a date when coupon or a bond expires),
*<math>rate</math> is security's annual coupon rate,
*<math>price</math> is the price per $100 face value,
*<math>redemption</math> is security's redemption value per $100 face value,
*<math>frequency</math> is the number of coupon payments per year, and
*<math>basis</math> is the type of day count basis to use.
YIELD() calculates the yield on a security or a bond that pays periodic interest.
== Description ==
YIELD(settlement, maturity, rate, price, redemption, frequency, basis)
*Yield is the annual percentage return on an investment or security. Yield is the value obtained by dividing Annual Interest by Bond Price.
Formula:-
If B= Number of days from beginning of coupon period to settlement date,
C= Number of days in coupon period,
N= Number of days from settlement date to redemption date,
<math>YIELD = \frac{(\frac{redemption}{100}+\frac{rate}{frequency})-(\frac{par}{100}+(\frac{B}{C}*\frac{rate}{frequency}))}{\frac{par}{100}+(\frac{B}{C}*\frac{rate}{frequency})}*\frac{frequency*C}{N}</math>
'''Frequency''' - the number of coupon payment per year.
*<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>rate</math> value must be greater than or equal to zero, else Calci displays #N/A error message.
*<math>price</math> and <math>redemption</math> values must be greater than zero, else Calci displays #N/A error message.
*The values for <math>frequency</math> should be 1,2 or 4.
For Annual payment, <math>frequency</math> = 1,
For Semi-annual payment, <math>frequency</math> = 2,
For Quarterly payment, <math>frequency</math> = 4.
*<math>basis</math> value is optional. If omitted, Calci assumes it to be 0.
Below table shows the use of <math>basis</math> values:
{| class="wikitable"
|-
! Basis !! Description
|-
| 0 || US (NASD) 30/360
|-
| 1 || Actual/actual
|-
| 2 || Actual/360
|-
| 3 || Actual/365
|-
| 4 || European 30/360
|}
*If <math>basis</math> value is other than 0 to 4, Calci displays #N/A error message.
== Examples ==
YIELD(settlement, maturity, rate, price, redemption, frequency, basis) function with inputs in order is calculated as follows:
<div id="2SpaceContent" class="zcontent" align="left">
{| id="TABLE3" class="SpreadSheet blue"
|- class="even"
| class="sshl_f" | 2/2/2008
| class="sshl_f" |
|- class="odd"
| class="sshl_f" | 11/2/2010
| class="sshl_f" |
|- class="even"
| class="sshl_f" | 5%
| class="sshl_f" |
|- class="odd"
----
| class="sshl_f" | 97
| class="sshl_f" |
|- class="even"
| class="sshl_f" | $100
| class="sshl_f" |
|- class="odd"
| class="sshl_f" | 2
| class="sshl_f" |
|- class="even"
| class="sshl_f" | 0
| class="sshl_f" |
|}
=YIELD(A1,A2,A3,A4,A5,A6,A7) : Calculates the Yield Value with the inputs in the range A1 to A7. Returns '''6.2%''' as a result.
== See Also ==
*[[Manuals/calci/YIELDDISC | YIELDDISC]]
*[[Manuals/calci/YIELDMAT | YIELDMAT]]
== References ==
*[http://en.wikipedia.org/wiki/Yield_(finance) Yield]
*[http://en.wikipedia.org/wiki/Current_yield Current Yield]
*[http://en.wikipedia.org/wiki/Day_count_convention Basis Convention]