## Vertex42 The Excel Nexus

#### Vertex42 The Excel Nexus Summary

Rating: 4.7 stars out of 12 votes

Contents: Excel Templates
Official Website: www.vertex42.com
Price: \$30.00

Access Now

#### My Vertex42 The Excel Nexus Review

Vertex42 The Excel Nexus is a professionally made product. Professionally done by acknowledged experts in this area of expertise.

In conclusion, I would say that the learning curve for this software is quite steep and lengthy to get the full benefits from it's use. But if you are prepared to put in the hours needed to learn it's full capabilities this piece of software will give you many times that back. I can recommend this software to anyone.

## Excel Application 161

The process involves setting up the Black-Scholes formula in a cell on a spreadsheet and then letting Excel search for the value of the volatility that makes the Black-Scholes price equal to the observed market price of 2.5. Then, assuming that the Black-Scholes model is correct, we will have an estimate of the volatility. Enter a guess for the volatility, say 10 , although any value will do, in cell B2. Enter the risk-free rate of 5 in cell B3 the time to expiry of 0.5 in cell B4 the current price of 100 in cell B5 and the strike price of 105 in cell B6.

## Excel Application 171

The use of Excel spreadsheets alone is not a very efficient way of implementing a large-scale asset-liability model and is not to be recommended under current technology. However, there are some Add-In products (such as Risk from Palisade Corporation) that can be used to construct models. In addition, implementing models using Excel VBA (Visual Basic for Applications, the macro language associated with Excel) is often fast enough for many practical purposes. Nevertheless, an Excel spreadsheet can be used to generate sample projections that will enable you to get a feel for the differences between the types of models considered in this chapter. As an example, we show how to create a sample path for an inflation index (Q(t)) that has the following structure In cell D2 enter the formula NORMSINV(RAND()). This formula will generate a random normal variable in cell D2, which will change every time the spreadsheet is recalculated. This can be done manually by pressing the function key F9....

## Excel Application

Example 1 To calculate the present value of 100 paid in 10 years, when the rate of interest is 10 p.a., you could enter in any cell on the spreadsheet Example 2 To calculate the present value of an annuity of 10 per month (paid in arrear) for 10 years at a rate of interest of 12 p.a. (convertible monthly), you would enter (in any cell in the spreadsheet)

According to the European Spreadsheet Risk Interest Group (EuSpRIG) Research has repeatedly shown that an alarming proportion of corporate spreadsheet models are not tested to the extent necessary to support Directors' fiduciary, reporting and compliance obligations. Uncontrolled and untested spreadsheet models therefore pose significant business risks. These risks include lost revenue & profits, mispricing and poor decision making due to prevalent but undetected errors, fraud due to malicious tampering and difficulties in demonstrating fiduciary and regulatory compliance. These risks are ignored due to a widespread failure to inventory (keep records of), test, document, backup, archive and control the legions of spreadsheets that support critical corporate infrastructure.9 In some ways, Microsoft's Excel spreadsheet is the crack cocaine of financial markets it is cheap, easy to obtain, and creates the illusion of speed. The many financial modeling books have gotten traders and...

Consistent with research on human error rates in other cognitive tasks, laboratory studies and field examinations of real-world spreadsheets have confirmed that developers make uncorrected errors in 2-5 of all formulas Consequently, nearly all large spreadsheets are wrong and in fact have multiple errors . . . Spreadsheet error rates are very similar to those in traditional programming. However, while programmers spend 25 -40 of their development time in testing, testing among spreadsheet developers in industry is extremely rare but the only proven way to reduce errors dramatically is testing.5 For spreadsheets, there seldom are obvious oracles. Runtime errors do not happen. Rather, logic errors result in incorrect calculations. Team testers must compare results 11.2. SPREADSHEET TESTING with known calculated values. In some cases, however, there may be no known comparable calculation. In nearly all spreadsheets, calculations are extended well beyond what has been done previously with...

The first step of proper record keeping is setting up a spreadsheet that lists all your trades. Each horizontal line describes a new trade. Each vertical column contains a certain detail of each trade or rates an aspect of your performance. These columns belong in any workable trader's spreadsheet

A practical way of evaluating a portfolio without requiring special mathematical knowledge or expensive software is to use a spreadsheet program. Using a classic stock and bond mix, Table 23-2 shows the monthly returns of the S& P 500 (S& P) and the Lehman Brothers Treasury Index (LBTI) combined into a portfolio of 60 stocks and 40 bonds. Columns B and C show the monthly percentage returns of these two inputs and columns E and F have the corresponding cumulative returns. At the bottom of columns B and C are some basic calculations that describe the performance. The S& P has an annualized return of 16.41 with a monthly standard deviation of 3.32 bonds have a-7.65 return with a 2.61 standard

## Closingthe Gap Between Expectations And Reality

Just a few last words about what I have included and not included in this edition. The choices were not always easy because a lot has been written in the past 11 years. Mostly, I have expanded on the different trading systems and techniques, as you would expect. I have included many computer programs and spreadsheet code to make it easier for you to transfer these to any computer and begin your own work.

## Tools And Materials Needed For The Scientific Approach

Not only is a universe of data needed, but it is necessary to simulate one or more trading accounts to perform back-testing. Such a task requires the use of a trading simulator, a software package that allows simulated trading accounts to be created and manipulated on a computer. The C+ + Trading Simulator from Scientific Consultant Services is the one used most extensively in this book because it was designed to handle portfolio simulations and is familiar to the authors. Other programs, like Omega Research's TradeStation or System Writer Plus, also offer basic trading simulation and system testing, as well as assorted charting capabilities. To satisfy the broadest range of readership, we occasionally employ these products, and even Microsoft's Excel spreadsheet, in our analyses.

## Box 42 Calculation And Interpretation Of Correlations

For a spreadsheet, it is necessary to create a new column with the difference between each equity value and the average equity Spreadsheet solution. Assume there are 100 rows of equity values entered. Each row below is copied down from 2 through 100. A comparison of the average of the standard deviation and rate of return with the same values found using a spreadsheet portfolio, shows the real effect of diversification for three passive portfolios (see Table 4-2). Using stocks, bonds, and a simple foreign exchange basket, more risk reduction is gained when the correlation is .00 for the FX index with stocks or bonds, compared with a higher .34 correlation for stocks and Built-in Spreadsheet Functions The correlation coefficient can be found for most spreadsheet programs, under the Tools menu. The result is instantaneous. Just select TOOLS ADVANCED MATH REGRESSION in Quattro, or the equivalent in other programs. Most spreadsheets give R Squared, (r2) rather than r therefore the...

## Leith Seegers Mc Kahan Real Estate Agent

George Castleberry, Tamara Fuller, Renata Circeo, Don Beck, and Elmer Diaz also participated in those teleconferences and wowed each of us with their incredible knowledge about the finer details of market analysis, property acquisition, and property management. Not only are they all incredible investors, they are all teachers at heart. Jimmy and Linda McKissack shared their insights into how they parlayed their real estate sales business into a phenomenal investing business. Jimmy's knowledge of the Texas foreclosure process lit our way through that thicket of information. Early on, Chris Hake took it upon himself to devise a spreadsheet that laid out how many properties one would have to own and how much cash flow those properties would have to generate to net a million dollars in annual income. George Meidhof and Michael Huang guided us through the early stages of how to set up legal entities for investments and how to structure every kind of partnership. George also visited us in...

## Test for Profit Taking

A simple exponential moving average system was tested using Telerate's TeleTrac, and the results were compared and plotted using a spreadsheet. The formula for the trend was The TeleTrac code is shown in Box 5-1. This can also be done using a spreadsheet however, it is important not to reenter a trade in the same direction once a profit is taken. You can solve this problem by keeping one spreadsheet column for the underlying trend direction (e.g., +1 for long and -1 for short), and another column to show whether that position is active (+1 if holding the trade, 0 if closed out using profit-taking). It is tricky, but it can be done. Strategy testing programs, such as Omega's TradeStation or TeleTrac make it much easier.

## Most Calculatorsfor This Problem

Using the IRR function in a spreadsheet or an IRR-enabled financial calculator, we enter the individual cash flows and apply the IRR function. We illustrate how we can solve for IRR in this particular problem using a financial calculator without a dedicated IRR function. The cash flows from t 1 through t 6 can be treated as a six-year, 4 million annuity with 7 million - 4 million 3 million, entered as a future amount at t 6.

## No real estate license required

Can you look at a fax and properly enter some numbers into a simple spreadsheet And are you capable of using a calculator These skills help you determine what a commercial property is worth, what you should pay for it, and what your payday will be. If you need some pointers and guidance when it comes to numbers, a course in business math is sure to get you up to speed.

## Evaluating Commercial Real Estate

There's a myth going around town that you need to be an accountant with an Ivy League degree to evaluate and analyze office buildings, retail centers, and apartment complexes. Don't believe the hype. If you can count and do some basic math, you'll have no problem figuring out what your cash flow and return on investment are for any piece of commercial property. In fact, we guarantee that after you read this chapter and follow along with the examples, you'll be able to figure out what a commercial property is worth just like those sophisticated investor guys you see with their pocket protectors and fancy spreadsheets.

## Optimizer Tools And Information

Although sometimes appearing as built-in tools in specialized programs, genetic optimizers are more often distributed in the form of class libraries or software components, add-ons to various application packages, or stand-alone research instruments. As an example of a class library written with the component paradigm in mind, consider OptEvolve, the C+ f genetic optimizer from Scientific Consultant Services (516-696-3333) This general-purpose genetic optimizer implements several algorithms, including differential evolution, and is sold in the form of highly portable C+ + code that can be used in UNIX LINUX, DOS, and Windows environments. TS-Evolve, available from Ruggiero Associates (800-21 1-9785) gives users of TradeStation the ability to perform full-blown genetic optimizations. The Evolver, which can be purchased from Palisade Corporation (800-432-7475), is a general-purpose genetic optimizer for Microsoft's Excel spreadsheet it comes with a dynamic link library (DLL) that can...

## Currency Pair Checklist

The first section of your trading journal should consist of a spreadsheet that can be printed out and completed every day. This purpose of this checklist is to get a feel for the market and to identify trades. It should list all of the currency pairs that are offered for trading in the left column, followed by three columns for the current, high, and low prices and then a series of triggers laid out as a row on the right-hand side. Newer traders probably want to start off with following only the four major currency pairs, which are the EUR USD, USD JPY, USD CHF, and GBP USD, and then gradually add in the crosses. Although the checklist that I have created is fairly detailed, I find that it is a very useful daily exercise and should take no more than 20 minutes to complete once the appropriate indicators are saved on the charts. The purpose of this checklist is to get a clear visual of which currencies are trending and which are range trading. Comprehending the big picture is the first...

## Importing Data into Excel

A common practice is to calculate interest charges and or return based on the average of beginning and ending balance of debt (investment). This might have been appropriate when we did not have the calculating resources of today. With spreadsheets, we can use up to 256 columns and 65,536 lines. If we use a column for a month, we could cover more than 21 years of analysis. If that is not good enough, we could work with quarters or bimesters (periods of 2 months)9 and cover more than 60 years or 42 years, respectively. In other words, using average of debt to calculate interest is not necessary because we could construct the model for a convenient and practical length of time month, quarters, semesters, and so on. When using average debt, we need to activate the iteration ability of a spreadsheet. Otherwise circularity will appear. There are some instances in corporate finance where circularity is unavoidable (e.g., estimation of weighted average cost of capital, WACC). However, in...

## Popular Methods For Calculating Seasonality

Finding the seasonal pattern does not need to be complicated however, some basic rules must be followed to get sound results. For most analysts, it is easiest to begin with a spreadsheet, where the months are recorded in each column and the rows represent years (see Table 7-1). The average monthly price, placed in each cell, can give a good indication of seasonal patterns by simply averaging each column and plotting the results as shown in the first of four summary lines at the bottom. The major criticism of this technique is that it ignores the changing price levels over time. For example, a 25-year study of soybeans will use prices that vary from 6 to 15 per bushel price changes at the 15 level could overwhelm other years.

## The Optimal Risky Portfolio With A Riskfree Asset

Now we can expand the asset allocation problem to include a risk-free asset. Let us continue to use the input data from the bottom of Spreadsheet 6.5, but now assume a realistic correlation coefficient between stocks and bonds of 0.20. Suppose then that we are still confined to the risky bond and stock funds, but now can also invest in risk-free T-bills yielding 5 . Figure 6.5 shows the opportunity set generated from the bond and stock funds. This is the same opportunity set as graphed in Figure 6.4 with pB 0.20.

## Discussion And Conclusions

Sections 2.3 and 2.4 described two complementary perspectives in which cointegration is viewed as a basis for implicitly hedging unknown risk factors and also as a basis for suggesting possible opportunities for statistical arbitrage. Section 2.5 provided a controlled simulation in which the cointegration approach was demonstrated upon artificial time series with known properties. Finally, the spreadsheet analysis in Section 2.6 demonstrates an application of these tools to a real-world problem. The data used for this demonstration consisted of the daily closing prices of the 50 equities which constituted the STOXX 50 index as of 4 July 2002, analysed over a period from 14 September 1998 to 3 July 2002. We have noted that the use of daily closing prices will introduce some spurious effects due to the non-synchronous closing times of the markets on which these equities trade, so the specific results themselves can only be taken as indicative of a...

## Benchmark the Financial Statistics and Ratios

The results of the benchmarking exercise are displayed on spreadsheet output pages that present the data for each company in an easy-to-compare format (see Exhibits 1.53 and 1.54). These pages also display the mean, median, maximum (high), and minimum (low) for the universe's selected financial statistics and ratios.

## Fraud Scams and Off Exchange

You may wish to use the Broker-Dealer Due Diligence Checklist in your research. (See Figure 7.1.) Feel free to customize it to fit your specific needs and wants. For example, adding specific platform features, indicators, currencies or orders you require. An expanded spreadsheet of this may be downloaded at www.fxpraxis.com.

## The Shock Is in Your Favor

The following code to create a gap-adjusted and shock-adjusted series cannot be done using TeleTrac, Easy Language, or spreadsheets because the new data series is shorter than the old one. The following code in FORTRAN reads the original data series OLD and creates an adjusted series NEW.

## Stepbystep Instructions

Select Display in Spreadsheet from the context menu for MBNA Cp in the Portfolio View. This will display the Display Spreadsheet Dialog. From the Display Spreadsheet Dialog, select Select new fields to display from the upper left corner. Figure 2. Location of Use all available data. 6. Press the OK button and view your spreadsheet. Figure 2. Location of Use all available data. 6. Press the OK button and view your spreadsheet. Figure 3. Initial display of spreadsheet. 7. By default, the spreadsheet opens to the last available date. Notice how the predicted value is shifted forward one date.

## How The Retail Spot Forex Works

In the spot forex market, it is common for currencies to move 100 to 300 pips points in a 24-hour session. For example, using my pivots spreadsheet, at one point the projected range for tomorrow's Swiss Franc trading was 308 and the actual range was 154. I recommend the Swiss Franc, because of all the currencies it moves the most. If you like volatility, there is no currency more volatile than the Franc.

## The CAPM and the Index Model

To illustrate the investor's problem, we first produce 24 possible observations for the risk-free rate and the market index. Using the random number generator from a spreadsheet package (e.g., you can use data analysis tools in Microsoft Excel), we draw 24 observations from a normal distribution. These random numbers capture the phenomenon that actual returns will differ from expected returns This is the statistical noise that accompanies all real-world return data. For the risk-free rate we set a mean of 5 and a standard deviation of 1.5 and

## Mowte Carlo Simulation

Simulation is any analytical method that is meant to imitate a real-life system, especially when other analyses are too mathematically complex or too difficult to reproduce. Spreadsheet risk analysis uses both a spreadsheet model and simulation to analyze the effect of varying inputs based on outputs of the modeled system. One type of spreadsheet simulation is Monte Carlo simulation, which randomly generates values for uncertain variables over and over to simulate a real-life model.

## Psychological Influences on Analysts Decisions

We also saw that increased information makes experts more confident, but unfortunately not more accurate. For the security analyst, as we considered briefly, the availability of information has gone up almost exponentially in recent years. The analyst traveling with a laptop can run spreadsheets, check stock quotes, receive faxes, or even tap into voluminous databases. At home base, his data input capabilities increase enormously. As a J. P. Morgan technology analyst puts it, This business

## Copy collate spiral binding doublesided Enough said

Large spreadsheets comparing the financial stats of different deals, client competitors, or even the investment banking capabilities of competing firms. As an analyst, you will spend a significant portion of your waking hours updating these and ensuring their accuracy (and dreaming about them in your sleeping hours) as an associate, you will oversee their preparation and double-check for accuracy.

## What the Performance Summary Does Not Show

The summary also does not give a histogram of your trades. You may wish to export your data to a spreadsheet to look for the maximum favorable excursion and maximum adverse excursion. These quantities will be explained in chapter 4 with the 65sma-3cc system. The summary does not give you any feel for the variation in test results. It does not give a standard deviation of trade profits and losses for all trades. The variability is another important item you should calculate, using a spreadsheet if needed. The variation tells you what you can expect for volatility of returns.

## Step Three Identify Comparables

After reviewing corporate disclosures and interviewing management to add depth to the picture, IR can move to the next step in definition determining the comparable group. To do this, IR should create a spreadsheet with a dozen or more companies, in the same industry or with the same business model, and with a similar market cap. These companies are commonly referred to as comps.

You will obtain the same result with the Excel template. Note that you may find a difference of a few cents between results you get by using the table and those from the Excel model. This is because the table goes out only six decimal places, leaving the possibility of a small error due to rounding.

## Step Four Determine Relative Value

The valuation comparison is a comparison of financials across the company's comp group. IROs should construct a spreadsheet of all comps and their market cap, stock price, last year's earnings, next year's earnings estimates (the analyst consensus), long-term debt, last year's EBITDA (the con-

## Few Final Thoughts About Part

I his part started out by taking a closer look at which system testing measures are more useful than others, and why it could be a good idea to expand the analysis work a bit with the help of a spreadsheet program, like MS Excel or Lotus 1-2-3. To properly evaluate a trading system, it is of paramount importance to use a set of universal measures that give an equal weighting to all the trades, no matter where and when they are derived. To accomplish this, it also is important to use the right type of data. As we have seen, not all data can be used all the time knowing when to use what is vital in building a robust and profitable trading system.

## Figure 46 A histogram of the 65sma3cc system over a narrower range of profits and losses Notice that only a small

The 65sma-3cc curve is more sharply peaked than the standard normal curve. To generate a normal distribution that would fit our data, I used a Microsoft Excel 5.0 spreadsheet and employed an iterative process of manually tweaking the values. The fitted normal curve, with a mean of-0.16 and standard deviation of 0.18 is shown in Figure 4.8. The fitted normal distribution shows that the actual 65sma-3cc distribution has fat tails. This simply means that there is a larger probability for the big trades than would be expected from the normal distribution. This chart shows that unusually large profits or losses are more likely than might normally be expected.

## Taking a Step Back for a Moment

We have built our spreadsheet up to where we have a useful tool in analyzing our pair. What's more, we also have some fundamental and technical information to also compare each individual stock with, just to double-check the statistics that have been presented. So now we ask, what constitutes a decent convergence pair's setup I would actually recommend waiting until the density curve is pegged out at 0 or 1. The reason is that the entire sector and two stocks are much more volatile than other, slower-moving equities like energy stocks. When trading slower oil stocks, I will use the 0.003 rule, but only after I have scanned the historical data to see if entries of 0.003 thousandths of zero or one proved to be profitable. The pairs information contained in this book leaves much to the decision of the individual trader, and there is no single hard number that works in every case. This is very important to remember If you simply build a spreadsheet and wait for a 0.003 or 0.997 entry...

## Example 53 A Trading Desk Evaluates Block Brokers

Your firm audits the performance of block brokers. Your firm calculates the post-trade, market-risk-adjusted dollar returns on stocks purchased from block brokers. On that basis, you classify each trade as unprofitable or profitable. You have summarized the performance of the brokers in a spreadsheet, excerpted in Table 5-2 for November 2003. (The broker names are coded BB001 and BB002.)

## Characteristics Of Moving Average Entries

Available approach that is easy to understand and implement, even in a spreadsheet. However, as with most trend-following methods, moving averages lag the market, i.e., the trader is late entering into any move. Faster moving averages can reduce lag or delay, but at the expense of more numerous whipsaws.

## FIGURE 57 Tail Probabilities for a 95 Percent Confidence Interval

There are as many different normal distributions as there are choices for mean (jx) and variance (a2). We can answer all of the above questions in terms of any normal distribution. Spreadsheets, for example, have functions for the normal cdf for any specification of mean and variance. For the sake of efficiency, however, we would like to refer all prob- Standard normal probabilities can also be computed with spreadsheets, statistical and econometric software, and programming languages. Tables of the cumulative distribution function for the standard normal random variable are in the back of this book. Table 5-5 shows an excerpt from those tables. N(x) is a conventional notation for the cdf of a standard normal variable.22

## TABLE 56 Mean and Standard Dviation for Three Allocations in percent

To answer this question, note that P(RB < 3.75) iV(-0.90625). We can round 0.90625 to 0.91 for use with tables of the standard normal cdf. First, we calculate W(-0.91) 1 - JV(0.91) 1 - 0.8186 0.1814 or about 18.1 percent. Using a spreadsheet function for the standard normal cdf on -0.90625 without rounding, we get 18.24 percent or about 18.2 percent. The safety-first optimal portfolio has a roughly 18 percent chance of not meeting a 3.75 percent return threshold.

## How Lenders Evaluate Properties

Commercial lenders are people of a different breed. (Think about it They live and die making decisions based on Excel spreadsheets.) They look at properties from a different point of view than the rest of us investors. So, it's wise for you to understand where they're coming from when they reject your deal. In this section, we help you to understand them, and we show you how to put your best foot forward in getting your deal approved for the best loan.

## Evaluating income Moving from fiction to useful figures

To evaluate the income side of your budget, we advise that you painstakingly record and verify all income by using a zero-based budget concept. A zero-based budget is where you start with a blank piece of paper (or spreadsheet, if you enjoy computer software) and individually, tenant by tenant, create the projected rents and income stream for the property. (A similar zero-based budget concept is useful for determining your likely or expected expenses and is discussed in further detail later in the chapter.)

## Putting It All Together

To save some arithmetic, there are many computer-generated systems for calculating this return. Some are proprietary systems written by their firms, and many are just modified spreadsheet systems. If you have access to one of these systems it will save time, but having an automated system is not necessary. The worksheet in Figure 5.1 will help you calculate the return yourself. All you need is a calculator.

## Exhibit 9.12 Monthly Excess Return Data For Two Portfolios And Three Risk Factors

Problems 6-7 refer to the data contained in Exhibit 9.12, which lists 30 monthly excess returns to two different actively managed stock portfolios (A and B) and three different common risk factors (1,2, and 3). (Note You may find it useful to use a computer spreadsheet program (e.g., Microsoft Excel) to calculate your answers.)

## Looks Can Be Deceiving

We walked through a (not surprisingly) old door and out onto what was apparently George's trading floor. It was a scattered group of more old doors sitting on top of sawhorses, sitting on top of old carpet. The computer screens looked newer, but the 20 or so traders were all wearing either older suits (even the young guys) or casual clothes. The other thing that looked remarkably different was that there were an equal number of men and women here or perhaps even more women than men. All the traders that I saw had the same things on their screen an Excel spreadsheet up with a bunch of columns stretching into eternity, a set of prices flashing quotes for several currencies, and charts. The place wasn't quiet, but it wasn't a playground either. I could tell that I was standing in the midst of a focused crowd.

## Real Options Behind The Scenes

The first time-step has two nodes (S0u and S0d), while the second time-step has three nodes (S0u2, S0ud, and S0d2), and so on. Therefore, as we have seen previously, to obtain 1,000 time-steps, we need to calculate 1, 2, 3 . . . 1,001 nodes, which is equivalent to calculating 501,501 nodes. If we intend to perform 10,000 simulation trials on the options calculation, we will need approximately 5 X 109 nodal calculations, equivalent to 299 Excel spreadsheets or 4.6 GB of memory space. Definitely a daunting task, to say the least, and we clearly see here the need for using software to facilitate such calculations.2 One noteworthy item is that the tree below is something called a recombining tree, where at time-step 2, the middle node (S0ud) is the same as time-step 1's lower bifurcation of S0u and upper bifurcation of S0 d.

## What Is the Interbank

Prior to computing convenience, traders limited their view to a few currencies and were exhausted by the process of seeking these little profit opportunities. The study of market theory inevitably reveals the concept of efficiency. The Efficient Market Theory states that as more participants seek out profit potentials, the spreads become increasingly narrow until opportunities disappear. This implies that the illustrated spreadsheet has a limited

## Tracking Volatilities on Your

The next step is to start compiling a list of data for date, currency pair price, implied one-month volatility, and implied three-month volatility for the currency pairs you care about. The best way to generate this list is through a spreadsheet program such as Microsoft Excel, which makes graphing trends much easier. It might also be beneficial to find the difference between the one-month and three-month volatilities to look for large differentials or to calculate one-month volatility as a percentage of three-month volatility.

## Current State Of Systems

One of the most prevalent problems is that the antiquity of the major clearing and settlement systems in the back office has meant that they lack the flexibility to be able to handle the welter of new financial products emanating from the front office. Because of this, there is frequent recourse to manual intervention and Excel spreadsheets, with all the attendant potential for error that this entails.1

## The Mind And Day Of An Analyst

A day in the life of the analyst is spent delving into the intricacies of a company and an industry, which means due diligence, analysis and spreadsheets, conferences, traveling, talking to CEOs, touring factories and kicking the tires, listening to employees talk about the latest innovations or development, dialing for discourse with investors, reading the trades, observing the consumer marketplace, spotting trends, fielding calls from institutional salespeople and traders, inputting numbers into valuation models, deciding on buy, hold, or sell recommendations, and writing them into a readable, compelling report that says something useful and incremental. Pressure and stress are just a few of the occupational hazards, because millions of dollars are on the line with every stock pick. Therefore, if an analyst seems impatient or abrupt, it's par for the course.

## Creating the Right Teams and Partnerships

The value of the right type of partnership is that each person brings a very unique skill set. For instance, Eric isn't the type of person who wants to sit down and create an Excel spreadsheet that shows whether the project is going to be profitable. But Sara loves doing that. Eric and Sara have a partner on their team who actually loves getting in his car and driving for two weeks across the state looking for deals. He enjoys talking to agents and driving to small towns and looking for 20-unit apartment buildings or strip centers. They have another person on their team who's excellent at marketing.

Get one year's history for the market you're day-trading. Drop it into a spreadsheet and start asking questions. Every time the market closed within 5 ticks of its high, how many times did it reach a new high the next day How far did it go the next day What about the days when that market closed within 5 ticks of the lows How low did it go the next day Once you get the answers, find out what happened when the market closed within 10 ticks of the high, and so on.

## Figure 125 Calculating the Regular and Alternative Minimum Tax Married Couple With AMTI

An optimal approach for clients subject to the AMT requires modeling the all o cation t o tax-exemp t vers us taxabl e b onds. Adjustment of the all o cation b etween the two types of bonds can meaningfully increase the clien t's net after-tax income, which is the tax-aware approach. To start the process, we select yields on representative high-grade tax-exempt and government agency bonds that have effective maturities similar to the portfolio's. For this example, the bond portfolio is 30 million and the yield on an appropriate municipal bond is 3 percent, whereas the taxable b ond is o ffering a yield 4.5 percent. Next we construct a spreadsheet that includes the items necessary to estimate taxes and net income for the investor at both the regular tax rate of 35 percent and the AMT rate of 28 percent, as shown in FIGURE 12.5.

## The Best Computer in the World Doesnt Help Broke Investors

Allen came to Redding well prepared to discuss his financial dilemma. He brought along six sets of computer spreadsheets to show me why he was losing money every month. None, however, offered a solution for how he might stop losing money. Allen saved that problem for me to solve.

## Risk Tolerance And Asset Allocation

The investor attempts to maximize utility, U, by choosing the best allocation to the risky asset, y. To illustrate, we use a spreadsheet program to determine the effect of y on the utility of an investor with A 4. We input y in column (1) and use the spreadsheet in Table 7.1 to compute E(rc), oC, and U, using equations 7.1-7.4. Spreadsheet We can readily generate an investor's indifference curves using a spreadsheet. Table 7.2 contains risk-return combinations with utility values of 5 and 9 for two investors, one with A 2 and the other with A 4. For example, column (2) uses equation 7.6 to calculate the expected return that must be paired with the standard deviation in column (1) for an investor with A 2 to derive a utility value of U 5. Column 3 repeats the calculations for a higher utility value, U 9. The plot of these expected return-standard deviation combinations appears in Figure 7.5 as the two curves labeled A 2.

## Financial Due Diligence

Can management reach the forecast objectives Projections are structured around the objectives developed by the management team during the planning process. The marketing, sales, and operations strategies and plans indicate the financial requirements. The industry trend analyses imply specific assumptions about likely future conditions. Investors will evaluate projections and the assumptions behind projections. Especially when projections were prepared using spreadsheet software by the entrepreneurs themselves, expect close examination.

## Valuation Exercise Estimating Free Cash Flow for Cisco

Suffering through the pain and agony of creating all of these schedules longhand is reminiscent of the third grade at Saint Michael's Elementary School in Levittown, PA in 1958. Sister Mary Ignatius would be impressed by the ease with which a spreadsheet solves the divine

## Step 2 Can You Program All the Rules

Can all the rules in the trading strategy be entered into the computer or a spreadsheet program Have you assumed anything that was not programmed A strategy that cannot be tested cannot be evaluated. If you assume that you would not have been caught in a price shock because the program does not trade overnight, then you leave yourself open to unexpected losses, undercapitalization, and justifiable criticism.

## Statements What Statements

These are probably the easiest to fix, but the most difficult to find. Many times the only mistake in a client's taxes is that two numbers are added incorrectly or aren't entered correctly. The most common mistake that I see happens when people use spreadsheets they have formulated themselves to help them. If there is an error in the spreadsheet, it will continue into perpetuity as long as you don't know the error is there. This is why it's important to have someone else double-check your work. If they find an error that you missed, they may have just saved you some money. However, that's not to say that all errors result in your paying more taxes. There are times when the errors that are made result in the government's paying more back to you. But, when the IRS goes over your return and finds an error, if it's in their favor they'll let you know. If it's not, they'll let it go.

## Step 5 Choose the Testing Tools and Method

With more sophisticated strategy-testing software, it is no longer necessary to program the trading method in FORTRAN, BASIC, or C to test its success. In a few minutes, using a strategy testing package such as TeleTrac, Omega's System Writer, or even a Lotus or Quattro spreadsheet program, you can have a good idea of the viability of the technique. An increasing number of programmable graphics terminals and new strategy-testing software are available at very competitive prices. They all have the advantage of calculating profits and losses accurately, the flexibility of rule changes and data selection, and the ability to plot both data and profitability. In some cases, results can be read into spreadsheets for further evaluation. The time saved is well worth the price. For the more sophisticated analysts, supplementary software such as Manugistics Statgraphics and Mathsoft Mathcad are impressive tools for evaluating complex statistical relationships and expressing mathematical...

## Distinguishing the Fund from the Manager

Maintaining Capital Accounts The hedge fund must keep track of many details concerning the ownership interests of the partners investing in the fund. It is possible to use the general ledger to record some of this information. For example, the fund's accountants could establish a unique capital account for each investor (or even distinguishing multiple lots for investors). A hedge fund may have hundreds of different investors, so this could require many separate accounts. In contrast, a corporation may have only a few capital accounts common stock, additional paid-in capital, retained earnings. Frequently a hedge fund will establish a capital account for general partners and a capital account for limited partners. Details, including ownership percentages, cost, and tax information, can be preserved in subledgers or subsidiary ledgers. A portfolio accounting system should handle this information automatically, but a hedge fund may also track this portfolio data in many spreadsheets...

## Traditional And Quantitative Approaches To

Traditional managers conduct stock-specific analysis to develop a subjective assessment of each stock's unique attractiveness. Traditional managers talk with senior management closely study financial statements and other corporate disclosures conduct detailed, stock-specific competitive analysis and usually build spreadsheet models of a company's financial statements that provide an explicit link between various forecasts of financial metrics and stock prices. The traditional approach involves detailed analysis of a company and is often well equipped to

A volatility filter is a simple calculation. Deciding which volatility filter to use is more complex because it requires a number of different testing programs. The following Omega program and the spreadsheet program (Table 20-2) combine all of these features into one testing program. An option is used to select each feature. TABLE 20-2 Spreadsheet Sample Corel Quattro Spreadsheet Code TABLE 20-2 Spreadsheet Sample Corel Quattro Spreadsheet Code

## Simple Approximation

But rather than use a more complex model, we will now look at trading a mean-reverting product with various entry levels and see how the P L varies in practice. We look at a trading simulation on the spreadsheet VIX entry test.xls. We know this is a mean-reverting process. Here we follow a simple Bollinger band rule. We buy or sell the VIX after it has deviated by a certain amount from its moving average. As can be seen, the deviation from the simple moving average is somewhat normal looking but clearly is fat-tailed and skewed. We could address the skewness issue by having different bands for buying and selling, but that is not the point of this exercise. This is not meant to be a realistic trading idea. It is just intended to show that equation (6.29) has some applicability to a situation that we know does not follow the necessary simplifying assumptions.

## Explain The Shape Of Feasible Region Of Risky Assets

This problem cannot be reduced to the solution of a set of linear equations It is termed a quadratic program, since the objective is quadratic and the constraints are linear equalities and inequalities. Special computer programs are available for solving such problems, but small to moderate-sized problems of this type can be solved readily with spreadsheet programs. In the financial industry there are a multitude of special-purpose programs designed to solve this problem for hundreds or even thousands of assets for the vector v1 (uj, . , uj). This solution can be found using a spreadsheet package that solves linear equations The coefficients of the equation are those of the covariance matrix, and the right-hand sides are ail l's. The resulting vj's are listed in the first column of the bottom part of Table 6.2 as components of the vector v Next we normalize the u 's so that they sum to 1, obtaining luj's as

## Figure 152 Sample Holdings Report

Spreadsheet rather than a preprinted form is recommended, as space can be expanded or contracted to accommodate all entries with ease. To save time, an organization should construct the form using nomenclature cons is tent with that used in all functional areas of the firm. For example, do mestic stocks can be categorized in various acceptable ways by capital-izat i o n and or style, but it should be done consistently to avoid confusing the client by having one nomenclature for the initial questionnaire, asset

## FIGURE 88 Fitted Regression Line Explaining the Inflation Rate Using Growth in the Money Supply by Country 19702001

We are showing how to solve the linear regression equation step by step to make the source of the numbers clear. Typically, an analyst will use the data analysis function on a spreadsheet or a statistical package to perform linear regression analysis. Later, we will discuss how to use regression residuals to quantify the uncertainty in a regression model.

## Layered Tax Allocation Example

Needless to say, this methodology puts severe demands on the tax accountants to keep track of each of these cost layers. If there are many investors and many positions, and if investments are held for many periods, the data needs and computational effort to produce tax returns can be enormous. To make matters worse, smaller funds in the past have done these calculations by hand in spreadsheets, requiring duplicate efforts to maintain positions and partnership allocations.

## Portfolios Of Two Risky Assets

This technique for computing the variance from the border-multiplied covariance matrix is general it applies to any number of assets and is easily implemented on a spreadsheet. Concept Check 1 asks you to try the rule for a three-asset portfolio. Use this problem to verify that you are comfortable with this concept.

## Selecting The Test Data

The entire futures contract, or more than one contract, are tested. Using full contracts is a tedious process and can result in overlapping test periods and duplications of results. Individual tables of results must be combined in a spreadsheet to be evaluated. Figure 21-1 gives an example of the test bias resulting from carelessly using all contracts within the range December 1992 through December 1996. Once the number of tests being performed at any one time is seen, it is easy to construct a distribution (Figure 21-16) by counting the horizontal bars at specific points. This distribution yill show the testing bias due to duplication. Because of the 18-month life of the contracts, there was only 1 delivery month tested in the last half of 1991, compared with six contracts tested during the middle period, falling off to two contracts at the end of 1996.

## Searching Forthe Optimal Result

Calculate the number of tests you will run before beginning an optimization. Although the time may not be a problem, your test software may not be_set up to hold the number of test results that you are creating. All computers have limits. If you generate as many as 1,000 combinations, remember that you will need to evaluate them using a spreadsheet. Large numbers of rows and columns, combined with numerous markets, can create an unmanageably large spreadsheet.

## The Role of Computational Aids

While spreadsheet technology has removed much of the tedium involved in illustrating and projecting investment performance, it should not be considered a complete tool kit. Technical computing software performs a different function than spreadsheet programs. It can make any numerical calculation that can be done by a spreadsheet program, but it is primarily used to perform symbolic analysis. That is, rather than operating on the numbers of a specific real estate project, it solves for the relationships between the variables used in the mathematics to reach the conclusions that drive business decisions for all projects. Such analysis provides a more general view of the process not tied to any particular investment and permits one to look behind spreadsheet icons at their inner workings to more fully understand the total picture. The market is competitive. Principals and brokers compete for the best deals. Many decisions must be made very fast. Although spreadsheets are ubiquitous, in a...

## How Much Time is Needed

Trading signals can be generated manually via a simple PC spreadsheet. Just keep careful records and a trading log. You can also automate trading signals with products such as TradeStation or even Microsoft EXCEL. The Turtle trading course includes recommendations for both PC and Mac software packages.

## Single Year Relationships And Project Data

As an example of a specific project, input variables are entered from Table 4-1. These match the inputs in the companion Excel worksheet provided in the electronic files for this chapter. Thus, cf0 is the initial cash flow to be received at the end of year one. The first year of compounding is 12 months later at the end of year two, hence only n -1 years of compounded cashflows occur during the investor's holding period. This has repercussions when considering the equity reversion at the time of sale because value compounds for the full n periods. The rationale is that at the time of sale, the next investor anticipates the end-of-year income (to be received 12 months after his acquisition) in making his acquisition decision. So the first investor collects the first cash flow without any compounding and n -1 years of compounded cash flows, but receives the benefit of n years of compounded cash flow because he prices the property for sale on the basis of n compounded cash flows. Figure...

## Property management checkpoints

Web-based property management software for all of our properties. Information is available to us 24 7 with a touch of a keystroke. So at anytime, we can find out about project accounting costs, budget monitoring, profit and loss reports, payable and receivable reports, tenant information, and maintenance status. Depending on the project, we create our own spreadsheets by using a program such as Microsoft Excel. For more expansive projects with multiple people and where sharing is involved, we'll use an online program such as Microsoft Groove.

## The Exchange Rate And Related Financial Data

The investigation is based on the London daily closing prices for the EUR USD exchange rate.3 In the absence of an indisputable theory of exchange rate determination, we assumed that the EUR USD exchange rate could be explained by that rate's recent evolution, volatility spillovers from other financial markets, and macro-economic and monetary policy expectations. With this in mind it seemed reasonable to include, as potential inputs, other leading traded exchange rates, the evolution of important stock and commodity prices, and, as a measure of macro-economic and monetary policy expectations, the evolution of the yield curve. The data retained is presented in Table 1.1 along with the relevant Datastream mnemonics, and can be reviewed in Sheet 1 of the DataAppendix.xls Excel spreadsheet.

## Practical Considerations

Technical analysis and computers have become synonymous with each other and, with the simple steps provided by development software such as TradeStation, MetaStock, and spreadsheets, it would be rare to trade a new technical system without first running it through a computer to simulate its past performance. Those who do not use a computer directly are strongly influenced by the computer systems of others.

## QWhen did you first develop a system and how different is your system now from your first idea

A The first program I purchased to develop systems was back in the early 1990s. The program was the Telerate Snap platform developed by an old friend of mine, Tim Slater. In its day it was the Rolls Royce of trading platforms. Before that I used VisiCalc and Lotus 1-2-3, developing elaborate trading spreadsheets. We have come a long way since the early days of computers. The curious thing is the simple systems I developed in the early days are similar to the way I trade today. Sometimes you have to take the long road to know you are doing it correctly.

## Weighing the pros with the cons

Perhaps the biggest headache of DRPs is the need to keep good records. Keep all your statements together and use a good spreadsheet program or accounting program if you plan on doing a lot of DRP investing. These records will become especially important at tax time, when you have to report any subsequent gains or losses from stock sales. Because capital gains taxes can be complicated as you sort out short term versus long term, DRP calculations can be a nightmare without good record keeping.

## Programming the System

The prevailing philosophy about software development is to try to use as much third-party software as possible. If you need to see the results of a TradeStation optimization, save the results to a spreadsheet, then sort, organize, analyze, and graph the results using your favorite spreadsheet. You can compare one set of tests with another if you save them all in the same format. For the more popular platforms, such as TradeStation and Meta-Stock, there are a number of user's groups, support groups, and newsletters that can be of great help. Some companies provide alternate statistical measurements for your tests, while others have an add-in portfolio program. It is a good idea to check to see if these services satisfy your needs before attacking the programming yourself.

## How To Use The Pivots Program

It calculates the low and high, pivot point, and resistance and support points for the current trading session, based on the open, high, low, and close of the preceding session. All you have to do is input the open, high, low, and close (no decimal points) and click on any open space in the spreadsheet. And, there you have it walaa all pivot resistance support points for the next trading session will appear before your very eyes. It is important to track the average range, as this information is not available anywhere else. Going into a trading session, it is important to know this average. To track the range, start the range total off at zero. Then, add the current session's actual range to the range total, and do so every day. At the same time, increment the of days by one, again starting at zero. By clicking on empty space in the spreadsheet, the average daily range will be calculated for you. Nowhere else is this average available. It is important to...

## Interaction System Design and Money Management 213 Monthly Equity Curve Filtered Breakout for DM

This system had a steady increase in equity with several significant retracements. We imported the monthly equity curve into a spreadsheet and analyzed the interval change in equity over 1, 3, 6, and 12 months. Those data appear in Table 7.5.

## Saving For The Long

In Chapter 17 we described the framework that the Association of Investment Management and Research (AIMR) has established to help financial advisers communicate with and involve client households in structuring their savings investment plans.2 Our objective here is to quantify the essentials of savings investment plans and adapt them to environments in which investors confront both inflation and taxes. As a first step in the process, we set up a spreadsheet for a simple retirement plan, ignoring for the moment saving for other objectives.

## The Retirement Annuity

We can easily obtain your retirement annuity from Spreadsheet 18.1, where we have hidden the lines for ages 32-34, 36-44, 46-54, and 56-64. You can obtain all the spreadsheets in this chapter from the Web page for the text http www.mhhe.com bkm. Let's first see how this spreadsheet was constructed. To view the formulas of all cells in an Excel spreadsheet, choose Preferences under the Tools menu, and select the box Formulas in the View tab. The formula view of Spreadsheet 18.1 is also shown on the next page (numbers are user inputs).

## Creating And Solving A Customized Option Using The Software

From the main index, select Custom Lattice to launch the customized options analysis. Click on Step I Reset Sheet to reset the spreadsheet before creating a customized option model. This is important because any prior input parameters will be cleared from memory. Then, click on Step II Enter Starting Asset Value, and you will see the dialog box shown in Figure 9.4.

## An Alternative Savings Plan

In Spreadsheet 18.2, we saved a constant fraction of income. But since real income grows over time (nominal income grows at 7 while inflation is only 3 ), we might consider deferring our savings toward future years when our real income is higher. By applying a higher savings rate to our future (higher) real income, we can afford to reduce the current savings rate. In Spreadsheet 18.3, we use a base savings rate of 10 (lower than the savings rate in the previous spreadsheet), but we increase the savings target by 3 per year. Saving in each year therefore equals a fixed savings rate times annual income (column B), times 1.03t. By saving a larger fraction of income in later years, when real income is larger, you create a smoother profile of real consumption. Spreadsheet 18.3 shows that with an initial savings rate of 10 , compared with the unchanging 15 rate in the previous spreadsheet, you can achieve a retirement annuity of 59,918, larger than the 49,668 annuity in the previous plan.

## Keeping an Idealized Trade Notebook

Corey explains, In addition to keeping a simple spreadsheet that tracks trading performance (which is essential in knowing when you're making mistakes and correcting them), I use a different kind of trading journal that I call my idealized trade notebook. In this notebook, I print off the intraday chart (I use the five-minute chart most frequently) of the stock or index I traded for the day. Also, if there are particular charts I find interesting, I annotate by hand what I deem to be ideal (or best) trades based on my understanding of price behavior and opportunity. Through looking at the charts at the end of the day without the pressure of real-time trading, I am able to see new patterns that I had missed I then overlay my fills to see how close I came to achieving the total potential move This serves a dual purpose of deeper visualization of my performance, but more importantly, helps clarify the

## Roth IRA with the Progressive Tax Code

As we have noted, a traditional IRA is identical to the benchmark tax shelter set up under two alternative tax codes in Spreadsheets 18.5 and 18.7. We saw that, as a general rule, the effectiveness of a tax shelter depends on the progressivity of the tax code lower tax rates during retirement favor the postponement of tax obligations until one's retirement years. However, with a Roth IRA, you pay no taxes at all on withdrawals during the retirement phase. In this case, therefore, the effectiveness of the shelter does not depend on the tax rates during the retirement years. The question for any investor is whether this advantage is sufficient to compensate for the nondeductibility of contributions, which is the primary advantage of the traditional IRA. To evaluate the trade-off, Spreadsheet 18.8 modifies Spreadsheet 18.7 (progressive tax) to conform to the features of a Roth IRA, that is, we eliminate deductibility of contributions and taxes during the retirement phase. We keep...

## Real Options Analysis Toolkits Function Description for Excel

These functions are available for use in the full version of the Real Options Analysis Toolkit software. Once the full version is installed, simply click on Start, select Programs, then Crystal Ball and Real Options Analysis Toolkit. Next, select Functions. The software will be loaded into Excel, and the following models are directly accessible through Excel by typing them directly in a spreadsheet or by clicking on the Equation Wizard and selecting the Financial All categories. Scroll down to the RO section for a listing of all the models.

## Risky Investments and Capital Gains as Tax Shelters

So far we limited our discussion to safe investments that yield a sure 6 . This number, coupled with the inflation assumption (3 ), determined the results of various savings rules under the appropriate tax configuration. You must recognize, however, that the 6 return and 3 inflation are not hard numbers and consider the implications of other possible scenarios over the life of the savings plan. The spreadsheets we developed make scenario analysis quite easy. Once you set up a spreadsheet with a contemplated savings plan, you simply vary the inputs for ROR (the nominal rate of return) and inflation and record the implications for each scenario. The probabilities of possible deviations from the expected numbers and your risk tolerance will dictate which savings plan provides you with sufficient security of obtaining your goals. This sensitivity analysis will be even more important when you consider risky investments.

## Bond Pricing And Duration

Bond pricing and duration calculations can be cumbersome. The calculations are set up in a spreadsheet that is available on the Online Learning Center (www.mhhe.com bkm). The models allow you to calculate the price and duration for bonds of different maturities. The models also allow you to examine the sensitivity of the calculations to changes in coupon rate and yield to maturity.

## The Primary Insurance Amount

To examine SS performance another way, the last line in the table shows the longevity (number of payments) required to achieve an IRR of 6 . Except for the highest income bracket, all have life expectancy greater than this threshold. Why are these numbers so attractive, when SS is so often criticized for poor investment performance The reason benefits are so generous is that the PIA formula sets a high replacement rate relative to the SS tax rate, the proportion of income taxed. Taking history as a guide, to achieve an IRR equal to the rate of inflation plus the historical average rate on a safe investment such as T-bills (with a historical real rate of 0.7 ), the formula would need to incorporate a lower replacement rate. With a future rate of inflation of 3 , this would imply a nominal IRR of 3.7 . Is the ROR assumed in our spreadsheets (6 ) the right one to use, or is the expected IRR based on past real rates the correct one to use In short, we simply don't know. But averaging...