Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drawdown Analysis
I have 3 columns of data: Date (MM-YY), % return for the
month, cumulative % return since 1st date (eg. cul return in May-02, 1.02809 = 1.01436*(1+1.35%) Pls refer to the following: Date % Month % Cul Start 1.00000 Apr-02 1.44% 1.01436 May-02 1.35% 1.02809 Jun-02 0.23% 1.03043 Jul-02 -0.65% 1.02370 Aug-02 -0.10% 1.02268 Sep-02 -0.30% 1.01966 Oct-02 0.23% 1.02197 Nov-02 0.19% 1.02396 Dec-02 1.65% 1.04085 Jan-03 0.57% 1.04679 Feb-03 0.00% 1.04679 Mar-03 -0.29% 1.04371 Apr-03 2.27% 1.06740 I want to automate the spreadsheet to yield the following: 1. Identify start date of the month that turned from +ve to -ve (eg. Jul-02 and Mar-03) 2. Cumulative -ve % return starting that date (eg. for Jul-02 its -1.05 [1.01966/1.3043-1] and Mar-03 its -0.29) 3. Identify the date when cul return recovers to the level before the drawdown (eg. for Jul-02's drawdown, cul return only recuperated losses in Dec-02, ie. first date when cul return is cul return right before the drawdown) 4. No. of months from the start until the end of the drawdown (eg. from Jul-02 to Dec-02, it was 6 months; for Mar-03, it was 1 month) 5. Auto sort the above results (perhaps a named range) by cul -ve return, in descending order. I imagine I should create 4 separate columns to hold each of the results from 1-4. Pls let me know if you need further clarifications or I can email whoever a spreadsheet to illustrate. Thank you!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drawdown Analysis
What happened to my first response??????
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Analysis ToolPak Regression Analysis Help Requested | Excel Worksheet Functions | |||
Analysis Toolpak-Confidence Level and data analysis questions | Excel Worksheet Functions | |||
Date drawdown questions | Excel Discussion (Misc queries) | |||
Maximum Drawdown Function | Excel Worksheet Functions | |||
drawdown | Excel Worksheet Functions |