# Max Drawdown - No VBA please

#### PeterBrazel

##### New Member
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### DBA

##### Board Regular
Peter,

Can you add a column "O" which gives you on each line -
The difference between the total of the current line at columns "M" & "N" and the previous line.
Then use the MAX() over column "O" to give you the answer?

Regards,

DBA

#### PeterBrazel

##### New Member
Either I do not understand or it does not work.

Given that I have Column M =Transaction result i.e. profit or loss on the transaction. Column N=running portfolio balance. What formula are you suggesting.

Peter Brazel, Shepparton

#### RichardS

##### Well-known Member
Could you possible post some sample data, and expected results?

Richard, Hamilton (Victoria is taking over!!)

#### DBA

##### Board Regular

Peter,

I was assuming that you were able to see (or at least be able to work out) the DrawDown somehow for each transaction based on the data you already have, then try to work out which was the highest value of those DrawDowns.

Is that a laymans view of it or have I missed what you are trying to acheive?

(I think the forum is being overrun by Victorians this afternoon) )

Regards,

DBA

#### PeterBrazel

##### New Member
OK here goes. This is the first group of transactions
Col M Col N Col O
Balance Fwd 41,354.00
-3,936.00 37,418.00
-1,236.00 36,182.00
14.00 36,196.00
4,964.00 41,160.00
5000.00 46160.00
-8000.00 38160.00

I just need to know what you are suggesting for column O at this stage.

We need to know what was the highest peak and then what was the biggest fall from that peak.

Thanks.

#### DBA

##### Board Regular

Peter,

Sorry I just re-read and realised that column M already has the data I was trying to work out in column O so you won't need it.

Will this give you the answer?

=MIN(M:M)

Regards,

DBA

#### PeterBrazel

##### New Member
No that will give me my biggest loss not my max drawdown.

#### RichardS

##### Well-known Member
So you need to know the Maximum level the portfolio balance reached, and the minimum it reached AFTER that maximum?

=MAX(N:N)

will give the peak of your portfolio performance. Just trying to get my head around how we restrict the search for the MIN to those portfolio balances AFTER the maximum is found.

Richard

#### PeterBrazel

##### New Member
Richard,

Yes its all Vics today. Thank you for taking time with this. I think you now understand what I am trying to do. Sometimes I lose it myself. I am getting these KPI's from a trading book and trying to implement them for my own benefit.

Yes I guess MAX(N:N) will give us our highest peak. Now what is our greatest fall from that peak. Is it that peak less our biggest loss. If so that would be MIN(M:M) but I am not so sure that this is right.
I guess it is our greatest fall after that peak occurred is it.

Peter Brazel

Replies
0
Views
6K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,194
Messages
5,768,778
Members
425,494
Latest member
Ragamacam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back