by Maureen Nevin Duffy

Spreadsheet developers have resisted the urge to gum them up their spreadsheets with esoteric functions. This has inspired ever-vigilant software developers to start churning out a prolific assortment of spreadsheet add-in products.

Add-in programs can help users test their assumptions by placing the functions or formulae in spreadsheet cells. The formulae range from the commonplace all the way to the truly exotic. And since the add-in program is not an integral part of the spreadsheet program, the add-in can be changed and manipulated to produce 1 or 1,000 iterations (or recalculations) and test numerous variables. Then it is easily extracted or disabled, leaving the original data intact and ready for another go.

Getting more than a single set of results is particularly helpful in derivatives applications. where analysts spend a good deal of their time calculating the probability of a range of results or the uncertainty, sensitivity and relationships among a number of different variables.

Two products from Newfield, NY-based Palisade Corporation are particularly valuable for these problems. Before Francis Monaghan tried Palisade's @Risk and BestFit add-in products, he used to spend hours creating macros. Monaghan, a senior vice president of research for Zimmerman Investment Company, a $1.2 billion Chicago-based money management firm, would insert his custom-made macros in the spreadsheet cells, and then expose parts of his portfolios to what-ifs and tests of various relationships. "The processing time was just too much," he recalls.

Now, Monaghan uses the Monte Carlo simulation in @Risk to, for example, assess the probability of realizing a particular return over time. Let's suppose he was 95 percent confident that he won't have a 2% loss over a certain period of time. To test that theory, he would input the daily trading results generated by his global trading system, and run the Monte Carlo option in @Risk to see the probability of that assumption holding up over 60 days. Each test might consist of just one security type of the firm's equity, fixed income and currency derivative portfolios. He could also run the test with all securities combined, or run one group of iterations with just one portfolio sector and the whole portfolio in another. @Risk then collects the values of output cells from the worksheets and store them for graphic comparison.

"If it comes up with a high probability of experiencing a drawdown or period of loss, it would suggest that I may have to adjust my fixed income position," he says. Monaghan then might choose to hedge out the exposure with options or a swap.

Monaghan also uses Palisade's BestFit program, which helps users determine the best distribution to use. @Risk accommodates some 30 different distribution functions, such as TRIANG, UNIFORM or BETA. Monaghan uses the program's ability to identify patterns in flows of data to compare the behavior of different currencies. "BestFit basically fits the market return data to a variety of types of distributions, giving me the ability to visualize what markets have similarities," says Monaghan.

For example, Monaghan once suspected that some major currencies may have similar patterns with minor currencies. So he took strings of market prices on the D-Mark versus the French Franc versus the Italian Lira versus the Swiss Franc and ran them all through BestFit. Through the shape of the currency price distributions, Monaghan was able to determine that the Lira was more similar to the Swiss Franc than the D-Mark. This is important in his work, because it allows him to justify why a certain trading approach might work on those currencies.

Bond tool

Robert Abad finds @Risk useful for slightly different purposes. Abad works on quantitative analysis projects for Frank Fernandez, CEO of Global Emerging Market Advisors, L.P., a NY-based money manager. "When you can manipulate multiple variables, the model becomes more sophisticated," he says. "It allows you to make additional assumptions which take your analysis to another level than was possible by just using Excel's Add-in functions."

Abad, a former Brady pricing model builder for Merrill Lynch, says he uses Palisade's TopRank, a sensitivity analysis program, for analyzing bonds. If, for example, Abad is looking at a new bond to be issued with a 10% yield and he thinks the value is really higher, he would run the bond through TopRank at different yields to get a series of relative prices.

That, of course, is an example with only one variable. @Risk becomes useful if he wanted to know how US yields rising would affect his view on Brazilian yields - or wanted to factor in how the US rates may be affected by German or Japanese interest rates. "As the model gets more complicated," says Abad, "you need @Risk, to assess the affect of all three variables. He puts all three assumptions in and @Risk provides a best and worst case scenario. @Risk will automatically rank the correlations and do regression analyses for you," he adds.

Abad cautions that "someone who doesn't have a strong statistics background would be blown away" by such multiple machinations and may draw the wrong conclusions. "You have to understand the limits of probability and financial theories," he says.Nevertheless, @Risk would be an excellent tool in any analyst's bag of tricks."