Max Drawdown - No VBA please

[PeterBrazel]

I am looking at some performance indicators on trading transactions and want to calculate max drawdown. Max Drawdown=a loss in equity from a previous high. E.G. If you were climbing a 29000ft mountain peak and you fell down 2000 feet then the 2000 feet would be the equivalent of a drwadown.

I have a database of transactions that contain column M=Nett proceeds of each transaction and column N=Account balance.

Not sure how to go about this. Remember this is not a mountain but a mountain range. Equity is constantly increasing and decreasing.

Bit tricky.

Thanks in advance.

 

[PeterBrazel]

Richard,

No I don't think I was right in my prior response.

We need to find the highest peak (easy enough) then we need to find the deepest valley and subtract one from other. I think. So I guess this would be it.

MAX(N:N)-MIN(N:N)

This is not quite right because we need to find only the deepest valley after that peak. This is the tricky part.

 

[RichardS]

Peter,

Yes, it is a bit tricky. I've been messing around with trying to return the position of the highest peak, got

=MATCH(MAX(M:M),M:M)

which returns the row the maximum occurs on.

Keep checking back, I'm sure one of the guru's here will be able to expand on it and come up with a solution. It's a bit beyond me at the moment.

I know the board says it's midnight, but it's beer o'clock here. Avagoodweekend.

Richard

 

[DBA]

Peter,

Last try for me. I think I also understand now too. Here goes.

In "O2" enter this formula and drag down as far as needed

=MAX($M$2:$M2)

This will give the maximum value on each line up to that point.

Now in "P2" enter and drag down

=IF(O2=MAX($M:$M),MIN($O2:$O$1000),"")

This will ener the minimum from this point onwards ONLY if the number next to it in column O is indeed the maximum to that point.

Now in Q2 enter and drag down

=IF(P6="","",O6-P6)

The highest number in this column should be your answer.

Did I crack it this time?

Regards,

DBA

 

[William Lim]

I am not entirely sure if this would help but i look high and low for a solution as well for max drawdown and this seem to work for me.

Assuming column B is your portfolio value/price/nav etc and it all starts at row 2 because you have got headers.

Do this in column C (starting row 2) and drag it down:
MAX($B$2:B2)

Calcuate in column D the return over the max price. (drag down of course)
=B2/C2-1

And in your final cell for results just do =min(d:d)

First time posting on this board!!

 

[xorq]

Hi, so I read a lot of crap about how to calculate drawdown...and I feel bad for those who try to help without having a clue (I know, it sounds vain haha) so here is the solution:

You have column "I" for the cumulated datas
in "O3" enter this formula:

=IF(IF(MAX(I$2:I3)=I3,I3,I3-MAX(I$2:I3))>0,0,IF(MAX(I$2:I3)=I3,I3,I3-MAX(I$2:I3)))

Copy down and there you go

Xorq

 

[Simulationconsultant]

Since we're resurrecting a 10 year old thread, there's VBA for maximum drawdown at this website: