Max Drawdown - No VBA please

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

DBA

Board Regular
Joined
May 28, 2002
Messages
100
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
Joined
May 9, 2002
Messages
35
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
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
Could you possible post some sample data, and expected results?

Richard, Hamilton (Victoria is taking over!!)
 

DBA

Board Regular
Joined
May 28, 2002
Messages
100

ADVERTISEMENT

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
Joined
May 9, 2002
Messages
35
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
Joined
May 28, 2002
Messages
100

ADVERTISEMENT

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. :oops:

Will this give you the answer?

=MIN(M:M)

Regards,

DBA
 

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
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
Joined
May 9, 2002
Messages
35
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
 

Forum statistics

Threads
1,148,274
Messages
5,745,797
Members
423,974
Latest member
highvoltageacdc

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

Which adblocker are you using?

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
Top