Buzz
Apart from the convenient VLOOKUP function, you can also utilize the ODDFPRICE and ODDLPRICE functions in Excel for statistical analysis, particularly in the field of stock trading. Here, Mytour will help you analyze and compare the usage of these two functions.
Comparison between ODDFPRICE and ODDLPRICE functions in Excel
1. Syntax:
- Syntax of the ODDFPRICE function:
ODDFPRICE (settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, basis)
Where:
On settlement day, securities transactions are finalized. It occurs after the issuance date when securities are sold to buyers. Mandatory parameter.
On maturity: The date when securities reach their expiration. Mandatory parameter.
Issue date: The date when securities are initially offered. Mandatory parameter.
First coupon: The date of the first interest payment for securities. Mandatory parameter.
Rate: The interest rate of the securities. Mandatory parameter.
Yield: The annual return of the securities. Mandatory parameter.
Redemption: The repayment value of securities at par. Mandatory parameter.
Frequency: The number of interest payments per year. For annual payments, frequency = 1; for semi-annual, frequency = 2; for quarterly, frequency = 4. Mandatory parameter.
Basis: Optional. The basis for counting days (if omitted, default is 0).
= 0: A month is considered to have 30 days / A year is considered to have 360 days (according to North American standards)
= 1: The actual number of days in each month / The actual number of days in each year
- Syntax of ODDLPRICE function:
ODDLPRICE (settlement, maturity, last_interest, rate, yld, redemption, frequency, basis)
Where:
+ Settlement: represents the securities settlement date. It occurs after the issuance date when securities are sold to buyers. Mandatory parameter.
+ Maturity: denotes the maturity date of the securities. It is the date when securities expire. Mandatory parameter.
+ Issue: The date of securities issuance. Mandatory parameter.
+ Last_coupon: The date of the final interest payment for securities. Mandatory parameter.
= 0 : One month consists of 30 days / One year consists of 360 days (according to North American standards)
= 1 : Actual number of days in each month / Actual number of days in each year
= 2 : Actual number of days in each month / One year consists of 360 days
= 3 : Actual number of days in each month / One year consists of 365 days
= 4 : One month consists of 30 days / One year consists of 360 days (according to European standards)
2. Functionality:
+ Function oddfprice: Returns the price per $100 face value of a security with odd first period.
+ Function oddlfprice: Returns the price per $100 face value of a security with odd last period.
3. Consider an Example:
- Calculate the value of a bond with a redemption value (based on $100 denomination) of $100 and with an odd first interest period.
Where:
+ ISSUE: issuance date is 15/10/2008.
+ SETTLEMENT: settlement date is 11/11/2008.
+ MATURITY: maturity date is 01/3/2021.
+ FIRST_COUPON: first coupon interest date is 01/3/2009.
+ RATE: annual interest rate is 7.85%.
+ YLD: annual yield is 6.25%.
+ FREQUENCY: interest is calculated semi-annually, meaning 2 times per year.
+ BASIS: day count basis is 360 days per year.
- Calculate the value of a bond with a redemption value (based on $100 denomination) of $100 and with an odd last interest period
Where:
+ SETTLEMENT: settlement date is 7/02/2008.
+ MATURITY: maturity date is 15/6/2008.
+ LAST_COUPON: last coupon interest date is 15/10/2007.
+ RATE: annual interest rate is 3.75%.
+ YLD: annual yield is 4.05%.
+ FREQUENCY: interest is calculated semi-annually.
+ BASIS: day count basis is 360 days per year, 30 days per month (North American style).
Through syntax and functionality as well as examples, you must have compared the differences between the two functions. Hopefully, you can apply this well to your calculation process. To enhance your Excel skills, you should also explore the Hlookup function, which helps find a value in the first row of a data table. Wishing you success!
