Advanced Excel Functions and Procedures 11
In finance calculations, cash flows occurring at different time periods are converted into
future (or present) values by applying compounding (or discounting) factors. With continu-
ous compounding at rate r, the compounding factor for one year is exp(r), and the
equivalent annual interest rate r
a
, if compounding were done on an annual basis, is given
by the expression:
r
a
D expr 1
Continuous compounding and the use of the EXP function is illustrated further in
section 2.7.1 on Data Tables.
LN(x) returns the natural logarithm of value x. Note that x must be positive, otherwise
the function returns #NUM! for numeric overflow. For example:
ž LN(0.36788) returns value 1
ž LN(2.7183) returns value 1
ž LN(7.3891) returns value 2
ž LN(4) returns value #NUM!
In finance, we frequently work with (natural) log returns, applying the LN function to
transform the returns data into log returns.
SQRT(x) returns the square root of value x. Clearly, x must be positive, otherwise the
function returns #NUM! for numeric overflow.
RAND() generates a uniformly distributed random number greater than or equal to zero
and less than one. It changes each time the spreadsheet recalculates. We can use RAND()
to introduce probabilistic variability into Monte Carlo simulation of option values.
FACT(number) returns the factorial of the number, which equals 1
Ł
2
Ł
3
Ł
...
Ł
number.
For example:
ž FACT(6) returns the value 720
COMBIN(number, number
chosen) returns the number of combinations (subsets of
size ‘number
chosen’) that can be made up from a ‘number’ of items. The subsets can be
in any internal order. For example, if a share moves either ‘up’ or ‘down’ at four discrete
times, the number of sequences with three ups (and one down) is:
COMBIN4,1 D4 or equally COMBIN4,3 D4
that is the four sequences ‘up-up-up-down’, ‘up-up-down-up’, ‘up-down-up-up’ and
‘down-up-up-up’. In statistical parlance, COMBIN4, 3 is the number of combinations
of three items selected from four and is usually denoted as
4
C
3
(or in general,
n
C
r
).
Excel has functions to transpose matrices, to multiply matrices and to invert square
matrices. The relevant functions are:
ž TRANSPOSE(array) which returns the transpose of an array
ž MMULT(array1, array2) which returns the matrix product of two arrays
ž MINVERSE(array) which returns the matrix inverse of an array
These fall in the same Math category. Since some readers may need an introduction to
matrices before examining the functions, this material has been placed at the end of the
chapter (see section 2.13).
