You can use PRICE to calculate how much you pay against the bond's final value, and therefore the return on your investment.
Syntax
PRICE(Settlement, Maturity, Rate, Yield, Redemption, Frequency[, Basis])
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 date. |
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 | Number | The basis determines how many days exist in a year. A full year has:
US 30/360 is the default basis for COUPDAYS. 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. |
The PRICE function returns a number.
Additional information
For bonds that pay two or more coupons between settlement and maturity, the price is calculated with this formula:
For a bond that pays one coupon between settlement and maturity, the price is calculated with this formula:
Where bonds have a zero rate and do not pay a coupon, the price is calculated with 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
- n is the number of years to maturity as a fraction (calculated using a basis)
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).
- 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).
Calculation engine functionality differences
Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.
Excel equivalent
Related Anaplan functions
Examples
This example shows a PRICE calculation that specifies a basis.
Formula | Description | Result |
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 1, 4) | The example has a:
| 104.97 |
This example shows a PRICE calculation that does not specify a basis. As a result, the basis defaults to US 30/360.
Formula | Description | Result |
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 4) | The example has a:
| 105.13 |