Using the Yield Add-in Functions

May 15 07:31 2008 Stephen L Nelson Print This Article

Excel provides several add-in functions that let you make yield calculations forsecurities such as bonds more easily. The functions can be challenging to use, however, explains the CPA author of the MBA's Guide to Microsoft Excel.

Excel provides five functions that let you make price calculations for securities such as bonds more easily: YIELD,Guest Posting YIELDDISC, ODDFYIELD, ODDLYIELD, and ODDLYIELD. (Excel's online help file supplies the actual formulas used for many of these yield functions.)

Some Background Info on the Yield Functions

As a group, the functions use a set of standard arguments:

The settlement date specifies the date the bond is settled, or purchased. The maturity date specifies the date the bond matures, or expires. The issue date is the date on which a security is issued. You may enter the date arguments either as text strings enclosed in quotation marks (for example, “7/4/99”) or as serial date values (for example, 37000 for April 19, 2001.)

The functions for pricing odd-period securities—ODDFYIELD and ODDLYIELD—also require the date of the first regular coupon payment or the date of the last regular coupon payment in order to calculate the first or last odd period.

The rate argument is the bond’s interest rate. The yield argument is the bond’s annual yield. The redemption argument is the bond’s redemption value per each $100 of face value. The price argument shows the price of a bond expressed as a percentage of its face value.For example, a bond that cost $991.83 would be priced at 99.183.

The frequency argument gives the number of coupon payments made each year: you specify 1 to indicate an annual coupon, 2 to indicate a semiannual coupon, and 4 to indicate a quarterly coupon. Finally, the basis argument specifies the number of days in the month and year assumed for the date calculations. You specify the basis as 0 for the US (or NASD) version of 30 days in amonth and 360 days in a year; as 1 for the actual number of days in the month and year; 2 for the actual number of days in the month but 360 days in a year; 3 for the actual number of days in the month and 365 days in a year; and 4 for the European version of 30 days in a month and 360 days in a year.

NOTE: Excel uses only the integer portion of the arguments you supply to the add-in  and yield date functions.

Using the YIELD function

The YIELD function calculates the yield of a security given the settlement date, maturity date, coupon rate, price, redemption price, coupon frequency, and basis. It uses the following syntax:

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

Suppose, for example, that you want to calculate the yield on a bond that you purchased on March 4, 2000, that will mature on May 31, 2011, pays a semiannual coupon of 3.5%, is priced at 101.1425, and will be redeemed at face value, or 100. Further assume that you want to use the European, 30-days-in-a-month, 360-days-in-a-year day count basis. To make this calculation, you use the following formula:

=YIELD("3/4/2000","5/31/2011",.035*2,101.1425,100,2,4)

The function returns 0.068507, which is equivalent to 6.8507%.

Using the YIELDDISC function

The YIELDDISC function calculates the yield of a discounted security given the settlement date, maturity date, price, redemption price, and basis. It uses the following syntax:

YIELDDISC (settlement, maturity, price, redemption, basis)

Suppose, for example, that you want to calculate the yield on a discounted security that you purchased on March 4, 2000, that will mature on May 31, 2011, is discounted at 56.1762, and will be redeemed at face value, or 100. Further assume that you want to use the European, 30-days-in-a-month, 360-days-in-a-year day count basis. To make this calculation, you use the following formula:

=YIELDDISC("3/4/2000","5/31/2011",56.1762,100,4)

The function returns 0.069412, which is equivalent to 6.9412%.

Using the YIELDMAT function

The YIELDMAT function calculates the yield of a security that will pay its interest upon maturity given the settlement date, maturity date, issue date, coupon rate, price, and basis. It uses the following syntax:

YIELDMAT (settlement, maturity, issue, rate, price, basis)

Suppose, for example, that you want to calculate the yield on a security that you purchased on March 4, 2000, was first issued on March 4, 1999, that will mature on May 31, 2011, pays a coupon of 3.5% semiannually, and is priced at 95.8194. Further assume that you want to use the European, 30-days-in-a-month, 360-days-in-a-year day count basis. To make this calculation, you use the following formula:

=YIELDMAT("3/4/2000","5/31/2011","3/4/1999",.035*2,95.8194,4)

The function returns 0.071698, which is equivalent to 7.1698%.

Using the ODDFYIELD function

The ODDFYIELD function calculates the yield of a security when the first period is odd— shorter or longer than a typical coupon period—given the settlement date, maturity date, issue date, first coupon date, coupon rate, price, redemption price, coupon frequency, and basis. It uses the following syntax:

ODDFYIELD (settlement, maturity, issue, first coupon, rate, price, redemption, frequency, basis)

Suppose, for example, that you want to calculate the price on an odd-period bond that you purchased on March 4, 2000, that will mature on May 31, 2011, was originally issued on December 7, 1999, pays a semiannual coupon of 3.5% starting on November 30, 2000, and is priced at 99.183 but will be redeemed at face value. Further assume that you want to use the European, 30-days-in-a-month, 360-days-in-a-year day count basis. To make this calculation, you use the following formula:

=ODDFYIELD("3/4/2000","5/31/2011","12/7/1999","11/30/2000",.035*2,99.183,100,2,4)

The function returns 0.066599, which is equivalent to 6.6599%.

Using the ODDLYIELD function

The ODDFYIELD function calculates the yield of a security when the last period is odd— shorter or longer than a typical coupon period—given the settlement date, maturity date, issue date, last coupon date, coupon rate, price, redemption price, coupon frequency, and basis.It uses the following syntax:

ODDLYIELD (settlement, maturity, issue, last coupon, rate, price, redemption, frequency, basis)

Suppose, for example, that you want to calculate the price on an odd-period bond that you purchased on March 4, 2000, that will mature on May 31, 2011, was originally issued on December 7, 1999, pays a semiannual coupon of 3.5%, last paid a coupon on November 30, 1999, and is priced at 99.183 but will be redeemed at face value. Further assume that you want to use the European, 30-days-in-a-month, 360-days-in-a-year day count basis. To make this calculation, you use the following formula:

=ODDLYIELD("3/4/2000","5/31/2011","11/30/1999",.035*2,99.183,100,2,4)

The function returns 0.070019, which is equivalent to 7.0019%.

Source: Free Guest Posting Articles from ArticlesFactory.com

About Article Author

Stephen L Nelson
Stephen L Nelson

Seattle CPA Stephen L. Nelson is the author of two hundred how-to books about using computers, including MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also publishes the http://www.scorporationsexplained.com/, http://www.llcsexplained.com/ and http://www.fasteasyincorporationkits.com/ websites.

View More Articles