1. Calculation functions
  2. All Functions
  3. Financial Functions
  4. PRICE

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:

Contains maths calculation – calculation is: Price=[,fraction start, redemption over (, 1 + fraction start, yld over frequency, end of fraction,,),superscript N β€” 1 + fraction start, DSC over E, end of fraction, end of fraction,,], +[,Ξ£, from k = 1 to N, of fraction start, 100 * fraction start, rate over frequency, end of fraction, over (, 1 + fraction start, yld over frequency, end of fraction,,), superscript k β€” 1 + fraction start, DSC over E, end of fraction, end of fraction,,], β€” 100 * fraction start, rate over frequency, end of fraction, *fraction start, A over E, end of fraction, with 15 items.

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

Contains maths calculation – calculation is : DSR=E-A, with 7 items.

Contains maths calculation – calculation is : Price= fraction start, 10 * fraction start, rate over  frequency, end of fraction, + redemption, over fraction start, yld over frequency, end of fraction, * fraction start, DSR over E, end of fraction, + 1, end of fraction, β€” 100 * fraction start, rate over frequency, end of fraction, * fraction start, A over E, end of fraction, with 13 items.

Where bonds have a zero rate and do not pay a coupon, use this formula:

Contains maths calculation – calculation is: Price= fraction start, redemption over (1 + yld), end of fraction, with 7 items.

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:

  • 1 for annual,
  • 2 for semi-annual, or
  • 4 for quarterly.
basis (optional) Number

The basis determines how many days exist in a year.

A full year has:

  • 360 days when basis US 30/360, Actual/360, and EUR 30/360 are used;
  • 365 days when basis Actual/365 is used; and
  • 365 or 366 days when Actual/Actual is used.

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:

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

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:

  • a settlement date of 01/15/2015,
  • a maturity date of 01/15/2018,
  • a rate of 12%,
  • a yield of 10%,
  • a redemption of 100 monetary units, and
  • a frequency of 1 (annual).
 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:

  • the settlement date is 01/15/2015,
  • the maturity date is 01/15/2018,
  • the rate is 12%,
  • the yield is 10%,
  • the redemption is 100 monetary units, and
  • the frequency is 4 (quarterly).
 105.13

Excel equivalent