[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

YIELD

Syntax:

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

Description: Computes the yield on a security that pays periodic interest.

Mathematical Formula:

If there is one coupon period or less until redemption, YIELD is calculated as follows:

where:

A = number of days from the beginning of the coupon period to the settlement date (accrued days).
DSR = number of days from the settlement date to the redemption date.
E = number of days in the coupon period.

If there is more than one coupon period until redemption, YIELD is calculated through some number of iterations. The resolution uses the Newton method, based on the formula used for the function PRICE. The yield is changed until the estimated price given the yield is close to price.

Arguments:

Name

Type

Description

settlement

number

The security's settlement date.

maturity

number

The security's maturity date.

rate

number

The security's interest rate.

pr

number

The security's price.

redemption

number

The security's redemption value per $100 face value.

frequency

number

the number of coupon payments per year. (For annual payments, frequency is 1; for semiannual payments, frequency is 2; for quarterly payments, frequency is 4.) frequency is truncated to an integer.

basis

number

The truncated integer type of day count basis to use, as follows:

Value

Day Count Basis

0 or omitted

US (NASD) 30/360

1

Actual/actual

2

Actual/360

 

Actual/365

4

European 30/360

 

 

Time information in the date arguments is ignored.

Return Type and Value: number – The yield on a security that pays periodic interest.

However, if

settlement or maturity is out of range for the current date base value, #NUM! is returned.

settlement ≥ maturity, #NUM! is returned.

rate < 0, #NUM! is returned.

pr or redemption ≤ 0, #NUM! is returned.

frequency is any number other than 1, 2, or 4, #NUM! is returned.

basis < 0 or basis > 4, #NUM! is returned.

[Example:

YIELD(DATE(2008,2,15),DATE(2016,11,15),0.0575,95.04287,100,2,0) results in 6.5000%

end example]