Difference between revisions of "Manuals/calci/PRICE"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> '''PRICE'''(SettlementDate, MaturityDate, Rate,Yield, Redemption, Frequency, Basis) where, '''SettlementDate'''...")
 
 
(21 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
<div id="6SpaceContent" class="zcontent" align="left">
+
<div style="font-size:30px">'''PRICE (Settlement,MaturityDate,Rate,Yield,RedemptionValue,Frequency,Basis)'''</div><br/>
 +
*where <math>Settlement</math> is security's settlement date (a date when coupon or a bond is purchased),
 +
*<math>MaturityDate</math> is security's maturity date (a date when coupon or a bond expires),
 +
*<math>Rate</math> is security's annual coupon rate,
 +
*<math>Yield</math> is security's annual yield,
 +
*<math>RedemptionValue</math> is security's redemption value per &#36;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.
 +
**PRICE(), returns the price per $100 face value of a security that pays periodic interest.
  
−
'''PRICE'''(SettlementDate, MaturityDate, Rate,Yield, Redemption, Frequency, Basis)
+
== Description ==
  
−
where,
+
PRICE(Settlement,MaturityDate,Rate,Yield,RedemptionValue,Frequency,Basis)
  
−
'''SettlementDate''' - represents the settlement date.
+
*The function returns the price per &#36;100 face value of security that pays periodic interest.
 +
*<math>settlement</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>Settlement</math> date ≥ <math>MaturityDate</math> date, Calci displays #N/A error message.
 +
*<math>Rate</math> and <math>yield</math> values must be greater than or equal to zero, else Calci displays #N/A error message.
 +
*<math>RedemptionValue</math> value 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,
  
−
'''MaturityDate''' - represents the maturity date.
+
For Semi-annual payment, <math>Frequency</math> = 2,
  
−
'''Rate''' - the annual coupon rate.
+
For Quarterly payment, <math>Frequency</math> = 4.
  
−
'''Yield''' - the annual yield.
+
*<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 - represents the redemption value per $100 face value.
+
{| class="wikitable"
 +
|-
 +
! Basis !! Description
 +
|-
 +
| 0 || US (NASD) 30/360
 +
|-
 +
| 1 || Actual/actual
 +
|-
 +
| 2 || Actual/360
 +
|-
 +
| 3 || Actual/365
 +
|-
 +
| 4 || European 30/365
 +
|}
  
−
'''Frequency''' - the number of coupon payment per year.
+
*If <math>Basis</math> value is other than 0 to 4, Calci displays #N/A error message.
  
−
'''Payments              frequency'''
+
== Examples ==
  
−
annual                     1
+
PRICE (Settlement,MaturityDate,Rate,Yield,RedemptionValue,Frequency,Basis)  function with inputs in order is calculated as follows:
  
−
semiannual               2
+
<div id="2SpaceContent" class="zcontent" align="left">
  
−
quarterly                  4
+
{| id="TABLE3" class="SpreadSheet blue"
 +
|- class="even"
 +
| class="sshl_f" | 2/2/2008
 +
| class="sshl_f" |
  
−
'''Basis''' - type of day count basis
+
|- class="odd"
 +
| class="sshl_f" | 11/2/2016
 +
| class="sshl_f" |
  
−
'''Basis              Day count basis'''
+
|- class="even"
 +
| class="sshl_f" | 5%
 +
| class="sshl_f" |
  
−
0 or omitted    US 30/360
+
|- class="odd"
 +
| class="sshl_f" | 8%
 +
| class="sshl_f" |
  
−
1                     Actual/actual
+
|- class="even"
 +
| class="sshl_f" | 100
 +
| class="sshl_f" |
  
−
2                     Actual/360
+
|- class="odd"
 +
| class="sshl_f" | 2
 +
| class="sshl_f" |
  
−
3                     Actual/365
+
|- class="even"
−
 
+
| class="sshl_f" | 0
−
4                     European 30/360
+
| class="sshl_f" |
−
 
+
|}
−
</div>
 
−
----
 
−
<div id="1SpaceContent" class="zcontent" align="left">
 
−
 
 
−
It returns the price per $100 face value that pays periodic interest.
 
−
 
 
−
'''Formula''':-
 
−
 
 
−
PRICE =
 
  
−
</div>
+
=PRICE(A1,A2,A3,A4,A5,A6,A7) : Calculates the Price Value with the inputs in the range A1 to A7. Returns ''81.36538958112115''.
−
----
 
−
<div id="7SpaceContent" class="zcontent" align="left">
 
  
−
If Basis &lt; 0 or Basis &gt; 4 , PRICE returns the #ERROR.
+
==Related Videos==
  
−
If Yield &lt; 0 or Rate &lt; 0, it returns the #ERROR.
+
{{#ev:youtube|tJLR3se4Pa4|280|center|PRICE}}
  
−
If SettlementDate &gt;= MaturityDate, PRICE returns #ERRROR.
+
== See Also ==
  
−
If Frequency other than 1 , 2 or 4 returns the #ERROR.
+
*[[Manuals/calci/PRICEDISC | PRICEDISC]]
 +
*[[Manuals/calci/PRICEMAT | PRICEMAT]]
  
−
If Redemption &lt;= 0, PRICE returns the #ERROR.
+
== References ==
  
−
</div>
+
*[http://en.wikipedia.org/wiki/Bond_valuation Bond Valuation]
−
----
+
*[http://en.wikipedia.org/wiki/Day_count_convention Basis Convention]
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
  
−
PRICE
 
  
−
</div></div>
 
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left">
 
  
−
Lets see an example in (Column2, Row2)
+
*[[Z_API_Functions | List of Main Z Functions]]
−
 
 
−
<nowiki>=PRICE(R1C1, R2C1, R3C1, R4C1, R5C1,R6C1,R1C2)</nowiki>
 
−
 
 
−
PRICE returns 97.852841.
 
−
 
 
−
Consider another example
 
−
 
 
−
<nowiki>=PRICE(date(1/1/2007),date(2/2/2008), "5%", "4%",0,3)</nowiki>
 
−
 
 
−
It returns #ERROR(frequency =0).
 
−
 
 
−
</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=" " |
 
−
| Column1
 
−
| class="sshl_f" | Column2
 
−
| Column3
 
−
| Column4
 
−
|- class="odd"
 
−
| class=" " | Row1
 
−
| class=" " | 2/2/2008
 
−
| class="sshl_f " | 1
 
−
|
 
−
|
 
−
|- class="even"
 
−
| class="  " | Row2
 
−
| class=" " | 11/2/2008
 
−
| class="sshl_f" | 97.852841
 
−
|
 
−
|
 
−
|- class="odd"
 
−
| Row3
 
−
| class=" " | 5%
 
−
| 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="even"
 
−
| Row4
 
−
| class=" " | 8%
 
−
|
 
−
|
 
−
|
 
−
|- class="odd"
 
−
| class=" " | Row5
 
−
| class=" " | 100
 
−
|
 
−
|
 
−
|
 
−
|- class="even"
 
−
| Row6
 
−
| class=" " | 2
 
−
|
 
−
|
 
−
|
 
−
|}
 
  
−
<div align="left">[[Image:calci1.gif]]</div></div>
+
*[[ Z3 |  Z3 home ]]
−
----
 

Latest revision as of 17:29, 22 August 2018

PRICE (Settlement,MaturityDate,Rate,Yield,RedemptionValue,Frequency,Basis)


  • where is security's settlement date (a date when coupon or a bond is purchased),
  • is security's maturity date (a date when coupon or a bond expires),
  • is security's annual coupon rate,
  • is security's annual yield,
  • is security's redemption value per $100 face value,
  • is the number of coupon payments per year, and
  • is the type of day count basis to use.
    • PRICE(), returns the price per $100 face value of a security that pays periodic interest.

Description

PRICE(Settlement,MaturityDate,Rate,Yield,RedemptionValue,Frequency,Basis)

  • The function returns the price per $100 face value of security that pays periodic interest.
  • 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, else Calci displays #N/A error message.
  • value must be greater than zero, else Calci displays #N/A error message.
  • The values for should be 1,2 or 4.

For Annual payment, = 1,

For Semi-annual payment, = 2,

For Quarterly payment, = 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/365
  • If value is other than 0 to 4, Calci displays #N/A error message.

Examples

PRICE (Settlement,MaturityDate,Rate,Yield,RedemptionValue,Frequency,Basis) function with inputs in order is calculated as follows:

2/2/2008
11/2/2016
5%
8%
100
2
0 
=PRICE(A1,A2,A3,A4,A5,A6,A7) : Calculates the Price Value with the inputs in the range A1 to A7. Returns 81.36538958112115.

Related Videos

PRICE

See Also

References


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