Drawdown calculation

[artz]

Hi,

I want to calculate drawdown on my account balance. Drawdown is generally defined as the peak to trough change in account value. Keeping a running total allows me to chart the growth in my account balance along with the greatest drops from the last highest balance in my account.

In the data set below, the drawdown column is based on the Account Balance in column B.

Can anyone in the Forum create a cell formula that will produce the output shown in column C, i.e., the Drawdown based on the Account Balance values?

Thanks,

Art

Excel Workbook
	A	B	C
1	Date	Acct. Balance	Drawdown
2	4/1/2013	263.58	0
3	4/2/2013	596.56	0
4	4/3/2013	878.16	0
5	4/4/2013	633.11	-245.04
6	4/5/2013	548.49	-329.67
7	4/6/2013	701.67	-176.48
8	4/7/2013	1024.92	-176.48
9	4/8/2013	1291.63	-176.48
10	4/9/2013	1419.32	-176.48
11	4/10/2013	1315.91	-103.41
12	4/11/2013	1137.63	-281.69
13	4/12/2013	1417.10	-2.22
14	4/13/2013	1571.46	-2.22
15	4/14/2013	1480.86	-90.60
16	4/15/2013	1616.88	-90.60
17	4/16/2013	1400.01	-216.87
18	4/17/2013	1636.06	-216.87
19	4/18/2013	2031.86	-216.87
20	4/19/2013	1824.19	-207.67
21	4/20/2013	2045.14	-207.67
22	4/21/2013	2325.56	-207.67
23	4/22/2013	2665.90	-207.67
24	4/23/2013	3009.04	-207.67
25	4/24/2013	3064.47	-207.67
26	4/25/2013	3322.90	-207.67
27	4/26/2013	3291.73	-31.17
28	4/27/2013	3173.89	-149.00
29	4/28/2013	3030.24	-292.66
Sheet1

 

[shg]

       ----A----- ----B----- ----C----- --D--
   1      Date      Price       Peak     DD  
   2   04/01/2013   $263.58    $263.58       
   3   04/02/2013   $596.56    $596.56       
   4   04/03/2013   $878.16    $878.16       
   5   04/04/2013   $633.11    $878.16  27.9%
   6   04/05/2013   $548.49    $878.16  37.5%
   7   04/06/2013   $701.67    $878.16       
   8   04/07/2013 $1,024.92  $1,024.92       
   9   04/08/2013 $1,291.63  $1,291.63       
  10   04/09/2013 $1,419.32  $1,419.32       
  11   04/10/2013 $1,315.91  $1,419.32   7.3%
  12   04/11/2013 $1,137.63  $1,419.32  19.8%
  13   04/12/2013 $1,417.10  $1,419.32       
  14   04/13/2013 $1,571.46  $1,571.46       
  15   04/14/2013 $1,480.86  $1,571.46   5.8%
  16   04/15/2013 $1,616.88  $1,616.88       
  17   04/16/2013 $1,400.01  $1,616.88  13.4%
  18   04/17/2013 $1,636.06  $1,636.06       
  19   04/18/2013 $2,031.86  $2,031.86       
  20   04/19/2013 $1,824.19  $2,031.86  10.2%
  21   04/20/2013 $2,045.14  $2,045.14       
  22   04/21/2013 $2,325.56  $2,325.56       
  23   04/22/2013 $2,665.90  $2,665.90       
  24   04/23/2013 $3,009.04  $3,009.04       
  25   04/24/2013 $3,064.47  $3,064.47       
  26   04/25/2013 $3,322.90  $3,322.90       
  27   04/26/2013 $3,291.73  $3,322.90   0.9%
  28   04/27/2013 $3,173.89  $3,322.90   4.5%
  29   04/28/2013 $3,030.24  $3,322.90   8.8%

In C2 and copy down, =MAX(B2,C1)

In D2 and copy down, =IF(B2 < N(B1), 1-B2/C2, "")

 

[artz]

SHG,

Thanks for your reply and code. A few things:

1) The percent drawdown is definitely useful. I'll use it but I would like to also have the dollar amount drawdown in addition to the percent drawdown as I showed in my original post.

2) At the start of my table, notice that until the first drawdown, I have 3 rows of leading zeros. In order to plot correctly, I need these leading zeros included.

3) Also, please notice in my table that after the drawdown changes from -329.67 in row 6, to -176.48 in row 7, rows 8-10 are also -176.48 until a new drawdown value in row 11. Again, in order to create a smoothing running drawdown for my chart, I would to include these values.

4) How did you create that nice looking table in your post? Excel Jeanie works but is not as compact.

Could you possibly modify your code to provide these features?

Thanks,

Art

 

[shg]

       ----A----- ----B----- ----C----- --D-- ----E----
   1      Date      Price       Peak    DD %    artz?  
   2   04/01/2013   $263.58    $263.58           $0.00 
   3   04/02/2013   $596.56    $596.56           $0.00 
   4   04/03/2013   $878.16    $878.16           $0.00 
   5   04/04/2013   $633.11    $878.16  27.9% ($245.05)
   6   04/05/2013   $548.49    $878.16  37.5% ($329.67)
   7   04/06/2013   $701.67    $878.16        ($176.49)
   8   04/07/2013 $1,024.92  $1,024.92        ($176.49)
   9   04/08/2013 $1,291.63  $1,291.63        ($176.49)
  10   04/09/2013 $1,419.32  $1,419.32        ($176.49)
  11   04/10/2013 $1,315.91  $1,419.32   7.3% ($103.41)
  12   04/11/2013 $1,137.63  $1,419.32  19.8% ($281.69)
  13   04/12/2013 $1,417.10  $1,419.32          ($2.22)
  14   04/13/2013 $1,571.46  $1,571.46          ($2.22)
  15   04/14/2013 $1,480.86  $1,571.46   5.8%  ($90.60)
  16   04/15/2013 $1,616.88  $1,616.88         ($90.60)
  17   04/16/2013 $1,400.01  $1,616.88  13.4% ($216.87)
  18   04/17/2013 $1,636.06  $1,636.06        ($216.87)
  19   04/18/2013 $2,031.86  $2,031.86        ($216.87)
  20   04/19/2013 $1,824.19  $2,031.86  10.2% ($207.67)
  21   04/20/2013 $2,045.14  $2,045.14        ($207.67)
  22   04/21/2013 $2,325.56  $2,325.56        ($207.67)
  23   04/22/2013 $2,665.90  $2,665.90        ($207.67)
  24   04/23/2013 $3,009.04  $3,009.04        ($207.67)
  25   04/24/2013 $3,064.47  $3,064.47        ($207.67)
  26   04/25/2013 $3,322.90  $3,322.90        ($207.67)
  27   04/26/2013 $3,291.73  $3,322.90   0.9%  ($31.17)
  28   04/27/2013 $3,173.89  $3,322.90   4.5% ($149.01)
  29   04/28/2013 $3,030.24  $3,322.90   8.8% ($292.66)

In E2 and copy down, =IF(C2=B2, N(E1), B2-C2)

 

[shg]

4) How did you create that nice looking table in your post?

I use the add-in posted at Add-In for posting

 

[artz]

SHG,

Thanks again. That's what I wanted. I also modified the percent drawdown to create the leading zeros and continue with the last updated drawdown percentage:

In D2 I put: =IF(C2=B2, N(D1), 1-B2/C2) and copied down. Works fine.

Also, thanks for the link to the Add-in. I downloaded it and will try it next time I need to post data.

Art

 

[shg]

You're welcome.

 

[cloobless]

You're welcome.

I had a related question for this 5-year old post (some things never go out of style).

How could one count the number of days it took to get to a new peak?

For example, in the below, the peak was held at $878.16 for four consecutive days. How could one calculate the time between peaks?
4 04/03/2013 $878.16 $878.16
5 04/04/2013 $633.11 $878.16 27.9%
6 04/05/2013 $548.49 $878.16 37.5%
7 04/06/2013 $701.67 $878.16

Code:
----A----- ----B----- ----C----- --D--
1 Date Price Peak DD
2 04/01/2013 $263.58 $263.58
3 04/02/2013 $596.56 $596.56
4 04/03/2013 $878.16 $878.16
5 04/04/2013 $633.11 $878.16 27.9%
6 04/05/2013 $548.49 $878.16 37.5%
7 04/06/2013 $701.67 $878.16
8 04/07/2013 $1,024.92 $1,024.92
9 04/08/2013 $1,291.63 $1,291.63
10 04/09/2013 $1,419.32 $1,419.32
11 04/10/2013 $1,315.91 $1,419.32 7.3%
12 04/11/2013 $1,137.63 $1,419.32 19.8%
13 04/12/2013 $1,417.10 $1,419.32
14 04/13/2013 $1,571.46 $1,571.46
15 04/14/2013 $1,480.86 $1,571.46 5.8%
16 04/15/2013 $1,616.88 $1,616.88
17 04/16/2013 $1,400.01 $1,616.88 13.4%
18 04/17/2013 $1,636.06 $1,636.06
19 04/18/2013 $2,031.86 $2,031.86
20 04/19/2013 $1,824.19 $2,031.86 10.2%
21 04/20/2013 $2,045.14 $2,045.14
22 04/21/2013 $2,325.56 $2,325.56
23 04/22/2013 $2,665.90 $2,665.90
24 04/23/2013 $3,009.04 $3,009.04
25 04/24/2013 $3,064.47 $3,064.47
26 04/25/2013 $3,322.90 $3,322.90
27 04/26/2013 $3,291.73 $3,322.90 0.9%
28 04/27/2013 $3,173.89 $3,322.90 4.5%
29 04/28/2013 $3,030.24 $3,322.90 8.8%