INT(INDEX(TradeableEquity,ThisRow1)*(1/INDEX(NewSignals,ThisRow))/
MarginReqM4)*MarginReqM4)),IF(INDEX(ProfitM4,ThisRow)0,
INDEX(CommM4,ThisRow–1),0))
This formula calculates the accrued interest:
INDEX(AvailableEquity,ThisRow)*((1InterestRate2)^(1/365)1)
This formula calculates the bottom of a drawdown:
IF(INDEX(FlatTime,ThisRow)>INDEX(FlatTime,ThisRow1),
MIN(INDEX(Drawdown,ThisRowINDEX(FlatTime,ThisRow)1):
INDEX(Drawdown,ThisRow)),"")
Note that none of the formulas makes use of any normal cell references, such
as “B14,” referring to the cell in column B, row 14. To speed up the computing
time and to simplify error checking, I have given all the input and output variables
specific names, just as one would do to program the entire analysis spreadsheet in
a regular programming language, such as Visual Basic or Delphi.
Figures 27.4 to 27.7 are taken from the IASG Web site (www.iasg.com) and
are provided just to give you something to compare with. IASG is a Chicago-based
brokerage company that also tracks and publishes the results of many commodity
trading advisors and fund managers. The IASG Web site also has a comprehensive
help page that describes and shows the calculations behind many of the evaluation
variables used.
Other companies that also track many CTAs and fund managers include
The Barclays Group (www.barclaygrp.com), Van Hedge Fund Advisors
(www.hedgefund.com), Hedgefund.net (www.hedgefund.net), Hedgefund247
(www.hedgefund247.com), International Traders Research (www.managedfutures
.com), and Risk & Portfolio Management (www.rpm.se) in Sweden.
The numbers in Figures 27.4 to 27.7 are all real performance numbers from
real fund managers and CTAs. Note that several performance numbers in these
figures can’t be found in my spreadsheet in Figures 27.2 and 27.3, such as the
monthly performance summary in Figure 27.2 and several of the different ratios
in Figure 27.3. The time-window analysis in Figure 27.6 is basically the same as
that in Figure 27.3, except Figure 27.3 also holds the annualized numbers. Some
of the information in Figures 27.4 to 27.7 also is redundant, such as the monthly
numbers in Figure 27.4 and the chart of the monthly ROR at the bottom left in
Figure 27.7.
Figure 27.7 shows a few charts that also can be found on the IASG Web site.
The top left chart shows the equity growth of a specific advisor, while the bottom
right chart shows the drawdowns. These are essentially the same charts as those we
put together in Figures 26.8 and 26.9, and 26.13 and 26.14. They are also essen-CHAPTER 27 Spreadsheet Development 329