Author: Oscar Cronquist Article last updated on December 13, 2018

The YIELD function calculates the yield for a security that pays interest. The YIELD function is designed to calculate the bond yield.

Formula in cell C6:

=YIELD(C2, C3, C4, C5, C6, C7, C8)

Excel Function Syntax

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

Arguments

settlement Required. The security's settlement date which is the date after the issue date.
maturity Required. The date when the security expires.
rate Required. The security's annual coupon rate.
pr Required. The security's price per $100 face value (par amount).
redemption Required. The security's redemption value per $100 face value.
frequency Required. The Treasury bill's price per $100 face value (par amount).
[basis] Required. The Treasury bill's price per $100 face value (par amount).
Basis Day count
0 (default) US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Comments

Keep in mind to use the DATE function if you enter dates in the function instead of using cell references.

For example,

=YIELD(DATE(2018, 9, 30), DATE(2018, 12, 31), C4, C5, C6, C7, C8)

Date arguments are truncated to integers.

The YIELD function returns:

  • #VALUE! error if settlement or maturity is not a valid data type.
  • #NUM! error if
    • pr <= 0 (zero)
    • redemption <= 0
    • frequency <> 1, 2 or 4
    • basis < 0 (zero)
    • settlement >= maturity