Microsoft's *Excel* spreadsheet program provides an alternative environment for
many of the computations required for Macro-Investment Analysis. Its ubiquity and ease of
use are among its more attractive features. However, spreadsheets are notoriously
dangerous, since the underlying logic of a set of calculations is usually contained in
formulas scattered around a sheet (or sheets). Worse yet, the formulas are usually hidden
from sight, behind the numbers representing the results of their calculations. These
disadvantages loom especially large when an environment is to be chosen primarily as a
means of communication. For our purposes, languages such as MATLAB are superior to a
spreadsheet environment -- Excel or any other.

The situation is not, however, as bleak as it once was. Since the introduction of
version 5.0, Excel has included a full programming language that allows for structured,
documented, and readable sets of commands. Formally, it is a version of Microsoft's *Visual
Basic for Applications*, but we will use the simpler form: *Visual Basic* or to
be even more succinct: *VB*.

In Excel, VB procedures are called *Macros* , but this is far too humble a term
for perfectly respectable programs and we will resist its use except when absolutely
necessary.

Will will not cover Visual Basic, since it is a complex programming language that requires an extensive treatise. Suffice it to say that it provides an alternative to MATLAB and other languages for preparing investment application programs.

Here we concentrate on a a discussion of matrix operations in the standard Excel spreadsheet environment. The treatment will be cursory, at best since Excel is far too complex to cover in any detail in this exposition. Our goal is only to suggest ways in which it can be used by the Analyst for matrix operations.

Many Excel formulas require the specification of one or more *ranges* of cells
as arguments. In many cases the easiest way to indicate such a range is to *select*
it using keystrokes and/or a mouse as the formula is typed. For clarity, we adopt an
alternative approach, using only *named ranges* in our formulas and statements.
Since names remain with the formulas and statements, it is easy to change the physical
range of cells to which a name applies whenever results are desired for a different range
of inputs. Perhaps more important, the use of appropriate range names can greatly improve
the readability of a set of formulas or statements.

The safest way to assign a name to a range of cells is to first *select* it,
then choose **I**nsert **N**ame **D**efine from the
menu, followed by the desired name. Be certain to avoid names that look like cell
locations or combinations of them (e.g. A22). In Excel, range names are not case
sensitive. Thus Prices, prices and PRICES are considered the same name.

To select a named range, choose **E**dit **G**o to (or the
equivalent key), followed by the range name. Alternatively, use the drop-down list of
names located just above and to the left of the spreadsheet. When a named range is
selected, the name will appear in the window for this list. (In fact, you can name ranges
by selecting them, then typing the name in this box; however, this sometimes allows
conflicts to creep in and should be avoided).

Once you have named a range, you may use it in any formula that allows for a range as an argument. As indicated earlier, we will always choose this alternative.

Unbeknownst to many users, Excel can do matrix operations very efficiently, either
directly, or through the use of *matrix functions*. Microsoft prefers to use the
term "Array" to "Matrix", so most references in their manuals and help
system can be found under the former term.

Key to understanding the use of matrix operations in Excel is the concept of the *Matrix
(Array) formula*. Such a formula uses matrix operations and returns a result that can
be a matrix, a vector, or a scalar, depending on the computations involved. Whatever the
result may be, an area on the spreadsheet of precisely the correct size must be selected
before the formula is typed in (otherwise you will either lose some of the answer or get
added and possibly confusing information).

After typing such a formula, you "enter" it with three keys pressed at once: CTRL, SHIFT and ENTER. This indicates that a matrix (array) result really is desired. It also designates the entire selected range as the desired location for the answer. To modify or delete the formula, select the entire region beforehand.

When matrix computations are performed in this way, the "result areas" will
be updated immediately whenever any of the numbers in the "input areas" change
(unless automatic recomputation has been turned off). This can be a great help when one
wishes to evaluate the effects of changes in assumptions, initial conditions, etc.. This
feature, coupled with the ability to see matrices, complete with identification of the
rows and columns (i.e. in the form that we have termed *tables*), will often make
the spreadsheet environment the preferred choice for computation, if not for
communication.

In Excel, some matrix operations are performed automatically, using standard operators (as in MATLAB). Others require the use of matrix functions. We treat each below.

Assume that Holdings_1 and Holdings_2 are two ranges of the same size (say, {20*1}) containing the holdings of mutual funds in two accounts. To create a vector with the total holdings of both accounts, select an empty {20*1} range on the sheet, type in the formula:

= Holdings_1 + Holdings_2

then press CTRL-SHIFT-ENTER. As a matter of good practice, you might wish to name the resultant range (e.g. Tot_Holdings) for future reference.

Any two matrices of the same size can be added in this manner, with the result placed in a range of the same size.

Not surprisingly, a matrix can be subtracted from one of the same size in a manner analogous to that of addition. For example to find the holdings of account 2, you could use the formula:

= Tot_Holdings - Holdings_1

To add a constant to every element of a matrix, simply include it in a formula, as in:

= Tot_Holdings + 100

You can also subtract a constant from every element or multiply or divide every element by a constant. For example:

= Prices * 1.10

To multiply two matrices, use the MMULT function. Thus, if *prices* and *holdings*
are compatible for multiplication, you could compute the value of a portfolio with the
formula:

= MMULT(prices,holdings)

If a matrix is not turned in the right direction, simply use the TRANSPOSE function.
Thus if *prices* is a {20*1} vector and *holdings* is also, you could use
the formula:

= MMULT(TRANSPOSE(prices),holdings)

to produce the value of the portfolio.

As is often the case, there is another way to do the same thing in Excel. The (non-matrix) function SUMPRODUCT produces the sum of the products of the elements in two vectors of equal dimensions. Thus if prices and holdings are both {20*1}, you could compute the value of the portfolio with the formula:

= SUMPRODUCT(prices,holdings)

Note that to enter this formula, only the ENTER key need be pressed.

The provision of alternative methods for accomplishing a given type of calculation endears Excel to many users, especially those who grew up with prior versions. But it tends to frustrate those who yearn, perhaps quixotically, for a simple, yet powerful computing environment.

To produce the inverse of a matrix, use the MINVERSE function, as in:

= MINVERSE(lhs)

Of course the matrix in the named range must be square and invertable.

In Excel, as in MATLAB, you may combine matrix operations in a single formula. Remember, however, that everything must conform, that the output range should be the correct size for the final result, and that you must press CTRL-SHIFT-ENTER to enter the formula in the output range. As in more mundane formulas, it never hurts to include sufficient parentheses to remove any possible ambiguity concerning your desires.