Syntax and Parameters
The syntax for the ODDLPRICE function is as follows:
ODDLPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])
The function requires the following parameters:
– `settlement`: The settlement date of the security.
– `maturity`: The maturity date of the security.
– `issue`: The issue date of the security.
– `first_coupon`: The date of the security’s first coupon payment.
– `rate`: The annual coupon rate of the security.
– `yld`: The annual yield of the security.
– `redemption`: The redemption value of the security.
– `frequency`: The number of coupon payments per year.
– `[basis]`: (Optional) The day count basis to use for the calculation.
Understanding the Parameters
Before we dive into using the ODDLPRICE function, let’s take a closer look at each parameter and its significance.
Settlement, Maturity, and Issue Dates
The `settlement`, `maturity`, and `issue` parameters represent the settlement, maturity, and issue dates of the security, respectively. These dates are used to determine the number of days between the settlement and maturity dates, which is a crucial factor in the calculation of the security’s price.
First Coupon Date
The `first_coupon` parameter represents the date of the security’s first coupon payment. This date is used to determine the number of days between the settlement date and the first coupon payment date.
Annual Coupon Rate
The `rate` parameter represents the annual coupon rate of the security. This is the rate at which the issuer of the security will pay interest to the holder of the security.
The `yld` parameter represents the annual yield of the security. This is the rate of return that an investor can expect to receive by holding the security until maturity.
The `redemption` parameter represents the redemption value of the security. This is the amount that the issuer of the security will pay to the holder of the security at maturity.
The `frequency` parameter represents the number of coupon payments per year. For example, if the security pays interest twice per year, the `frequency` would be 2.
Day Count Basis
The `[basis]` parameter is an optional parameter that represents the day count basis to use for the calculation. This parameter is not required, as Power BI will default to the 30/360 day count basis if no value is specified.
Using the ODDLPRICE Function
Now that we understand the various parameters of the ODDLPRICE function, let’s explore how to use it in practice.
To use the ODDLPRICE function, we first need to open a new Power BI report and create a new measure. In the formula bar, we can enter the following formula:
ODDLPRICE([settlement], [maturity], [issue], [first_coupon], [rate], [yld], [redemption], [frequency], [basis])
We can then replace each parameter with its corresponding value. For example:
ODDLPRICE("1/1/2021", "6/30/2030", "1/1/2021", "6/30/2021", 0.05, 0.06, 1000, 2)
This formula would calculate the price of a security that settles on January 1, 2021, matures on June 30, 2030, was issued on January 1, 2021, pays its first coupon on June 30, 2021, has an annual coupon rate of 5%, has an annual yield of 6%, has a redemption value of $1,000, and pays interest twice per year.
We can also customize the formula by specifying a day count basis. For example:
ODDLPRICE("1/1/2021", "6/30/2030", "1/1/2021", "6/30/2021", 0.05, 0.06, 1000, 2, 1)
This formula would calculate the price of the security using the actual/actual day count basis.
The ODDLPRICE function is a powerful tool for financial analysts who need to work with securities that pay periodic interest. By understanding the various parameters of the ODDLPRICE function and how to use them, analysts can easily calculate the price of a security and make informed investment decisions.
Power BI DAX Training Courses by G Com Solutions (0800 998 9248)
Power BI DAX Intensive Training Course£1,050.00 – £26,550.00
Power BI DAX Introduction£395.00 – £9,750.00
Power BI DAX Intermediate£395.00 – £9,750.00
Power BI DAX Advanced£395.00 – £9,750.00