Access Keys:

# What Xess functions are available?

A list of functions is available in Xess and information on how to use them are available by clicking on "Help" within Xess. A list is also included here.

@@(R)           Reference to the cell or range R

@ABS(X)         Returns the absolute value of X
X - a numeric value

@ACCRINT(I,Ft,S,R,P,F[,B])
Returns the accrued interest for a security that pays
periodic interest.  Accrued interest is the amount the
buyer must compensate the seller for the portion of
the next coupon interest payment the seller has earned
but will not receive from the issuer.
I - issue date
Ft - first coupon date of the security.
S - settlement date
R - annual coupon rate
P - par value of the security
F - number of coupon payments per year
B - day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365
Example:
A security has the following terms: 4/1/93 settlement date,
6/1/93 maturity date, 10% semiannual coupon, \$1000 par
value, 30/360 bais
@accrint(34060,34121,10%,1000,2,0) = 33.333

@ACCRINTM(I,S,R,P,[B])
Returns the accrued interest for a security that pays
interest at maturity
I - the issue date
S - the settlement date
R - the annual coupon rate
P - the par value of the security
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@ACOS(X)        Returns the arc cosine of X
X - a numeric value between -1 and 1

@ACOSH(X)       Returns the hyperbolic arc cosine of X
X - a numeric value greater than or equal to 1

@ALARM(X,S)     If X evaluates non-zero, then the string S is evaluated
as an expression, and the terminal beeps. The return value
is set to the result of evaluating the S expression. If
X evaluates to zero, S is not evaluated and the return
e.g. @ALARM(pressure > 500, "@REMOTE_FN(Pressure)")

@AND(...)       Returns 1 if all arguments are 1; 0 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argument list - any combination of numbers, cells or ranges

@ANNOTATE(S,X,Y[,H[,V]])
Generates a string suitable for use as an Xess graph
annotation, in which case it will cause the string S
to be displayed at position (X,Y) within the graph.  The
optional parameters H and V specify how the string S
will be justified with respect to the point (X,Y).
H and V represnet horizontal and vertical justication values.
(H,V)  ->   X,Y Placement
(0,0)       bottom left corner of S (the default).
(0,1)       top left corner of S.
(1,0)       bottom right corner of S.
(1,1)       top right corner of S.

S - a string value
X,Y - graph coordinates
H,V - optional text justification parameters
Example:
@ANNOTATE("Average",5/2,12,1,0.5) = "(2.5,12,1,0.5)Average"

@ASIN(X)        Returns the arc sine of X
X - a numeric value between -1 and 1

@ASINH(X)       Returns the hyperbolic arc sine of X
X - a numeric value

@ATAN(X)        Returns the 2-quadrant arc tangent of X
X - a numeric value

@ATANH(X)       Returns the hyperbolic arc tangent of X
X - a numeric value greater than -1 and less
than 1.

@ATAN2(X,Y)     Returns the 4-quadrant arc tangent of Y/X
X, Y - numeric values, one of them must be non-zero

@AVG(...)       Returns the average (mean) of its arguments
argumentlist - any combination of numbers, cells or ranges

@CAVG(...,C)    Returns the conditional average (mean) of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CCOUNT(...,C)  Returns a conditional count of its non-blank arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CEIL(X)        Returns the smallest integer greater than or equal to X
X - a numeric value

@CELLREF(C,R)   Returns the value contained in cell C,R.
C - Column, an integer value
R - Row, an integer value

@CHAR(N)        Returns the character represented by N in the ASCII
character set.
N - an integer value between 1 and 255.

@CHOOSE(N,...)  Returns the value at the Nth place in the argument
list.
N - an integer.
@CHOOSE(2,"mary","sue","beth") ==> "sue"

@CLEAN(S)       Returns a "printable" representation of string S by
stripping out unprintable characters.

@CMAX(...,C)    Returns the conditional maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CMIN(...,C)    Returns the conditional minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CODE(S)        Returns the ASCII code for the first character in string S.
S - a string value

@COL(C)         Returns the column associated with cell C, or the
left-most column if C is a range.  Returns the column
of the current cell if C is omitted.

@COLS(R)        Returns the number of columns in range R.

@CORR(R1,R2)    Returns Pearson's product-moment correlation coefficient for
the paired data in ranges R1 and R2
R1, R2 must have the same dimensions

@COS(X)         Returns the cosine of X
X - a numeric value

@COSH(X)        Returns the hyperbolic cosine of X
X - a numeric value

@COUNT(...)     A count of its non-blank arguments
argumentlist - any combination of numbers, cells or ranges

@CCOUNT(...,C)  Returns a conditional count of its non-blank arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@COUPDAYBS(S,M,F,[B])
Returns the number of days between the beginning of the
coupon period to the settlement date.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@COUPDAYS(S,M,F,[B])
Returns the number of days in the coupon period that
the settlement date is in.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0   30/360
1   actual/actual
2   actual/360
3   actual/365

@COUPDAYSNC(S,M,F,[B])
Returns the number of days between the settlement date
and the next coupon date.
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@COUPNCD(S,M,F,[B])
Returns the next coupon date after the settlement date.
S - the settlement date.
M - the maturity date
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@COUPNUM(S,M,F,[B])
Returns the number of coupon payments between the
settlement date and munurity date.
S - settlement date
M - maturity date
F - the number of coupon payments per year
B - day count basis
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@COUPPCD(S,M,F,[B])
Returns the previous (most recent) coupon date before
S - the settlement date
M - the maturity date
F - the number of coupon payments per year
B - day count basis
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@CSTD(...,C)    Returns the conditional standard deviation (N weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CSTDS(...,C)   Returns the conditional sample deviation (N-1 weighting)
of its arguments.
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CSUM(...,C)    Returns the conditional sum of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CTERM(R,FV,PV) Returns the number of compounding periods for an investment
R - Rate of return ( e.g. .07 for 7 %)
FV - future value of the investment
PV - present value of the investment

@CUMIPMT(R,N,P,S,E,T)
Returns the cumulative interest paid between S
(start) and E (end) on a loan.
R - Rate, a number
N - Number of payments, an integer > 0
P - Present Value
S - Start of loan
E - End of loan
T - Type is the timing of the loan: 0 means payment
at the end of each period, and 1 means payment at
the beginning of each period.

@CUMPRINC(R,N,P,S,E,T)
Returns the cumulative principle paid between S
(start) and E (end) on a loan.
R - Rate, a number
N - Number of payments, an integer > 0
P - Present Value
S - Start of loan
E - End of loan
T - Type is the timing of the loan: 0 means payment
at the end of each period, and 1 means payment at
the beginning of each period.

@CVAR(...,C)    Returns the conditional population variance (N weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@CVARS(...,C)   Returns the conditional sample variance (N-1 weighting)
of its arguments
argumentlist - any combination of numbers, cells, or ranges
C - a constraint expression

@DATE(Y,M,D)    returns the date value for year Y, month M, and date D.
Y - year
M - month
D - day

@DATEVALUE(S)   Returns the corresponding date value for a given string S.
S - string value of a date.
Examples:
@DATEVALUE("September-91")
@DATEVALUE("3/24/54")

@DAY(DT)        Returns the day number in the date/time value DT.
DT - a date/time value

@DAYS360(S,E)   Returns the number of days between two dates, based
on a 30-month/360-day year.
S,E - date/time values
@DAYS360(@DATEVALUE("16-NOV-93"),@DATEVALUE("17-NOV-93"))

@DB(C,S,L,N,M)  Returns the fixed-declining real depreciation of an
asset for a specified period.
C - initial Cost of asset
S - Salvage value of asset
L - Number of periods over which the asset is
depreciating (Life)
N - Number of periods over which to calculate the
depreciation.
M - (optional) Number of Months in the first year,
which is assumed to be 12 if omitted.

@DDB(C,S,L,N)   Returns the double-declining depreciation allowance
C - cost
S - salvage value
L - allowable value
N - number of depreciation periods

@DEGREES(X)     Returns 180/pi times X, converting radians to degrees.
X - a numeric value

@DET(M)         Returns the determinant of the matrix range M, which
must be a square matrix.
M - a range, which must contain an equal number of
rows and columns.

@DFT(R)         The Discrete Fourier Transform of the range R.
R must represent a real vector (either its row or column
dimension must be 1) or a complex vector (either its row
or column dimension must be 2). The result is always a
complex vector.
R - a range representing the vector to be transformed.

@DISC(S,M,P,R,B)
Returns the discount rate of a security.
S - Settlement date
M - Maturity
P - Price per \$100
R - Redemption value per \$100
B - Basis is the standard day count basis

@DOLLARDE(D,F)  Returns the amount in Dollar Decimal form of an amount
expressed in fractional form.
D - Dollars (in decimal form)
F - Fraction (denominator of fraction)

@DOLLARFR(D,F)  Returns the amount in Dollar Fractional form of an
amount expressed in decimal form.
D - Dollars (in fractional form)
F - Fraction (denominator of fraction)

@DOT(R1,R2)     Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal
number of rows and columns, or must be a dimension
1 x n (row vector) or n x 1 (column vector).

@DURATION(S,M,C,Y,F,B)
Returns the Macauley duration for an assumed par
value of \$100.
S - Settlement date, a date/time value
M - Maturity date, a date/time value
C - Annual Coupon rate
Y - Annual Yield
F - Frequency -- number of payments per year.
B - Basis is the day count basis

@EDATE(S,M)     Returns a date/time value representing the date M
months after S, if M is positive, and M months before
S, if M is negative.  The date returned will be on the
same day of the month as S.
S - Start date, a date/time value
M - Months, an integer

@EFFECT(R,P)    Returns the effective annual interest rate.
R - Nominal Rate
P - Number of compounding Periods per year

@EIGEN(M)       Generates the eigenvalues of the matrix M, which must be square
and symmetric.
M - a matrix which must be square and symmetric.

@EOMONTH(S,M)   Returns a date/time value representing the date M
months after S, if M is positive, and M months before
S, if M is negative.  The date returned will be on the
last day of the month.
S - Start date, a date/time value
M - Months, an integer

@ERF(L[,U])     Returns the error function integrated between L (lower
limit) and U (upper limit).  If U is omitted, @ERF
integrates between 0 and L.
L, R - a non-negative numeric value

@ERFC(L)        Returns the comlementary error function integrated
between L and infinity.  @ERFC(L)=1-@ERF(L).
L - a non-negative numeric value

@EXACT(S1,S2)   Returns 1 if S1 exactly matches string S2; otherwise, 0
S1, S2 - string values

@EXP(X)         Returns e raised to the X power
X - a numeric value representing exponentiation

@F(M,N,F)       Returns the integral of Snedecor's F-distribution with M and N
degrees of freedom from minus infinity to F
M,N,F - numeric values

@FACT(X)        Returns X * (X-1) * ... * 1 (X factorial, or X!)
X is a numeric value.

@FALSE          Returns the logical value 0

@FFT(R)         Generates the Discrete Fourier Transform of the range R
using a Fast Fourier Transform algorithm.  The length of
the vector must be a power of 2. @FFT will produce the
same results as @DFT, but is much faster (N*log(N)) times
as opposed to N**2 times.
R - a range representing the vector to be transformed.

@FIND(S1,S2,N)  Returns the index of the first occurrence of S1 in S2
S1, S2 - String values
N - a numeric value

@FILEEXISTS(S)  Returns 1 if file S can be opened to read; otherwise 0
S - a string value

@FIND(I,T,N)    Returns the index of the first occurrence of string
I in string T, starting at search position N in
string T.  Note that the index of a string starts
with 0.
@FIND("bc","abcdefg",0)=1

@FLOOR(X)       Returns the largest integer less than or equal to X
X - a numeric value

@FORECAST(X,R1,R2)
Returns a predicted Y value for X when R1 and R2 are
known x values and y values respectively

@FORMAT(F,N,X)  Returns the string formed by formatting the value X using
the Xess format code F and precision N
F - Xess format code
N - the precision level, from 0 to 15
X - a numeric value

@FRAC(X)        Returns the fractional portion of X
X - a numeric value

@FREQUENCY(R, B)
Returns a frequency distribution for a set of values R
with a set of intervals B.
R - a range of values on which frequencies will be counted
B - a range of intervals used to group values in R
@FREQUENCY(A1..A8, B1..B2)={3,2,2}
where A1..A8 = {70,79,80,61,83,93,88,97} and
B1..B2 ={80,90}

@FTEST(R1,R2)   Returns the significance level (alpha) of the two-sided
F-test on the variances of the data specified by
ranges R1 and R2

@FV(P,R,N)      Returns the future value of an annuity
P - periodic payment
R - interest rate
N - number of periods

@FVSCHEDULE(P,R)
Returns the future value of an initial principle
after compounding by a series of interest rates.
P - Principal, or Present value
R - An array of interest rates by which to compound

@GAMMA(X)       Returns the value of the gamma function evaluated at X
X - a numeric value which must be non-negative

@GMEAN(...)     Returns the geometric mean of its arguments
argumentlist -- any combination of numbers, cells,
or ranges.

@GRAND          Returns a 12th-degree binomial approximation to a Gaussian
random number with zero mean and unit variance

@HEXTONUM(S)    Returns the numeric value for the hexadecimal
interpretation of S.
S - a string value

@HLOOKUP(X,R,N) Searches the first row (known as the index row)
in range R for the numeric or string value which "matches"
X, and returs the value N rows below in the column
where the match is found.  The index row is searched from
left to right.  If X is a string value, an exact match must
be found or an error is returned.  If X is a numeric value,
the matching column is determined by the following rules:
1) Strings and blank cells in the index row are ignored.
2) If the first value in the index row is greater than X,
an error is returned.
3) Searching stops when a numeric value which is greater than
or equal to X is found in the index row.  If the value
found is greater than X, the preceding column is the
matching column.
4) If there are no numeric values in the index row which are
greater than or equal to X, the last column in R is
considered to be the matching column.

@HMEAN(...)     Returns the harmonic mean of its arguments
argumentlist - any combination of numbers, cells, or ranges.

@HOUR(DT)       Returns the hour value (0-23) of date/time value DT.
DT - a date/time value

@IF(X,T,F)      Returns the value of T if X evaluates to non-zero,
or F if X evaluates to zero
X - numeric value
T,F - numeric or string values, cell or range references

@INIT(X1,X2)    Returns its first argument on the first recalculation pass
and its second argument on all subsequent recalculation
passes when Xess is performing iterative calculations.
X1 - initial value
X2 - iterative or subsequent value

@INT(X)         Returns the integer portion of X
X - a numeric value

@INTERP2D(R1,R2,N)
Returns interporlation value for a 2 dimensional
vector.
R1 - a range reference points to  x values of the vector;
R2 - a range reference points to  y values of the
vector.  XR and YR have to be same size.
N  - a numerical value or range for which the
interporlation value will be based on.

@INTERP3D(R,X,Y)
Returns interporlation value for a 3 dimensional
vector.  X and Y ranges must be the same size.
R - a range reference points to x, y and z values of
the vector;
X, Y  - numerical values or ranges for which the
interporlation value will be based on.

@INTRATE(S,M,I,R,B)
Returns the interest rate of a fully invested security
S - Settlement date
M - Maturity
I - Investment amount
R - Redemption amount
B - Basis

@INVDFT(R)      Generates the inverse of the Discrete Fourier Transform
of the range R. R must represent a real vector (either
its row or column dimension must be 1) or a complex
vector (either its row or column dimension must be 2).
The result is always a complex vector.
R - a range representing the vector to be inverse transformed.

@INVERT(M)      Generates the inverse of matrix M, which must be square.
M - a square matrix.

@INVFFT(R)      Generates the inverse of the Discrete Fourier
Transform of the range R using a Fast Fourier
Transform algorithm. The length of the vector
must be a power of 2.  @INVFFT will produce the
same results as @INVDFT, but is much faster
(N*log(N)) time as opposed to N**2 time.
R - a range representing the vector to be inverse
transformed.

@IPMT(R,P,NP,PV,FV[,T])
Returns the interest payment for a specific period for
an investment based on periodic, constant payments and
a constant interest rate.
R - interest rate per period
P - the period for which the interest will be calculated
NP - the total number of payment periods.  Make sure
the units used be R and NP are consistent.  For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - present value of the investment.
FV - future value or a cash balance you would like to
attain at the end of the last period.
T - timing of the payment
0   payment is made at the end of the period.
1   payment is made at the beginning of the period.

@IRR(G,F)       Returns the internal rate of return on an investment
Related functions: @MIRR and @XIRR
G - a numeric value (an initial "guess" G)
F - cash flow, represented by a range. It must contain
at least two cash flow values

@ISERROR(X)     Returns 1 if X "contains" an error, otherwise 0.
X is an expression, which may include references to
other cells.  X "contains" an error if the expression
causes an error, or if it refers to any cells which
have errors.

@ISNUMBER(X)    Returns 1 if X is a numeric value; otherwise 0
X - a numeric or a string value

@ISSTRING(X)    Returns 1 if X is a string value; otherwise 0
X - a numeric or a string value

@LARGE(R,N)     Returns the Nth largest datum in range R.
R - Range of data
N - Integer specifying which datum to choose.
@LARGE(A1..A10,3) = 7
where A1..A10={38,3,9,45,7,90,17,2,75,12}

@LEFT(S,N)      Returns the string composed of the leftmost N characters of S
If N is greater than or equal to the length of S, then the
entire string S is returned
S - a string value
N - a numeric value

@LENGTH(S)      Returns the number of characters in S
S - a string value

@LINCOEF(X,Y)   Generates the straight line least squares fit.
X - a range representing a column vector of independent
variable values
Y - a range representing a column vector of dependent variable
variable values

@LINFIT(X,Y)    Returns the straight line least squares fit.
X - a column vector of values for the independent variable.
Y - a column vector of values for the dependent variable.
This function is equivalent to @POLYFIT(X,Y,1).

@LLS(X,Y)       (for all version prior to Xess 3.1.2b)
The linear least squares solution X to the overdetermined
system of equations AX=Y.
X - a range representing the coefficient matrix which
must intercept the origin (rightmost column of X must
be all 1's)
Y - a column vector of values for the dependent variable

@LLS(X,Y,flag)  (Xess 3.1.2b and later)
The linear least squares solution X to the overdetermined
system of equations AX=Y.
X - a range representing the coefficient matrix
Y - a column vector of values for the dependent variable
flag =   1   (X data intercepts origins)
0   (X data does not intercept the origin)

@LN(X)          Returns the log base e of X
X - a numeric value greater than 0

@LNGAMMA(X)     Returns the log base e of the gamma function evaluated at X
X - a numeric value greater than 0

@LOG(X)         Returns the log of X
X - a numeric value greater than 0

@LOG10(X)       Returns the log base 10 of X
X - a numeric value greater than 0

@LOG2(X)        Returns the log base 2 of X
X - a numeric value greater than 0

@LOWER(S)       The string S is converted to lower case
S - a string value

@MATCH(V,R[,T]) Returns the relative position in range R of
value V in a specific way.
V - the value to be matched.  V can be a string,  a
numeric value.  If V is a string, T must be 0.  MATCH
allows regular expression type "wildcard".
T - match type
T > 0 means find largest value in R <= V
T = 0 or omitted means find first value in R = V
T < 0 means find smallest value in R >= V

@MAX(...)       Returns the maximum of its arguments
argumentlist - any combination of numbers, cells, or ranges.

@MDURATION(S,M,R,Y,F[,B])
Returns the modified Macauley duration of a security
assuming \$100 face value.
S - the settlement date
M - the maturity date
R - the security's annual coupon rate
Y - the security's annual yield
F - the number of coupon payments per year
B - day count basis
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@MEDIAN(...)    Returns the median (middle value) of the range R1.

@MESSAGE(X,S)   If X evaluates non-zero, the string S is displayed on
the status line and the terminal beeps. The return
value of the function is set to the value of X.
e.g. @MESSAGE(@HOUR(@NOW) > 17, "Time to go home!")
Tells you that it is time to go home after 5:00 PM.

@MID(S,N1,N2)   Returns the string of length N2 that starts at position N1 in S.
S - a string value

@MIN(...)       Returns the minimum of its arguments
argumentlist - any combination of numbers, cells, or ranges.

@MINUTE(DT)     Returns the minute value (0-59) of date/time value DT.
DT - a date/time value

@MIRR(V,F,R)    Returns the Modified Internal Return Rate for a range
of periodic cash flows.
V - A range of values representing periodic income
(positive values) and payments (negative values)
F - Finance rate, the interest paid on the payments
R - Reinvestment rate, the rate of return on the income

@MMUL(M1,M2)    Generates the product of multiplying matrix M2 by matrix M1.
M1 and M2 can be multiplied only if number of columns in
M1 is equal to the number of rows in M2.
M1, M2 - matrices.

@MOD(X,Y)       Returns the remainder of X/Y, with the same sign as X.
This is NOT the modulus, see @MODULUS below for that.
X, Y - numeric values

@MODE(...)      Returns the mode, or most frequently occurring datum,
of all the arguments.  Empty cells and cells containing
text are ignored.

@MODULUS(X,Y)   Returns the modulus of X/Y, which is always positive.
X, Y - numeric values

@MONTH(DT)      Returns the number of the month in date/time value DT.
DT - a date/time value

@MSQ(...)       Returns the mean of the squares of its arguments
argumentlist - any combination of numbers, cells, or ranges.

@N(R)           Returns the numeric value of the top left cell in range R
R - a range

@NAND(...)      0 if all arguments are 1; 1 if any arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges

@NETWORKDAYS(S,E[,H])
Returns the number of whole working days, starting
at S and going to E, excluding days in H.
S - Starting date, a date/time value
E - Ending date, a date/time value
H - A range of dates to exclude, such as holidays.

@NOMINAL(R,C)   Returns the nominal annual interest rate for an
effective interest rate and the number of compounding
periods each year.
R - Effective interest rate
C - Compounding periods per year

@NOR(...)       0 if any arguments are 1; 1 if all arguments are 0;
-1 if any arguments are neither 0 nor 1.
argumentlist - any combination of numbers, cells or ranges

NOT(X)          Returns 1 if X=0; 0 if X=1; otherwise -1
argumentlist - any combination of numbers, cells or ranges
X - a numeric value

@NOW            Returns the date/time value of the current system date
and time.

@NPV(R,CF)      Returns the present value of a series of future cash
flows at a given rate. Related functions: @MNPV, @XNPV.
R - periodic interest rate
CF- future cash flow series (must be a range)

@NUMTOHEX(X)    Returns the hexadecimal representation of the integer
portion of X, for -2147483648 <= X <= 2147483647.
X - a numeric value

@ODDFPRICE(S,M,FC,R,Y,RD,F[,B])
Returns the price per \$100 face value of a security
with an odd (shor or long) first period.
S - settlement date
M - maturity date
FC - first coupon date of the security
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
\$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@ODDFYIELD(S,M,I,FC,R,PR,RD,F[,B])
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
I - issue date
FC - first coupon date of the security
R - annual coupon rate of the security
PR - the price of the security per \$100 face value
RD - redemption value of the security at maturity per
\$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@ODDLPRICE(S,M,LC,R,Y,RD,F,[B])
Returns the price per \$100 face value of a security
with an odd (short or long) last period.
S - settlement date
M - maturity date
LC - first coupon date of the security
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
\$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@ODDLYIELD(S,M,LC,R,PR,RD,F,[B])
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
LC - Last coupon date of the security
R - annual coupon rate of the security
PR - the price of the security per \$100 face value
RD - redemption value of the security at maturity per
\$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@OR(...)        Logical OR function.  Returns 1 if any argument in
the list equals 1, 0 if not, and -1 if there is any
argument in the list not equal to 0 or 1.

@PERCENTILE(R,N)
Returns the datum from the range R which is at the Nth
percentile in R.  Only numeric data in R are considered.
The result will be interpolated if N is not a multiple
of 1/(s-1), where s is the size of R.
R - A range of data
N - A numeric value between 0 and 1, inclusive.

@PERCENTRANK(R,D[,S])
Returns the percentile rank of the datum D in range R,
to significance S.
R - Range of numeric data
D - Datum to find
S = Number of significant digits

@PERMUT(S,T)    Returns "S choose T", or the number of T objects that
can be chosen from the set S, where order is significant.
S - Number of objects to choose from
T - Number of objects to be chosen

@PI             Returns a numeric approximation of PI.

@PLS(X,Y,d)     (for all version prior to Xess 3.1.2b)
analyzes the least squares polynomial model. The output is
identical to that of @LLS, with the polynomial coefficients
listed in the order of decreasing degree.
X - a range representing a row or column vector of independent
variable values which must intercept the origin
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in range 1 - 10

@PLS(X,Y,d,[,flag])  (Xess 3.1.2b and later)
analyzes the least squares polynomial model. The output is
identical to that of @LLS, with the polynomial coefficients
listed in the order of decreasing degree.
X - a range representing a row or column vector of independent
variable values.
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in range 1 - 10
flag - omitted/1     X data intercepts the origin
0             X data does not intercept the origin

@PMT(P,R,N)     Returns the periodic payment for a loan, given the
present value P and interest rate R, and the number
of periods N.
P,R,N - Numeric values

@POLY(X,...)    Returns the value of the Nth degree polynomial
in X, where N is the number of data in the argument
list.  The argument list may contain numbers and
cell references, including ranges.  Any non-numeric
argument causes an error.
@POLY(2,3,4,5) = (3*(2**2)+4*2+5) = 25

@POLYCOEF(X,Y,d)
Returns the least squares coefficients for the polynomial fit.
X - a range representing a row or column vector of independent
variable values.
Y - a range representing a row or column vector of dependent
variable values.
d - polynomial degree in the range 1 to 10.

@POLYFIT(X,Y,d) Returns the least squares polynomial fit.
X - a column vector of values for the independent variable.
Y - a column vector of values for the dependent variable.
d - the polynomial degree in the range of 1 to 10.

@PPMT(R,P,NP,PV,FV,T)
Returns the payment on the principal for a specific
period for an investment based on periodic, constant
payments and a constant interest rate.
R - interest rate per period
P - the period for which the interest will be calculated
NP - the total number of payment periods.  Make sure
the units used be R and NP are consistent.  For
example, for a 5-year loan with 12% annual
interest, if you make payment monthly, use 12%/12
for (monthly) R and 5*12 for NP. If you mkae
annual payment on the same loan, use 12% for R
and 5 for NP.
PV - present value of the investment.
FV - future value or a cash balance you would like to
attain at the end of the last period.
T - timing of the payment
0   payment is made at the end of the period.
1   payment is made at the beginning of the period.

@PRICE(S,M,R,Y,RD,F[,B])
Returns the price per \$100 face value of a security
that pays periodic interest
S - settlement date
M - maturity date
R - annual coupon rate of the security
Y - annual yield of the security
RD - redemption value of the security at maturity per
\$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@PRICEDISC(S,M,D,RD[,B])
Returns the price per \$100 face value of a discounted
security.
S - settlement date
M - maturity date
D - discount rate of the security
RD - redemption value of the security at maturity per
\$100 face value
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@PRICEMAT(S,M,I,R,Y[,B])
Returns the price per \$100 face value of a security
that pays interest at maturity
S - settlement date
M - maturity date
I - issue date
R - annual coupon rate of the security
Y - annual yield of the security
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@PRODUCT(...)   Multiplies all the numeric elements of the argument
list together, ignoring empty and non-numeric values,
and returns the product.

@PROPER(S)      Returns a copy of string S with the first letter of
every word capitalized.

@PTTEST(R1,R2)  Returns the significance level (alpha) of the two-sided T-test
for the paired samples contained in ranges R1 and R2
R1 and R2 must have the same size.

@PV(P,R,N)      Returns the present value of an annuity given
periodic payment P, interest rate R, and N
periods.

@QUARTILE(R,Q)  Finds the quartile Q of the data in range R.
Equivalent to @PERCENTILE(R, Q/4)
R = Range of cells
Q = Quartile as follows:
0      Minimum value
1      First quartile (25th percentile)
2      Second quartile (50th percentile)
3      Third quartile (75th percentile)
4      Maximum value

@QUARTILE(A1..B4, 2) = 7.5
where A1..B4 = {1,2,4,7,8,9,10,12}

D is a numeric value.

@RAND           Returns a uniformly distributed random number on
the interval [0,1].  The number will change every

@RANGEREF(UC,UR,LC,LR)
Returns a reference to the range described by
the corner co-ordinates UC, UR, LC, LR.
UC = Upper left column of the range
UR = Upper left row of the range
LC = Lower right column of the range
LR = Lower right row of the range

@RANK(E,R[,O])  Returns the rank of a numeric argument E in the
range R.  The rank of a number is its size relative to
other values in the list, which is equivalent to it's
position in the list after the list is sorted.  RANK
gives duplicate numbers the same rank. The presence of
duplicate numbers will affect the ranks of subsequent
numbers.  For examples, if there are two rank of 3,
the next rank will be 5.
E - a numeric value whose rank you want to find
R - a reference to a range of values.  Non-numeric
values will be ignored.
O - a numeric value specifying the way the numbers to
be ranked. RANK ranks E as if R is in descending order
if O is 0 or omitted. Otherwise, it ranks E as if
R is in ascending order.
@RANK(5, C2..C6) = 2
@RANK(5, C2..C6, 1) = 3
@RANK(7, C2..C6, 1) = 5
where C2..C6 = {2,7,5,1,5}

@RATE(F,P,N)    Returns the interest rate required to go from present
value P to future value F in N compounding periods.

Returns the value at maturity of a fully invested
security. Dates must be entered as a serial date value.
S = Settlement date, date of purchase
M = Maturity date.
I = Investment amount
D = Discount rate
B = Day count basis as follows:
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@REGEX(R,S)     Returns True(1) if the strings R and S match, and
and False (0) otherwise. This function is a regular
expression pattern matcher and is similar to @EXACT(S1,S2)
function, except @REGEX allows "wildcard" comparisons.
R - A string representing a regular expression
S - A string
@REGEX("t.*e", "table") = True
@REGEX("F[0-9]", "F3") = True

@REPEAT(S,N)    Returns a string containing N copies of string S.

@REPLACE(S1,N1,N2,S2)
Returns the string formed by replacing the N2
characters starting at position N1 in string
S1 with string S2.
@REPLACE("Expense Report",8,3,"Log") = "Expense Logort"
@REPLACE("Expense Report",8,6,"Log") = "Expense Log"
@REPLACE("ABC",8,3,"XYZ") = "XYZABC"

@RIGHT(S,N)     Returns the string composed of the rightmost N
characters of S.  If N is greater than or
equal to the length of S, then the entire string
S is returned.

@RMS(...)       Returns the square root of the mean of squares of its
arguments, which may be any combination of numbers, cells,
and ranges.  Blank cells and cells containing strings
are not counted.

@ROUND(X,N)     Returns the number X rounded to the number of decimal
places specified by the integer N, for N between 15
and -15, inclusive.
@ROUND(@PI,2) = 3.14
@ROUND(1234.5678,-2) = 1200

@ROW(C)         Returns the row index of the cell referenced by C,
the top row of C if C is a range, or the current
row if C is omitted.

@ROWS(R)        Returns the number of rows in the specified range R.

@S(R)           Returns the string value of the top left cell in range R.
Returns a null string if the cell is blank or contains a
numeric value.
R - a range

@SALARM(X,S)    If X evaluates non-zero, then the string S is evaluated
as an expression. The return value is set to the result
of evaluating the S expression. If X evaluates to zero,
S is not evaluated and the return value is zero. Does
not beep like the @ALARM function above.
e.g. @SALARM(pressure > 500, "@REMOTE_FN(Pressure)")

@SECOND(T)      Returns the seconds (0 - 59) component of the
date/time value T.

@SIGMOID(X)     Returns the value of the sigmoid function 1/(1 + exp(-X))
X - a numeric value

@SIN(X)         Returns the sine of X
X - a numeric value

@SINH(X)        Returns the hyperbolic sine of X
X - a numeric value

@SLN(C,S,L)     Returns the straight-line depreciation allowance
given cost C, salvage value S, and allowable life L.

@SMALL(R,N)     Returns the Nth smallest number in range R.

@SQRT(X)        Returns the positive square root of X
X - a positive numeric value

@SSE(...)       Returns the sum squared error of its arguments. This function
equivalent to @VAR(...)/@COUNT(...)
argumentlist - any combination of numbers, cells, or ranges

@SSQ(...)       Returns the sum of squares of its arguments
argumentlist - any combination of numbers, cells, or ranges

@STD(...)       Returns the population standard deviation (N weighting) of its
arguments
argumentlist - any combination of numbers, cells, or ranges

@STDS(...)      Returns the population standard deviation (N-1 weighting) of
its arguments
argumentlist - any combination of numbers, cells, or ranges

@STRCAT(...)    Returns the concatenation of all its arguments.
argumentlist - any combination of numbers, cells, or ranges

@STRING(X,N)    Returns the string representing the numeric value of X,
to N decimal places
X - a numeric value
N - a numeric value between 0 and 15

@STRLEN(...)    Returns the total length of all strings in its arguments
argumentlist - any combination of numbers, cells, or ranges

@SUM(...)       Returns the sum of its arguments
argumentlist - any combination of numbers, cells, or ranges

@SUMPRODUCT(R1,R2)
Returns the dot product of the vectors R1 and R2
R1, R2 - each a range, which must contain an equal number of
rows and columns, or must be a dimension 1 x n (row vector) or
n x 1 (column vector).

@SYD(C,S,L,N)   Returns the "sum-of-years-digits" depreciation allowance
given cost C, salvage value S, allowable life L, and
N depreciation periods.

@T(N,T)         Returns the integral of Student's T-distribution with
N degrees of freedom from minus infinity to T.
N, T - numeric values

@TAN(X)         Returns the tangent of X
X - a numeric value

@TANH(X)        Returns the hyperbolic tangent of X
X - a numeric value

@TBILLEQ(S,M,D) Returns the bond-equivalent yield (BEY) for a Treasury Bill.
If the term is one half-year or less, BEY is
equivalent to a actural/365 simple interest rate.  If
the term of the security is more than one-half year,
BEY is equivalent to a semiannually compounded
Treasury bond yield.
S = Settlement date
M = Maturity date
D = Discount rate of Treasury Bill

@TBILLPRICE(S,M,D)
Returns the price per \$100 face value for a Tresury
bill.
S - settlement date
M - maturity date
D - discount rate of the Tresury bill

@TBILLYIELD(S,M,D)
Returns the yield on a treasury bill, given settlement
date S, maturity date M, and discount rate D.
S = Settlement date
M = Maturity date
D = Discount rate of Treasury Bill

@TERM(P,R,FV)   Returns the number of payment periods for an investment
P - amount of periodic payments
R - interest rate
FV - future value of the investment

@TIME(H,M,S)    Returns the time value represented as a fraction of a day,
starting at midnight.  H (hours) must be between 0 and 23,
M (minutes) and S (seconds) must be between 0 and 59.

@TIMEVALUE(S)   Returns the corresponding time value for a given string
value S.
Example:
@TIMEVALUE("12:55:00 AM")
@TIMEVALUE("0:55.00")

@TODAY          Returns the date value corresponding to the current
system date.

@TRANSPOSE(M)   Generates the transpose of matrix M.

@TREND(NX, KX, KY)
TREND fits a straight line to KX (known x's) and KY
(known y's) using least square method, and then
returns the y values along the line for NX (new x's)
NX - new x values for which TREND will return the y-values
KX - known x values
KY - known y values

@TRIM(S)        Returns the string formed by removing leading, trailing,
and consecutive embedded spaces from string S.

@TRUE           Returns the logical value 1 (true).

@TTEST(R,X)     Returns the significance level (alpha) of the two-sided single
population T-test for the population samples contained
in range R
R - a range
X - a numeric value

@TTEST2EV(R1,R2)
Returns the significance level (alpha) of the two-sided dual
population T-test for ranges R1 and R2, where their
variances are equal

@TTEST2UV(R1,R2)
Returns the significance level (alpha) of the two-sided single
population T-test for ranges R1 and R2, where their
variances are not equal

@UPPER(S)       Returns the string S converted to upper case.
S - a string value

@VALUE(S)       Returns the numeric value represented by the string S
or 0 if S does not represent a number.
S - a string value

@VAR(...)       Returns the population variance (N weighting) of its arguments
argumentlist - any combination of numbers, cells, or ranges

@VARS(...)      Returns the population variance (N-1 weighting) of its arguments
argumentlist - any combination of numbers, cells, or ranges

@VDB(C,S,L,S,E)
Returns the depreciation of an asset between two specific
period using the fixed-declining balance method.
C - the initial cost of the asset.
S - salvage , the value at the end of the depreciation.
L - life, the total number of periods over which the asset
is being depreciated.
S - Starting period for the calculation.
E - Ending period for the calculation.

@VECLEN(...)    Returns the square root of the sum of squares of its arguments
argumentlist - any combination of numbers, cells or ranges

@VLOOKUP(X,R,N) Searches the first column (known as the index column)
in range R for the numeric or string value which "matches"
X, and returs the value N columns to the right in the row
where the match is found.  The index column is searched from
top to bottom.  If X is a string value, an exact match must
be found or an error is returned.  If X is a numeric value,
the matching row is determined by the following rules:
1) Strings and blank cells in the index column are ignored.
2) If the first value in the index column is greater than X,
an error is returned.
3) Searching stops when a numeric value which is greater than
or equal to X is found in the index column.  If the value
found is greater than X, the preceding row is the matching
row.
4) If there are no numeric values in the index column which are
greater than or equal to X, the last row in R is considered
to be the matching row.

@VSUM(...)      Returns the "visual sum" of its arguments, using precision and
rounding of formatted cell values
argumentlist - any combination of numbers, cells, or ranges

@WEEKDAY(D)     Returns an integer representing the day of the week
on which the day D falls.  1 is Sunday, 7 is Saturday.

@WORKDAY(S,D[,H]) Returns the day that is D working days after day S,
excluding weekends and all holidays specified as dates
in range H.

@XIRR(G,V,D)    Returns the internal rate of return for a series of
cash flows with variable intervals
G - a guessed value of the result
V - a range, contains a series of cash flows
D - a range, contains the schedule of cash flows in V
V and D must be one-dimentional ranges and have the
same size.

@XNPV(R,V,D)    Returns the net present value for a series of cahs
flows with variable intervals.
R - discount rate to apply to the cash flows
V - a range, contains a series of cash flows
D - a range, contains the schedule of cash flows in V
V and D must be one-dimentional ranges and have the
same size.

@XOR(...)       Returns -1 if any arguments are UNKNOWN;
1 if the total number of arguments with the value 1 is odd;
0 if the total number of arguments with the value 1 is even.
argumentlist - any combination of numbers, cells or ranges

@XVALUE(N,C)    Returns the value of cell C in sheet N.
N - a string containing the pathname of another
C - a valid cell reference or a string containing a cell
reference; you can specify a cell reference from the other
sheet as well by using a cell name enclosed in quotes.

@XHLOOKUP(F,X,R,N)
Searches the first row (known as the index row) in range R
in file F for the numeric or string value which "matches"
X, and returs the value N rows below in the column
where the match is found.  The index row is searched from
left to right.  If X is a string value, an exact match must
be found or an error is returned.  If X is a numeric value,
the matching column is determined by the following rules:
1) Strings and blank cells in the index row are ignored.
2) If the first value in the index row is greater than X,
an error is returned.
3) Searching stops when a numeric value which is greater than
or equal to X is found in the index row.  If the value
found is greater than X, the preceding column is the
matching column.
4) If there are no numeric values in the index row which are
greater than or equal to X, the last column in R is
considered to be the matching column.

@XVLOOKUP(F,X,R,N) Searches the first column (known as the index column)
in range R in spreadsheet file F for the numeric or string
value which "matches" X, and returns the value N columns to
the right in the row where the match is found.

The index column is searched from top to bottom.  If X is
a string value, an exact match must be found or an error
is returned.  If X is a numeric value, the matching row is
determined by the following rules:
1) Strings and blank cells in the index column are ignored.
2) If the first value in the index column is greater than X,
an error is returned.
3) Searching stops when a numeric value which is greater than
or equal to X is found in the index column.  If the value
found is greater than X, the preceding row is the matching
row.
4) If there are no numeric values in the index column which are
greater than or equal to X, the last row in R is considered
to be the matching row.

@YEAR(DT)       Returns the four-digit year value of a date/time value DT.
DT - a date/time value

@YEARFRAC(S,E[,B])
Returns the year fraction representing the number of
whole days between S (start date) and E (end date)
S - a date value that represents the start date
E - a date value that represents the end date
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@YIELD(S,M,R,PR,RD,F[,B])
Returns the yield of a security that pays periodic
interest
Returns the yield per of a security with an odd (short
or long) first period.
S - settlement date
M - maturity date
R - annual coupon rate of the security
PR - the price of the security per \$100 face value
RD - redemption value of the security at maturity per
\$100 face value
F - the number of coupon payments per year
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@YIELDDISC(S,M,PR,RD[,B])
Returns the annual yield for a discounted security
S - settlement date
M - maturity date
PR - the price of the security per \$100 face value
RD - redemption value of the security at maturity per
\$100 face value
B - the day count basis to be used
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

@YIELDMAT(S,M,I,R,PR[,B])
Returns the annual yield of a security which pays interest at
maturity.  All dates must be expressed as serial date values.
S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per \$100 face value
B = the day count basis to be used:
0 or omitted        30/360
1                   actual/actual
2                   actual/360
3                   actual/365

## IS&T Contributions

#### Documentation and information provided by IS&T staff members →

February 25, 2009

### Get Help

Request help
from the Help Desk
Report a security incident
to the Security Team
Labels:
olc-xess olc-xess Delete
xess xess Delete
function function Delete