Drawdowns.... again!

L

leobedford

New Member
Joined
Oct 16, 2007
Messages
2
Hi,

I've got what I'm sure is a fairly simple problem, but I can't seem to figure it out. I've been searching for ages, and while lots of people seem to have a similar problem, its not quite the same... I need to find the max drawn down for the growth of an investment. I've given an example below

100 - point a
110
120 - point c
115 - point d
125
130
140 - point e
135
130
128 - point f
130
140

so what I need to is to find the difference between the value of the portfolio at the end of the growth, and the end of the shrink, i.e. the difference between point c and d, or point e and f. But, I then need to divide the difference by the value at the top of the growth (points c and e), to give the percentage of the portfolio value which was lost. Is that possible? the fact that the shrink period could go on for a number of months makes this beyound my excel ability! also, the positive growth periods are not needed either, only declines. Any help would be much appreciated. The idea is, that once these percentages are calculated in their own column I can use a Max:)) formula to identify the max drawdown for that whole column.

thanks

Leo
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Assuming you have your investment values in column A starting at A1.

Ive broken the calc up in three steps.

Col B: Calc if its rising / falling based on the previous value.
Col C: The previous peak
Col D: if the value is at the end of a shrink. show the difference from the last peak.

Use these formulas

B2 =IF(A2>=A1,"R","F")
C2 =IF(A2>=A1,A2,C1)
D2 =IF(AND(B2="F",B3<>"F"),(A2-C2)/C2,"")
 
Upvote 0
Hi,
I need help. I struggled to find my answer digging through web but I don't find what I need.
So, taking the example from above, It is very close for what I need but is not exactly what I need.
here is a picture of what I need
http://www.tradingblox.com/forum/files/ahl_151.jpg

Notice that the Drawdown is calculated for every new point that is entered from the previous high, so a new series of drawdown will begin ONLY when a new high was made.

I would be very glad if someone can help me, because I'm so exhausted because I can't find the answer. Thanks. Apologize for my english.
 
Upvote 0
can you give numerical example as done in the first post. otherwise no one is going to be clear on exactly what you want.
 
Upvote 0
100> 0
110> 0
120> 0
140> 0
150> 0
140> -6.66
130> -13.333
120> -20
140> -6.66
145> -3.33
160> 0
140> -12.5
120> -25
110> -31.25
115> -28.12
120> -25
125> -21.87
140> -12.5
150> -6.25
180> 0
190> 0
220> 0

So, the first Maximum high value is 150 then it start to go down to 140
so, the first DrawDown will be ((140/150)-1)*100= -6.6666
then 130/150... => -13.33 and so on...
and the last "output" for the first DD will be 145/150..etc.=> -3.33
then 160 is not calculated because it exceed the first Maximum high(150)

Here is a link for the example made in excel http://img405.imageshack.us/img405/6159/drawdownexampleexcel.png

So, when the balance exceed the previous maxim high, the results should be 0 for DD coloumn.
I also wanted to attach the excel example file but I don't know how.

Thank you very much.
 
Last edited:
Upvote 0
Upvote 0
wow, crimson_b1ade you are AMAZING!!! this is EXACTLY what I wanted, unbelievable :eek: You really know what you are doing! Great job!
Thanks a lot! You made my life easier.
If I was to make it by myself... well... :LOL: no chance.
thanks again and a Happy New Year! (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Excel chart with a line that will adjust certain cells
Replies
0
Views
103
jondavis1987
J
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
607
ahaye
A
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
1K
felixstraube
F
S
  • Question
Formula to filter COUNTIFS for discrete values too
Replies
9
Views
711
RoryA
RoryA
N
  • Solved
Duplicates question and scoring system
Replies
6
Views
293
MountainFog
M

Forum statistics

Threads
1,215,684
Messages
6,126,199
Members
449,298
Latest member
Jest

Share this page

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
Back
Top