Excel provides five functions that let you make price calculations for securities such as bonds more easily: YIELD, 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 a month 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%.
Stephen L. Nelson is the author of many bestselling financial and computer books including the MBA's Guide to Microsoft Excel from which this article is adapted. Nelson also edits the popular Forming an S Corp and the Incorporating a Business, the forming an LLC web sites.
Article Source: http://EzineArticles.com/?expert=Stephen_Nelson
0 comments:
Post a Comment