Tuesday, July 8, 2014

A Ready to Use Money Management Excel Spreadsheet

I am fond of saying that trading is NOT about being right or wrong! Trading IS about managing money.
To prove this to our members a few years ago, on a percentage of account basis, I presented my trades in a Microsoft Excel Spreadsheet.  Recently I had a request to resurrect the spreadsheet for others to use. This work and trading is from a few years ago, therefore the equities, ETFs, and options have been removed from the spreadsheet. It's purpose was to illustrate money management principles not trading prowess although it turned out to be a pretty good year.

Top right hand corner: Percent of Account Equity you are willing to risk on any one trade and the number of open positions you are willing to maintain.  They are set in this example at 3% of account equity and maximum positions at 6. Your requirements may be different just change those two cells.
  • Beginning Capital =100 which one looks at as a percent.  Columns 1,2,3,8,and 9 are self explanatory.
  • Column 4 is the amount of equity committed to this trade.  100 divided by 6 equals 16.67%.  For this spreadsheet I'm using equal dollar amounts in each position, if you have other means by all means use them.
  • Column 5 is just showing the number of currently open positions.
  • Column 6 is your perceived risk on the trade basis the stocks price. i.e. bot at $15 stop at $13 - Position Risk is 13.33%
  • Capital at risk is column 4 times column 6 - that is the percent of your total account.  A test in this cell makes sure that number is less than the 3% placed above.  If it is higher the cell is flagged with a message.
  • Column 12 takes the gain (loss) and adds (subtracts from previous) column 4 times column 11.
  • Something learned from Victor Sperandeo's Trader Vic books is that any time you move above 100 in the account you remove half the amount above 100 from trading during this accounting period.
If I had set 4 open positions then we would be committing 25% on an equal percent basis.  While this is a manual input here is what happens to the spreadsheet: In column 7 we would have many "Flags".  What this is telling you is that with a 25% commitment you are violating your 3% rule. Something must change, either the 3% or the percent commitment.
This is not to say that the portfolio risk shown (3%) or the maximum positions (6) is correct for you.  I believe one of the problems in this business is we tend to ignore YOU!.
If you wish to download this spreadsheet as seen and make changes to suit your individual needs
Download the Excel Spreadsheet HERE!

No comments: