Microsoft Office Tutorials and References
In Depth Information
The Financial button on the Formulas tab includes a group of functions designed for
specific tasks related to computing and analyzing various types of securities.
In older versions of Excel, these functions were part of the Analysis Toolpak add-in, but
Microsoft fully integrated these functions back in Excel 2007. As a result, the functions
might produce slightly different results, but Microsoft says they’re so insignificantly
different that the results are nonetheless “equally correct” (Microsoft’s words). 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” in Chapter 17, “Functions for analyzing statistics.”
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.
TABLE 16-3 Security-analysis function arguments
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.
The security’s annual coupon rate.
Number of coupon payments made per year: 1 = annual, 2 = semiannual,
4 = quarterly.
Amount of investment in the security.
Issue date of the security.
Maturity date of the security, which must be greater than the settlement
Par value (face value) of the security; $1,000 if omitted.
Price of the security.
Interest rate of the security at the issue date, which must be greater than or
equal to zero.
Value of the security at redemption.
Settlement date of the security (the day you have to pay for it), which must
be greater than the issue date.
Annual yield of the security, which must be greater than or equal to zero.