YieldF

Syntax

YieldF(Settlement, Maturity, Rate, Price, Redemption, Frequency[, Basis])

Parameters

Settlement. The security's settlement date. This date is later than the issue date (the date, when the securities were sold to the buyer).

Maturity. The security's maturity date. The maturity date is the date when the security expires.

Rate. Annual interest rate for coupons on securities. Value of this parameter must be greater or equal to zero.

Price. The security's price per $100 face value. Value of this parameter must be greater than zero.

Redemption. The security's redemption price per $100 face value. Value of this parameter must be greater than zero.

Frequency. The number of payments for coupons in a year. This parameter may take the following values:

1. Annual payments.
2. Semi-annual payments.
4. Quarterly payments.

Basis. The day calculation method used:

0. The day calculation method - American. 360 days (NSAD method). Default value.
1. Day calculation method - Actual/actual.
2. Day calculation method - Actual/360 days.
3. Day calculation method - Actual/365 days.
4. Day calculation method - European 30/360 days.

Optional parameter.

Description

It returns the yield on securities, for which interest is paid periodically. This function is used to get yield on securities.

Comments

The Settlement parameter value should not be less or equal to the Maturity parameter value.

The agreement date is the date when a coupon (for instance, a bond) was sold to a buyer. Term of payment is the coupon expiration date.

If only one or less coupon periods fit until the maturity date, the YieldF function is calculated, using the following formula:

where:

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

If more than one coupon period fits the period left till the maturity date, the YieldF function is calculated using the iteration method (within 100 iterations). The Newton method is used based on the formula for the Price function. The yield changes until the calculated price for this yield is close to the value of the price argument.

Example

Formula

Result

Description

=YieldF("01.01.2008", "01.06.2008", 0.15, 145, 150, 1, 0)

0,18

Security gain based on the following terms:

The calculation date is 01.01.2008.
The maturity date is 01.06.2008.
The annual interest rate is 0.15.
The price per $100. The face value is 145.
The repurchase value is 150.
Annual coupon payments.
The used method of day calculation - American.

=YieldF(A0, B0, 0.05, 1015.3, 1510, 1, 0)

0,65

Security gain based on the following terms:

The calculation date is specified in the A0 cell, value is 01.01.2007.
The maturity date is specified in the B0 cell, the value is 01.10.2007.
The annual interest rate is 0.05.
The price per $100. The face value is 1015.3.
The repurchase value is 1510.
Annual coupon payments.
The used method of day calculation - American.