The PRICE function calculates the price per 100 monetary units invested for a bond that pays periodic interest.

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

ArgumentData typeDescription
Settlement (required)DateThe bond settlement date: The date the bond is traded to the buyer.
Maturity (required)DateThe bond maturity date: The date when the bond expires.
Rate (required)NumberThe bond annual coupon date.
Yield (required)NumberThe bond annual yield.
Redemption (required)NumberThe payment received when the bond reaches maturity.
Frequency (required)Number

The number of coupon payments per year.

Enter:

  • 1 for annual
  • 2 for semi-annual
  • 4 for quarterly
BasisNumber

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US (NASD) 30/360, Actual/360, and EUR 30/360 are used
  • 365 days when basis Actual/365 is used
  • 365 or 366 days when Actual/Actual is used

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:

  • 1 for Actual/Actual
  • 2 for Actual/360
  • 3 for Actual/365
  • 4 for European 30/360

Learn about the conventions used to calculate the day count for basis.

The PRICE function returns a number.

Calculation engine functionality differences

Financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

Additional information

For bonds that pay two or more coupons between settlement and maturity, the price is calculated with this formula:

Price=[redemption(1+yldfrequency)N−1+DSCE]+[∑k=1N100∗ratefrequency(1+yldfrequency)k−1+DSCE]−100∗ratefrequency∗AEPrice = \left [ \frac {redemption} {\left ( 1+\frac{yld}{frequency} \right )^{N-1+\frac{DSC}{E}}} \right ] + \left [ \sum_{k=1}^{N} \frac {100 * \frac{rate}{frequency}} {\left ( 1+\frac{yld}{frequency} \right )^{k-1+\frac{DSC}{E}}} \right ] - 100 * \frac{rate}{frequency} * \frac{A}{E}


For a bond that pays one coupon between settlement and maturity, the price is calculated with this formula:

DSR=E–ADSR = E – A


Price=100∗ratefrequency+redemptionyldfrequency∗DSRE+1<ul><li>100∗ratefrequency∗AE</li></ul>Price= \frac {100 * \frac{rate}{frequency} + redemption} {\frac{yld}{frequency} * \frac{DSR}{E}+1} <ul> <li>100 * \frac{rate}{frequency} * \frac{A}{E}</li> </ul>


Where bonds have a zero rate and do not pay a coupon, the price is calculated with this formula:

Price=redemption(1+yld)nPrice = \frac{redemption}{(1 + yld)^{n}}


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).

Excel equivalent

PRICE

YIELD

Examples

This example shows a PRICE calculation that specifies a basis.

FormulaDescriptionResult
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 1, 4)

The example has a:

  • settlement date of 01/15/2015
  • maturity date of 01/15/2018
  • rate of 0.12 (12%)
  • a yield of 0.10 (10%)
  • redemption of 100 monetary units
  • frequency of 1 (annual)
  • basis of 4 (European 30/360)
104.97

This example shows a PRICE calculation that does not specify a basis. As a result, the basis defaults to US 30/360.

FormulaDescriptionResult
PRICE(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.10, 100, 4)

The example has a:

  • settlement date of 01/15/2015
  • maturity date of 01/15/2018
  • rate of 0.12 (12%)
  • a yield of 0.10 (10%)
  • redemption of 100 monetary units
  • frequency of 1 (annual)
105.13