Dakota State University
BUS
418 Financial Futures & Options
Spring 2001
Computer Assignment #3
Account Equity and Charting
|
Assignment Due: |
Monday, March 19, 2001 10:00 pm |
|
|
|
|
|
|
Procedure
Extend the spreadsheet you created in Assignments #1 and #2. Add columns for Change, Long or Short, Commission, Profit or Loss, and Account Equity. See example on back of this sheet.
Each day you record prices decide whether you should be long (expecting the futures price to go up) or short (expecting the futures price to go down) the next day based on the buy or sell signals generated by the moving averages. When the 4-day moving average crosses the 10-day moving average going up, it is a buy signal. When the 4-day moving average crosses the 10-day moving average going down, it is a sell signal. Whenever you switch from long to short or vice versa charge yourself $25 commission.
Use a formula to calculate the change in futures price each day. Enter in your long or short position and any commission. Always trade just one contract. Use a formula to calculate the profit or loss for the day, e.g., =IF(H10="L",G10*25,-G10*25)*100 for Eurodollars. (See Excel Tips for an explanation of if/then formulas.) For Eurodollars each tick (a change of .01 in the futures price) represents $25 profit or loss. Therefore a change of three ticks, as on Jan 5, represents a change in account equity of $75. Whether that is a profit or a loss depends on whether the position is long or short. In this case the account was short one contract, and the price went down, so the equity went up. Note that different contracts will produce numbers rather different from these examples.
Use a formula to increase or decrease the account equity as the case may be.
Produce a chart of daily profit or loss and the account equity. Do not include commissions as a series. To do this, highlight the Profit or Loss and Account Equity columns all the way to the eventual end of the series and use the Chart Wizard. Select Custom Types (on a tab) and Line-Column on 2 axes (scroll down to see it). In the next screen name the two series appropriately and add the date column as the Category (X) Axis Label. Then put the legend at the bottom of the page. Give your chart a two-line title including contract name and month and Account Equity. Name each of the axes. Include "$" on the vertical axes. Do not have diagonal dates on the chart. Create the chart as a new sheet.
Stretch the legend so the two entries are not too close. Remove the border. Add a text box (from View/ Toolbars/Drawing) with the message: Note: Commissions not shown.
Use dashed gridlines. Make sure the gridlines match up with tick marks on both Y axes. The gridlines are actually attached to the left Y axis. Making the two scales multiples of each other will cause them to line up. Also, you will have to manually set the scales, tick sizes, etc. so that the X axis passes through 0 on both axes. The scale for the daily profit and loss will probably be greater than the other scale to keep the bars from overwhelming the account equity line.
When printing the chart extend the margins so the chart fills up the page. No headers or footers. Do not shade the plot area. Use dashed gridlines. When printing the cell formulas print the border row and column headings.
TURN IN
1. A hardcopy of the extended spreadsheet.
2. A hardcopy of the cell formulas.
3. A hardcopy of the Account Equity chart.
4-Day 10-Day L/ Profit/ Account
Date OI Open High Low Settle MA MA Change S Comm. loss equity
Jan 4 382,336 94.56 94.56 94.52 94.54 0 0
5 381,890 94.53 94.57 94.53 94.55 0.01 S -25 -25
8 387,101 94.54 94.55 94.51 94.52 -0.03 S 75 50
9 405,689 94.52 94.53 94.47 94.48 94.52 -0.04 S 100 150
10 406,203 94.48 94.48 94.36 94.46 94.50 -0.02 S 50 200
13 408,336 94.47 94.50 94.47 94.48 94.49 0.02 S -50 150
14 406,240 94.49 94.49 94.46 94.48 94.48 0.00 S 0 150
15 404,344 94.47 94.48 94.43 94.44 94.47 -0.04 S 100 250
16 400,166 94.43 94.47 94.42 94.44 94.46 0.00 S 0 250
17 399,687 94.44 94.49 94.43 94.47 94.46 94.49 0.03 S -75 175
20 400,458 94.48 94.48 94.44 94.45 94.45 94.48 -0.02 S 50 225
21 424,380 94.45 94.45 94.39 94.43 94.45 94.47 -0.02 S 50 275
22 432,756 94.42 94.44 94.40 94.42 94.44 94.46 -0.01 S 25 300
23 440,733 94.41 94.46 94.40 94.45 94.44 94.45 0.03 S -75 225
24 451,934 94.46 94.47 94.44 94.45 94.44 94.45 0.00 S 0 225
27 453,059 94.46 94.50 94.45 94.47 94.45 94.45 0.02 S -50 175
28 455,100 94.45 94.49 94.45 94.48 94.46 94.45 0.01 L -25 -25 125
29 454,950 94.48 94.52 94.46 94.49 94.47 94.46 0.01 L 25 150
30 456,870 94.50 94.55 94.48 94.51 94.49 94.46 0.02 L 50 200
Feb 1 462,450 94.50 94.53 94.47 94.50 94.50 94.47 -0.01 L -25 175
Feb 4 465,870 94.49 94.57 94.47 94.53 94.51 94.47 0.03 L 75 250
5
6