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

Thanks in advance.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

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.

Thanks again in advance.

Peter Brazel, Shepparton

Could you possible post some sample data, and expected results?

Richard, Hamilton (Victoria is taking over!!)

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

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.

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

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

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

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
8K

Threads
1,218,628
Messages
6,143,589
Members
450,494
Latest member
GolfNut39

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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