Buzz
Apart from the convenient VLOOKUP function, you can also use the ODDFPRICE and ODDLPRICE functions in Excel for statistical analysis, which are very helpful, especially in the securities field. Below, Mytour will help you analyze and compare the usage of these two functions.
Comparison of 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:
+ Settlement: is the date of securities accounting. The securities settlement date is the day after the issuance date when securities are sold to the buyer. Mandatory parameter.
+ Maturity: is the maturity date of the securities. The maturity date is the date when the securities expire. Mandatory parameter.
+ Issue: is the date of issuance of the securities. Mandatory parameter.
+ First_coupon: is the date of the first interest payment of the securities. Mandatory parameter.
+ Rate: is the interest rate of the securities. Mandatory parameter.
+ Yld: is the annual yield of the securities. Mandatory parameter.
+ Redemption: is the redemption value of the securities at a face value of $100. Mandatory parameter.
+ Frequency: is the number of interest payment periods per year. For yearly payments, frequency = 1; for semi-annual, frequency = 2; for quarterly, frequency = 4. Mandatory parameter.
+ Basis: is optional. The type of day count basis to be used (if omitted, defaults to 0).
= 0: One month has 30 days / One year has 360 days (according to the North American standard)
= 1: Actual number of days in each month / Actual number of days in each year
- Syntax of the ODDLPRICE function:
ODDLPRICE (settlement, maturity, last_interest, rate, yld, redemption, frequency, basis)
Where:
+ Settlement: represents the securities settlement date. It is the day following the issuance date when the securities are sold to the buyer. This parameter is mandatory.
+ Maturity: denotes the maturity date of the securities. It is the date when the securities expire. This parameter is mandatory.
+ Issue: The issuance date of the securities. This parameter is mandatory.
+ Last_coupon: The date of the last interest payment for the securities. This parameter is mandatory.
= 0: One month has 30 days / One year has 360 days (according to North American standard)
= 1: Actual days in each month / Actual days in each year
= 2: Actual days in each month / One year has 360 days
= 3: Actual days in each month / One year has 365 days
= 4: One month has 30 days / One year has 360 days (according to European standard)
2. Function:
+ Function oddfprice: Returns the price per $100 face value of a security with the first odd period.
+ Function oddlfprice: Returns the price per $100 face value of a security with the last odd period.
3. Example:
- Calculate the value of a bond with a redemption value (based on $100) of $100 and the first interest period is odd.
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 date is 01/3/2009.
+ RATE: annual interest rate is 7.85%.
+ YLD: annual yield is 6.25%.
+ FREQUENCY: interest is calculated semiannually, meaning 1 year equals 2 times.
+ BASIC: basis for day count is 360 days per year.
- Calculate the value of a bond with a redemption value (based on $100) of $100 and the last interest period is odd
Where:
+ SETTLEMENT: settlement date is 7/02/2008.
+ MATURITY: maturity date is 15/6/2008.
+ LAST_COUPON: last coupon date is 15/10/2007.
+ RATE: annual interest rate is 3.75%.
+ YLD: annual yield is 4.05%.
+ FREQUENCY: interest is calculated semiannually.
+ BASIC: basis for day count is 360 days per year, 30 days per month (North American convention).
Through the syntax and functions as well as the example, you probably have compared the differences between the two functions. Hope you can apply them effectively in your computational 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. Wish you success!
