
Use this function to calculate the price per 100 monetary units invested, for a bond that pays periodic interest.
The price is calculated using the following formula for bonds that pay two or more coupons between settlement and maturity:
For a bond that pays one coupon between settlement and maturity, the price is calculated using this formula:
Where bonds have a zero rate and do not pay a coupon, use this formula:
In these formulas:
- E is the number of coupon days in the coupon period containing the settlement,
- A is the number of coupon days before settlement,
- DSC is the number of coupon days between the settlement and the next coupon,
- DSR is the number of coupon days between the settlement and the maturity,
- yld is the yield,
- N is the total number of coupon periods between settlement and maturity, and
- n is the number of years to maturity as a fraction (calculated using a basis).
Syntax
PRICE(settlement, maturity, rate, yield, redemption, frequency[, basis])
The PRICE function has the following arguments:
Argument | Data type | Description |
settlement (required) | Date | The bond settlement date — the date the bond is traded to the buyer. |
maturity (required) | Date | The bond maturity date — the date when the bond expires. |
rate (required) | Number | The bond annual coupon rate. |
yield (required) | Number | The bond annual yield. |
redemption (required) | Number | The payment received when the bond reaches maturity. |
frequency (required) | Number |
The number of coupon payments per year. Enter:
|
basis (optional) | Number |
The basis determines how many days exist in a year. A full year has:
US 30/360 is the default basis for DURATION. It can also be specified by entering 0. To use a different type of day count basis, enter:
Learn about the conventions used to calculate the day count for basis. |
Returns |
Number |
Constraints
The PRICE function has the following constraints:
- the settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399;
- the maturity date must be later than the settlement date;
- the rate must be greater than zero;
- the yield must be greater than negative one;
- the redemption must be greater than zero;
- the frequency must be either 1 (annual), 2 (semi-annual), or 4 (quarterly); and
- the basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).
Examples
The following tables show some example formulas using the PRICE function.
You can reference line items or list properties in your formula.
Formula | Description | Result |
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 1, 4) |
This is an example of a PRICE calculation that specifies a basis. It uses the European 30/360 basis, indicated by the number 4. The example has:
|
104.97 |
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 4) |
In this example, the price is calculated without specifying a basis. As a result, this defaults to US 30/360. Here:
|
105.13 |