Brian Dunning's FileMaker Custom Functions

_cf_Returns.Drawdown ( PeriodCount ; IncomingArray ; DrawdownSoFar ; MaxDrawdown ; Type )

This summarizes a drawndown history for a return series (typically a monthly or quarterly return stream representing an index, a portfolio, a security, etc.).

  Average rating: 4.0 (2 votes) Log in to vote

Peter Gerlings, CFA CAIA   Peter Gerlings, CFA CAIA
PDG
www.rembrandtadvisors.com

Share on Facebook Share on Twitter

  Sample input:
a number between zero and one, depending on the how far the returns at a point in time are below the previous market peak.
  Sample output:
0.1746
1
0.998

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

_cf_Returns.Drawdown ( PeriodCount ; IncomingArray ; DrawdownSoFar ; MaxDrawdown ; Type )

This summarizes a drawndown history for a return series (typically a monthly or quarterly return stream representing an index, a portfolio, a security, etc.). The value is "1" whenever there is a new market peak. Whenever the trailing return history is will be some value less than 1. It can never exceed one.

This is a recursive function, but does not call any other custom functions.

Note: "Type" is a boolean number: 1 = Max, 0 = Current. The latter is the assumed type if no value is provided. This determines what kind of drawdown result is provided, i.e the largest drawndown over the entire history (even if the current drawdown is zero), or just the current drawdawn. IMPORTANT!! The return stream MUST be provide in the IncomingArray parameter in CHRONOLOGICAL ORDER. I accomplish this by sorting the relationship based on the chronological date of each return entry, but there are other methods.

Written by Peter D. Gerlings, CFA, CAIA
August, 2011

 

Comments

Peter Gerlings   Peter Gerlings, Amsterdam
Jan 11, 2018
After posting I went and tried to clean this up a little. Hopefully the comments are more helpful. I also got rid of a parameter

/*

_cf_Returns.Drawdown ( ReturnArray ; DrawdownSoFar ; MaxDrawdown ; Type )


This summarizes a drawndown history for a return series (typically a monthly or quarterly return stream representing an index, a portfolio, a security, etc.). The value is "1" whenever there is a new market peak. Whenever the trailing return history is negative it will be some value less than 1. It can never exceed one, and only go below zero in the case of leverage.

This is a recursive function, but does not call any other custom functions.

Note: "Type" is a boolean number: 1 = Max, 0 = Current. The latter is the assumed type if no value is provided. This determines what kind of drawdown result is provided, i.e the largest drawndown over the entire history (even if the current drawdown is zero), or just the current drawdawn. IMPORTANT!! The return stream MUST be provided in the ReturnArray parameter in CHRONOLOGICAL ORDER. I accomplish this by sorting the relationship based on the chronological date of each return entry, but there are other methods. Also, this assumes that numbers are entered in percentage and NOT decimal terms. I.e. 0.98% versus 0.98.

I call this function in the following let statement:

Let ( [

_IncomingReturnArray = List ( DATA_PerformanceHistory::PerformanceValue ) ;

// the relationship connecting to this separate TO is sor
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 21 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: