Analysis ToolPak functions
The Spreadsheet component supports the Analysis ToolPak functions listed in the table below. Before you can use these functions you must connect the Analysis ToolPak function add-in. For more information, see Use the Analysis ToolPak function add-in. For information on the syntax and use of the functions listed below, search Help in Microsoft Excel or search Microsoft Office Online.
| Function | Description |
|---|---|
| ACCRINT | Returns the accrued interest for a security that pays periodic interest. |
| ACCRINTM | Returns the accrued interest for a security that pays interest at maturity. |
| AMORDEGRC | Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. The function is similar to AMORLINC, except that a depreciation coefficient is applied in the calculation depending on the life of the assets. |
| AMORLINC | Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the prorated depreciation is taken into account. |
| BESSELI | Returns the modified Bessel function, which is equivalent to the Bessel function evaluated for purely imaginary arguments. |
| BESSELJ | Returns the Bessel function. |
| BESSELK | Returns the modified Bessel function, which is equivalent to the Bessel functions evaluated for purely imaginary arguments. |
| BESSELY | Returns the Bessel function, which is also called the Weber function or the Neumann function. |
| BIN2DEC | Converts a binary number to decimal. |
| BIN2HEX | Converts a binary number to hexadecimal. |
| BIN2OCT | Converts a binary number to octal. |
| COMPLEX | Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj. |
| CONVERT | Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers. |
| COUPDAYBS | Returns the number of days from the beginning of the coupon period to the settlement date. |
| COUPDAYS | Returns the number of days in the coupon period that contains the settlement date. |
| COUPDAYSNC | Returns the number of days from the settlement date to the next coupon date. |
| COUPNCD | Returns a number that represents the next coupon date after the settlement date. |
| COUPNUM | Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon. |
| COUPPCD | Returns a number that represents the previous coupon date before the settlement date. |
| CUMIPMT | Returns the cumulative interest paid on a loan between start_period and end_period. |
| CUMPRINC | Returns the cumulative principal paid on a loan between start_period and end_period. |
| DEC2BIN | Converts a decimal number to binary. |
| DEC2HEX | Converts a decimal number to hexadecimal. |
| DEC2OCT | Converts a decimal number to octal. |
| DELTA | Tests whether two values are equal. |
| DISC | Returns the discount rate for a security. |
| DOLLARDE | Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number. Use DOLLARDE to convert fractional dollar numbers, such as securities prices, to decimal numbers. |
| DOLLARFR | Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction. Use DOLLARFR to convert decimal numbers to fractional dollar numbers, such as securities prices. |
| DURATION | Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price's response to changes in yield. |
| EDATE | Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. |
| EFFECT | Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. |
| EOMONTH | Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. |
| ERF | Returns the error function integrated between lower_limit and upper_limit. |
| ERFC | Returns the complementary ERF function integrated between x and infinity. |
| FACTDOUBLE | Returns the double factorial of a number. |
| FVSCHEDULE | Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate. |
| GCD | Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder. |
| GESTEP | Returns 1 if number ≥ step; returns 0 (zero) otherwise. Use this function to filter a set of values. For example, by summing several GESTEP functions you calculate the count of values that exceed a threshold. |
| HEX2BIN | Converts a hexadecimal number to binary. |
| HEX2DEC | Converts a hexadecimal number to decimal. |
| HEX2OCT | Converts a hexadecimal number to octal. |
| IMABS | Returns the absolute value (modulus) of a complex number in x + yi or x + yj text format. |
| IMAGINARY | Returns the imaginary coefficient of a complex number in x + yi or x + yj text format. |
| IMARGUMENT | Returns the argument  (theta), an angle expressed in radians.
|
| IMCONJUGATE | Returns the complex conjugate of a complex number in x + yi or x + yj text format. |
| IMCOS | Returns the cosine of a complex number in x + yi or x + yj text format. |
| IMDIV | Returns the quotient of two complex numbers in x + yi or x + yj text format. |
| IMEXP | Returns the exponential of a complex number in x + yi or x + yj text format. |
| IMLN | Returns the natural logarithm of a complex number in x + yi or x + yj text format. |
| IMLOG10 | Returns the common logarithm (base 10) of a complex number in x + yi or x + yj text format. |
| IMLOG2 | Returns the base-2 logarithm of a complex number in x + yi or x + yj text format. |
| IMPOWER | Returns a complex number in x + yi or x + yj text format raised to a power. |
| IMPRODUCT | Returns the product of 2 to 29 complex numbers in x + yi or x + yj text format. |
| IMREAL | Returns the real coefficient of a complex number in x + yi or x + yj text format. |
| IMSIN | Returns the sine of a complex number in x + yi or x + yj text format. |
| IMSQRT | Returns the square root of a complex number in x + yi or x + yj text format. |
| IMSUB | Returns the difference of two complex numbers in x + yi or x + yj text format. |
| IMSUM | Returns the sum of two or more complex numbers in x + yi or x + yj text format. |
| INTRATE | Returns the interest rate for a fully invested security. |
| ISEVEN | Returns TRUE if number is even, or FALSE if number is odd. |
| ISODD | Returns TRUE if number is odd, or FALSE if number is even. |
| LCM | Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on. Use LCM to add fractions with different denominators. |
| MDURATION | Returns the modified Macauley duration for a security with an assumed par value of $100. |
| MROUND | Returns a number rounded to the desired multiple. |
| MULTINOMIAL | Returns the ratio of the factorial of a sum of values to the product of factorials. |
| NETWORKDAYS | Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term. |
| NOMINAL | Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year. |
| OCT2BIN | Converts an octal number to binary. |
| OCT2DEC | Converts an octal number to decimal. |
| OCT2HEX | Converts an octal number to hexadecimal. |
| ODDFPRICE | Returns the price per $100 face value of a security having an odd (short or long) first period. |
| ODDFYIELD | Returns the yield of a security that has an odd (short or long) first period. |
| ODDLPRICE | Returns the price per $100 face value of a security having an odd (short or long) last coupon period. |
| ODDLYIELD | Returns the yield of a security that has an odd (short or long) last period. |
| PRICE | Returns the price per $100 face value of a security that pays periodic interest. |
| PRICEDISC | Returns the price per $100 face value of a discounted security. |
| PRICEMAT | Returns the price per $100 face value of a security that pays interest at maturity. |
| QUOTIENT | Returns the integer portion of a division. Use this function when you want to discard the remainder of a division. |
| RANDBETWEEN | Returns a random number between the numbers you specify. A new random number is returned every time the worksheet is calculated. |
| RECEIVED | Returns the amount received at maturity for a fully invested security. |
| SERIESSUM | Returns the sum of a power series. |
| SQRTPI | Returns the square root of (number * pi). |
| TBILLEQ | Returns the bond-equivalent yield for a Treasury bill. |
| TBILLPRICE | Returns the price per $100 face value for a Treasury bill. |
| TBILLYIELD | Returns the yield for a Treasury bill. |
| WEEKNUM | Returns a number that indicates where the week falls numerically within a year. |
| WORKDAY | Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed. |
| XIRR | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the IRR function. |
| XNPV | Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function. |
| YEARFRAC | Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. |
| YIELD | Returns the yield on a security that pays periodic interest. Use YIELD to calculate bond yield. |
| YIELDDISC | Returns the annual yield for a discounted security. |
| YIELDMAT | Returns the annual yield of a security that pays interest at maturity. |