Analyzing Securities
The Financial button on the Formulas tab offers a group of functions designed for specific tasks related to computing and analyzing various types of securities.
| Note | In previous versions of Excel, these functions were part of the Analysis Toolpak add-in, but at long last, Microsoft has fully integrated these functions into Excel 2007. As a result of this change, the functions may produce slightly different results, but Microsoft says they're so insignificantly different that the results are nonetheless "equally correct." Although its worksheet functions have been integrated into Excel, the Analysis Toolpak is still available as an add-in with sophisticated data analysis tools. For more information, see "Installing the Analysis Toolpak" on page 567. |
Many of these functions share similar arguments. We'll describe the most common ones in Table 16-3 to avoid revisiting the same information in the function discussions that follow.
| Argument | Description |
|---|---|
| Basis | Day count basis of the security. If omitted, defaults to 0, indicating U.S. (NASD) 30/360 basis. Other basis values include 1 = actual/actual, 2 = actual/360, 3 = actual/365, and 4 = European 30/360. |
| Coupon | The security's annual coupon rate. |
| Frequency | Number of coupon payments made per year: 1 = annual, 2 = semiannual, 4 = quarterly. |
| Investment | Amount of investment in the security. |
| Issue | Issue date of the security. |
| Maturity | Maturity date of the security, which must be greater than the settlement date. |
| Par | Par value (face value) of the security; $1,000 if omitted. |
| Price | Price of the security. |
| Rate | Interest rate of the security at the issue date, which must be greater than or equal to zero. |
| Redemption | Value of the security at redemption. |
| Settlement | Settlement date of the security (the day you have to pay for it), which must be greater than the issue date. |
| Yield | Annual yield of the security, which must be greater than or equal to zero. |
Open table as spreadsheet You can type dates by using any of the following: the date's serial number, the date enclosed in quotation marks, or a reference to a cell that contains a date. For example, you can type the date June 30, 2008 as the serial date value 39629, as 6/30/08, or as a reference to a cell containing this date. If the security-analysis function results in a #NUM! error value, be sure the dates are in the correct form and that they meet the criteria described in Table 16-3.
For more information about serial date values, see "Understanding How Excel Records Dates and Times" on page 521.
The DOLLARDE and DOLLARFR Functions
One of this pair of functions converts the familiar fractional pricing of securities to decimals, and the other converts decimals to fractions. The DOLLARDE function takes the arguments (fractional dollar, fraction), and the DOLLARFR function takes the arguments (decimal dollar, fraction). The fractional dollar argument is the value you want to convert expressed as an integer, followed by a decimal point and the numerator of the fraction you want to convert. The decimal dollar argument is the value you want to convert expressed as a decimal. The fraction argument is an integer indicating the denominator you want to use in the conversion. For the DOLLARFR function, fraction is the unit that the function should use when converting the decimal value, effectively rounding the decimal number to the nearest half, quarter, eighth, sixteenth, thirty-second, and so on.
For example, the formula =DOLLARDE(1.03, 32) translates as 1+3/32, which is equivalent to 1.09375. On the other hand, the formula =DOLLARFR(1.09375, 32) returns the result 1.03.
The ACCRINT and ACCRINTM Functions
The ACCRINT function returns the interest accrued by a security that pays interest on a periodic basis. This function takes the arguments (issue, first interest, settlement, rate, par, frequency, basis, calculation method), in which first interest indicates the date on which interest is first accrued and calculation method is a logical value (1 or True; 0 or False). The default value of True for calculation method returns the total accrued interest; a value of False returns the interest accrued after the first interest date. For other argument definitions, see Table 16-3. For example, suppose a U.S. Treasury bond has an issue date of March 1, 2008; a settlement date of April 1, 2008; a first interest date of September 1, 2008; a 7 percent coupon rate with semiannual frequency; a par value of $1,000; and a basis of 30/360. The accrued interest formula is =ACCRINT("3/1/08", "9/1/08", "4/1/08", 0.07, 1000, 2, 0), which returns 5.833333, indicating that $5.83 accrues from March 1, 2008, to April 1, 2008.
Similarly, the ACCRINTM function returns the interest accrued by a maturity security (a type of security with not only a rhyming name but that also pays interest at maturity). This function takes the arguments (issue, settlement, rate, par, basis). Using the preceding example with a maturity date of July 31, 2012, the accrued interest formula is =ACCRINTM("3/1/08", "7/31/12", 0.07, 1000, 0), which returns 309.1667, indicating that the $1,000 bond will pay $309.17 interest on July 31, 2012.
The INTRATE and RECEIVED Functions
The INTRATE function calculates the rate of interest, or discount rate, for a fully invested security. This function takes the arguments (settlement, maturity, investment, redemption, basis); for argument definitions, see Table 16-3. For example, suppose a bond has a settlement date of March 31, 2008, and a maturity date of September 30, 2008. A $1,000,000 investment in this bond will have a redemption value of $1,032,324, using the default 30/360 basis. The bond's discount rate formula is =INTRATE("3/31/08", "9/30/08", 1000000, 1032324, 0), which returns 0.064648, or 6.46 percent.
Similarly, the RECEIVED function calculates the amount received at maturity for a fully invested security and takes the arguments (settlement, maturity, investment, discount, basis). Using the preceding example with a 5.5 percent discount rate, the formula =RECEIVED("3/31/08", "9/30/08", 1000000, 0.055, 0) returns the mature value $1,028,277.63.
The PRICE, PRICEDISC, and PRICEMAT Functions
The PRICE function calculates the price per $100 of face value of a security that pays interest on a periodic basis. This function takes the arguments (settlement, maturity, rate, yield, redemption, frequency, basis); for argument definitions, see Table 16-3. For example, suppose a bond's settlement date is March 31, 2008; its maturity date is July 31, 2008; and the interest rate is 5.75 percent, with semiannual frequency. The security's annual yield is 6.50 percent, its redemption value is $100, and it's calculated using the standard 30/360 basis. The bond price formula is =PRICE("3/31/08", "7/31/08", 0.0575, 0.065, 100, 2, 0), which returns $99.73498.
Similarly, the PRICEDISC function returns the price per $100 of face value of a security that is discounted, instead of paying periodic interest. This function takes the arguments (settlement, maturity, discount, redemption, basis). Using the preceding example with the addition of a discount amount of 7.5 percent, the formula =PRICEDISC("3/31/08", "7/31/08", 0.075, 100, 0) returns a price of $97.50.
Finally, the PRICEMAT function returns the price per $100 of face value of a security that pays its interest at the maturity date. This function takes the arguments (settlement, maturity, issue, rate, yield, basis). Using the preceding example with a settlement date of July 31, 2008; an issue date of March 1, 2008; and the maturity date changed to July 31, 2009; the formula =PRICEMAT("7/31/08", "7/31/09", "3/31/08", 0.0575, 0.065, 0) returns $99.18.
The DISC Function
The DISC function calculates the discount rate for a security and takes the arguments (settlement, maturity, price, redemption, basis). (For argument definitions, see Table 16-3.) For example, suppose a bond has a settlement date of June 15, 2008; has a maturity date of December 31, 2008; has a price of $96.875; has a $100 redemption value; and uses the standard 30/360 basis. The bond discount rate formula =DISC("6/15/08", "12/31/08", 96.875, 100, 0) returns 0.057398, or 5.74 percent.
The YIELD, YIELDDISC, and YIELDMAT Functions
The YIELD function determines the annual yield for a security that pays interest on a periodic basis and takes the arguments (settlement, maturity, rate, price, redemption, frequency, basis); for definitions of these arguments, see Table 16-3. For example, suppose a bond has a settlement date of February 15, 2008; has a maturity date of December 1, 2008; has a coupon rate of 5.75 percent with semiannual frequency; has a price of $99.2345; has a $100 redemption value, and uses the standard 30/360 basis. The annual bond yield formula =YIELD("2/15/08", "12/1/08", 0.0575, 99.2345, 100, 2, 0) returns 0.067406, or 6.74 percent.
The YIELDDISC function, on the other hand, calculates the annual yield for a discounted security. It takes the arguments (settlement, maturity, price, redemption, basis). Using the preceding example but changing the price to $96.00, the bond yield formula =YIELDDISC("2/15/08", "12/1/08", 96, 100, 0) returns 0.052448, or 5.25 percent.
The YIELDMAT function calculates the annual yield for a security that pays its interest at maturity. This function takes the arguments (settlement, maturity, issue, rate, price, basis). Using the arguments from the YIELD example but adding an issue date of January 1, 2008, and changing the price to $99.2345, the yield-at-maturity formula =YIELDMAT("2/15/08", "12/1/08", "1/1/08", 0.0575, 99.2345, 0) returns 0.067178, or 6.72 percent.
The TBILLEQ, TBILLPRICE, and TBILLYIELD Functions
The TBILLEQ function calculates the bond-equivalent yield for a U.S. Treasury bill. It takes the arguments (settlement, maturity, discount). (For argument definitions, see Table 16-3.) For example, suppose a U.S. Treasury bill has a settlement date of February 1, 2008; a maturity date of July 1, 2008; and a discount rate of 8.65 percent. The formula for calculating the bond yield that is equivalent to the yield of a U.S. Treasury bill is =TBILLEQ("2/1/08", "7/1/08", 0.0865), which returns 0.091, or 9.1 percent.
You use the TBILLPRICE function to calculate the price per $100 of face value for a U.S. Treasury bill. This function takes the arguments (settlement, maturity, discount). Using the preceding example, the formula to calculate the price per $100 of face value, =TBILLPRICE("2/1/08", "7/1/08", 0.0865), returns 96.3718, or $96.37.
Finally, the TBILLYIELD function calculates a U.S. Treasury bill's yield. It takes the arguments (settlement, maturity, price). Using the preceding example with its result, a price of $96.37, the yield formula =TBILLYIELD("2/1/08", "7/1/08", 96.37) returns the yield 0.089803, or 9 percent.
The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD Functions
This group of functions performs calculations relating to bond coupons. For all the sample formulas in this section, we'll use as our example a bond with a settlement date of March 1, 2008, and a maturity date of December 1, 2008. Its coupons are payable semiannually, using the actual/actual basis (that is, a basis argument of 1). All these functions take the same arguments: (settlement, maturity, frequency, basis). (For definitions of these arguments, see Table 16-3.)
The COUPDAYBS function calculates the number of days from the beginning of the coupon period to the settlement date. Using our sample data, the formula =COUPDAYBS("3/1/08", "12/1/08", 2, 1) returns 91.
The COUPDAYS function calculates the number of days in the coupon period that contains the settlement date. Using our sample data, the formula =COUPDAYS("3/1/08", "12/1/08", 2, 1) returns 183.
The COUPDAYSNC function calculates the number of days from the settlement date to the next coupon date. Using our sample data, the formula =COUPDAYSNC("3/1/08", "12/1/08", 2, 1) returns 92.
The COUPNCD function calculates the next coupon date after the settlement date. Using our sample data, the formula =COUPNCD("3/1/08", "12/1/08", 2, 1) returns 39600, or June 1, 2008.
The COUPNUM function calculates the number of coupons payable between the settlement date and the maturity date and rounds the result to the nearest whole coupon. Using our sample data, the formula =COUPNUM("3/1/08", "12/1/08", 2, 1) returns 2.
The COUPPCD function calculates the coupon date before the settlement date. Using our sample data, the formula =COUPPCD("3/1/08", "12/1/08", 2, 1) returns 39417, or December 1, 2007.
The DURATION and MDURATION Functions
The DURATION function calculates the annual duration for a security whose interest payments are made on a periodic basis. Duration is the weighted average of the present value of the bond's cash flow and measures how a bond's price responds to changes in the yield. This function takes the arguments (settlement, maturity, coupon, yield, frequency, basis). (For argument definitions, see Table 16-3.)
For example, suppose a bond has a settlement date of January 1, 2008; has a maturity date of December 31, 2013; has a semiannual coupon rate of 8.5 percent; has a yield of 9.5 percent; and uses the default 30/360 basis. The resulting formula, =DURATION("1/1/08", "12/31/13", 0.085, 0.095, 2, 0), returns a duration of 4.78708.
The MDURATION function calculates the annual modified duration for a security with interest payments made on a periodic basis, adjusted for market yield per number of coupon payments per year. This function takes the arguments (settlement, maturity, coupon, yield, frequency, basis). Using the values from the DURATION formula, the modified duration formula looks like =MDURATION("1/1/08", "12/31/13", 0.085, 0.095, 2, 0) and returns a value of 4.57.
EAN: 2147483647
Pages: 260