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

Use this function to calculate the yield to maturity (YTM) of a bond.

The YTM is the overall rate of interest that, when used to discount the bond's future cashflows, produces the given price.

When interest rates rise, bond prices fall, so it is important to know the yield when calculating the price of a bond.

As yield is used in the formula for calculating price, you can determine the value of the yield using an iterative solution.

This is the formula for price:

Contains maths calculation – calculation is : Price = Σ, from t = 1 to T, of fraction start, CashFlows, subscript t, over ( 1 + Yield), end of fraction, with 2 items.

Where T is the total number of coupon periods.

Syntax

YIELD(settlement, maturity, rate, price, redemption, frequency[, basis])

The YIELD 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 date.
price (required) Number The bond price per 100 monetary units, face value.
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 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 price must be greater than zero;
  • the redemption must be greater than zero; 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).

Note that if the price and redemption parameters are very far apart, the iterative method of calculation might never converge on a result. In this case, NaN (not a number) is returned.

Examples

The following tables show some example formulas using the YIELD function.

You can reference line items or list properties in your formula.

Formula Description Result
YIELD(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 90, 100, 1, 4)

This example shows a YIELD calculation that specifies a basis.

The basis is given as 4 (European 30/360).

The example has:

  • a settlement date of 01/15/2015,
  • a maturity date of 01/15/2018,
  • a rate of 0.12 (12%),
  • a price of 90 monetary units,
  • a redemption of 100 monetary units, and
  • a frequency of 1 (annual).
 0.1648 or 16.5%
YIELD(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 90, 100, 4)

In this example the yield 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 0.12 (12%),
  • the price is 90 monetary units,
  • the redemption is 100 monetary units, and
  • the frequency is 4 (quarterly).
 0.1627 or 16.3%

Excel equivalent

Similar functions