Calculate maximum drawdown with negative numbers

[billj]

Dear all,

I've used the macro from a site to calculate the maximum drawdown (a loss due to peak value vs. trough) for positive numbers. The macro can be found here:

http://investexcel.net/2450/maximum-drawdown-vba/

Now I've got a problem with this macro, as I need to calculate negative for a sample with both positive and negative numbers. I adapted the formula as:

temp = (TS(j,1) - TS(i,1)) / abs(TS(i,1))

But this is not right: a sample with 10, 20, -5, 8 will have a maximum drawdown of -150% using the macro. But the actual maximum drawndown should be: (-5-20)/20 = -125%, which is the loss from the peak 20 to the trough -5.

Is there any idea to adapt the original macro which is able to do the right calculation when there are negative numbers? I attached the file with macro here.

Thanks a lot in advance.

[zbor]

Will this work:

Please Login or Register  to view this content.

[billj]

Hi zbor. Thanks for the code. I noticed that you are comparing the current value i with the previous value i - 1. But the peak value and the trough value may not be necessarily adjacent to each other. For example, a sample of 10, 20, 5, 2 will have a maximum drawdown of 90%: (2-20)/20, while your macro gives a result of -75%. The reason is the peak value 20 and the trough value 2 are not adjacent to each other.


The idea of the original macro makes sense, but it does not work.

Will this work:

Please Login or Register  to view this content.

[zbor]

So you actually want this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[billj]

Hi, zbor. Thanks for your reply.

It is not that simple. Because maximum drawdown is defined to be a loss: it must be from the peak value to the trough value. Your formula can be wrong when the trend is actually from trough value to the peak value, which means there is no drawdown.

As an example, if you bought MicroSoft stock at a price of $100, and today if the price drops to $50, then you made a loss. If the price drops further to $25, then there is a loss again. During this period of time, the maximum drawdown is (25-100)/100 = -75%. If the other way round, if the price is now $125, then there is NO loss at all and the maximum drawdown is 0.

[zbor]

And if you bought that stock at 100 and price was 125 but now is 110 is that loss or not?

[billj]

And if you bought that stock at 100 and price was 125 but now is 110 is that loss or not?

Hi zbor, in this case there is a loss in the 2nd time period: (110-125)/125 = -12%

[zbor]

You actuall yneed to compare current value with the minimum value of the rest of range.

I started but don't have time to finnish. Hope it will be rnough for you to figure out solution.