The `PRICE` function in Excel is used to calculate the price per $100 face value of a security that pays periodic interest. It is particularly useful for bond pricing. The function requires several arguments related to the bond, including settlement and maturity dates, the bond’s coupon rate, and other financial parameters.
Here’s the syntax for the `PRICE` function:
PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])
Arguments:
- settlement: (required) The settlement date of the security. This is the date after the issue date when the security is traded to the buyer.
- maturity: (required) The maturity date of the security. This is the date when the security expires.
- rate: (required) The annual coupon rate of the security.
- yld: (required) The annual yield of the security.
- redemption: (required) The redemption value of the security per $100 face value.
- frequency: (required) The number of coupon payments per year. Possible values are:
- 1 for annual payments
- 2 for semi-annual payments
- 4 for quarterly payments
- basis: (optional) The day count basis to be used. Possible values are:
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example:
Suppose you want to calculate the price of a bond with the following details:
- Settlement Date: January 1, 2023
- Maturity Date: January 1, 2025
- Annual Coupon Rate: 5%
- Annual Yield: 4%
- Redemption Value: $100
- Coupon Payments per Year: Semi-Annual
You can use the `PRICE` function like this:
=PRICE(DATE(2023, 1, 1), DATE(2025, 1, 1), 0.05, 0.04, 100, 2)
This formula will return the price per $100 face value of the bond.
Notes:
- Dates should be entered using the `DATE` function or as results of other functions or formulas.
- Ensure that the settlement date is before the maturity date, or you will get a `#NUM!` error.
- The `basis` argument, if omitted, defaults to 0 which is the US (NASD) 30/360 basis. Make sure to choose the correct basis if it’s a significant factor in your bond pricing.
Unlock Your Potential
Excel
Access
Power BI
