+ Reply to Thread
Results 1 to 3 of 3

Max draw down from a high water mark calculation

  1. #1
    Registered User
    Join Date
    06-15-2018
    Location
    London, England
    MS-Off Ver
    365
    Posts
    2

    Max draw down from a high water mark calculation

    I need some help with a formula or function that can calculate the maximum draw down from a high water mark.
    I have attached an extract from my sheet but it is very simple.
    Column A is sequential dates
    Column C is the daily profit/ loss
    Column B is the accumulated overall P&L

    So I'd like a formula that looks at the data as a whole (the series contains several years of daily data) and calculates the maximum pull back between new highs in column B.
    If it's possible to also calculate the number of days that pullback took and also the subsequent recovery time to a new high water mark that would be amazing but I'd settle just for the Max draw down at this point!

    Thanks in advance for any assistance. I'm using Office 365.
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Max draw down from a high water mark calculation

    =if(c4=1,aggregate(15,6,row(5:$28)/($c5:$c$28=1),1)-row(),"")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-15-2018
    Location
    London, England
    MS-Off Ver
    365
    Posts
    2

    Re: Max draw down from a high water mark calculation

    Thanks very much for the input on this.
    If I understand what you've done correctly - you've given me a formula that identifies the peaks in the data series and also a column that gives me the number of days to the next peak (ie recovery period).
    To progress along those lines I guess what I need now is a forumla to identify the low points between those peaks (the 1's in column C)
    Any suggestions?
    Thanks again for the feedback - much appreciated.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to insert water mark in Excel
    By Terressa in forum Excel General
    Replies: 4
    Last Post: 07-01-2015, 04:20 AM
  2. Add water mark to all sheets
    By Rob K in forum PowerPoint Formatting & General
    Replies: 5
    Last Post: 10-27-2014, 06:55 AM
  3. High Water Mark(Draw down Calculation)
    By sajeel in forum Excel General
    Replies: 5
    Last Post: 07-03-2012, 06:13 AM
  4. Image as water mark
    By tqm1 in forum Excel General
    Replies: 2
    Last Post: 05-25-2007, 06:25 AM
  5. How to keep a high water mark
    By TerryM in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-10-2006, 03:06 PM
  6. Water mark
    By m_ravindran in forum Excel General
    Replies: 1
    Last Post: 01-28-2006, 06:25 AM
  7. How To Remove Page 1 Water Mark?
    By travelersway in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-27-2005, 03:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1