Hello this is Mark from Tradinformed.com. Welcome to the second part of this
video on back testing a MACD trading strategy using Excel. So the spreadsheet
we've got on screen comes from the first part of this video and we've calculated
that the MACD trading strategy would increase our capital from $1,000 to just
short of $17,000 but we might want to compare this trading strategy to other
trading strategies to see how effective it is and to see how easy it is to trade
I'm going to do this by inserting a couple of new columns, the first of these I'm
just gonna call trades. The second of these will be our drawdown. Scroll all the way
back up to the top here and the Trades is simply whether or not there was a
positive or negative trade in that particular week we do this saying
capital account of that week - the capital account of the previous week we can
drag this down and then double click on the bottom right hand corner. Drawdown we
calculate using the formula this week / highest value in the previous weeks
then we use F4 to lock the first cell so I'm going to make the whole formula one minus
expressed as a percentage and then double click on the bottom right hand corner to copy it
down to all the cells below scroll down to the beginning again and I've got the
metrics here that I'm going to use to stick them in here
total gross wins to start with we're going to use the formula equals sum IF AND and
the range of course will be the range of all our trades and we're going to use
F4 to lock this range and the criteria is going to be greater than
zero so these are all our positive winning trades
so now we can use this we can just copy this down here and the sum of our losses
I'm just going to use the criteria, the same range, there's going to be trades less
than zero. The profit factor we calculate using the absolute value of the gross wins
divided by the gross losses to get a pretty healthy profit factor of one
point seven nine our total net profit use the formula equals sum these two. We can
see there our total net profit is exactly the same as our capital at the
end, subtracted by our capital at the beginning, now we can just reuse these
formulas here but change them slightly the number of winning trades still SUM IF
here we use the formula count IF count
if all the trades greater than zero and count IF all the trades less than zero
and from this you can calculate percentage of profitable trades through
the winning trades divided by the sum of the winning and losing trades 51% of
profitable trades in this strategy now our largest profitable trade we've
already seen this formula, we use the same range, exactly the same range but we
use the MAX formula returns the largest value and we don't need this criteria here
the largest losing trade we use the corresponding formula which is MIN the number
of years trading I know that it's pretty much sixty two point five years worth of
trading from our first trade to the end of the backtest and we can use this
information to calculate our percentage profit per annum and we do this by using
the compound interest formula which is the final capital divided by the
starting capital using brackets and then we're going to put this the
to the power of one divided by the number of years trading and then we're going to
put this all in brackets subtract one percentage profit per annum it's a
respectable four point six three percent and then the last one we want to know is
what is our largest drawdown. Drawdowns can be very difficult psychologically
to take and people in general have a tendency to give up on trading
strategies when they see their capital account drawdown by a large amount we
calculate this by looking at our MAX the range down again we look at ourMAX here
I'm just gonna cheat by dragging this across to this column the drawdown
column next to it. Express this as a percentage, we see our largest drawdown is twenty three
percent over this time period
if you just want to focus on one of these metrics here percentage profit per annum if
you looked at the previous video when we looked at the percentage profit per
annum of the buy-and-hold strategy for the S&P 500 over the same time period we
saw that it was somewhere over 7% so you can see that this trading strategy does
not return as much per annum as just buying and holding the index would have
done however we do have quite a few variables we can tweak to test different
criteria and one of these if we have a look at the profitable trade we're
risking 5% loss per trade, if we wanted to increase this to 10% we can see our
percentage profit per annum increases to just short of 8% if we wanted to crease
this a bit further to say 15% risk per trade
you see we go percentage of profit per annum of over 10%.Equally we can see the
other factors are affected our profit factor is lower and our largest drawdown
is also lower. I hope you found this video useful and showing how we can
build a backtest for a trading strategy in this case over the long term. For more
trading information and information about backtesting trading strategies
please go to www.tradinformed.com