Hello this is Mark from Tradinformed.com and welcome to this video on How to
Calculate the Compound Annual Growth Rate in Excel this video is one of a
series that I am currently doing on trading metrics and how to analyze our
trading and backtest performance. So compound annual growth rate we need a
few inputs into this we need, the key one is our final capital our capital at the
end of our trading period and we need our starting capital in addition to that
we need the number of years that we have been trading the strategy because it is
obviously the compound Annual growth rate so we're going to measure this in
the number of years so you can see on the screen the calculation sheet that I
am using and I've highlighted in green the inputs that we need now as I said
the first part of this is final capital and then divided that by the starting
capital we also need the number of trading years and I'm just going to show
you the formula that I've used to calculate this I've used the built in
excel formula YEARFRAC and I've taken the final and the first date now we
always want to make our Excel backtest spreadsheets as automated as possible so
I've made this one flexible and so if we wanted to change this date it will
change the number of years that we are trading okay so the formula is nice and
simple we just take our final capital we divide it by our starting capital and we
put these in brackets and we put this we open up another set
of brackets and we do 1 divided by the number of years let me close that I'm
going to put everything in another set of brackets here and I'm going to subtract
1 from that and it really is as simple as that
I've got a compound annual growth rate of this strategy of 6.2% now we can make
this as I said we've got a bit of flexibility built into there and I'll
just demonstrate as I said if we could change this date here we can see a
compound annual growth rate change as well I can put that - 2015 number of
trading years declines and in this case our anual growth rate increases the
other thing of course to be aware of when we're looking at a trading strategy
is that leverage has a huge impact on how our trading strategy performs so
I've set this up one-to-one but if I was to switch this to two you see our
compound annual growth rate jumps also our drawdown and that is pretty much
always going to happen I could put that to three and again we can see much
higher annual growth rate but we are taking on a greater risk I hope you
enjoyed this video please check out the rest of the videos in this series on how
to measure your trading performance using trading metrics in Excel and for
more information about trading the financial markets please go to
www.tradinformed.com