# Can we use a simple Excel formula to get the same results as the VBA macro?

1. ## Can we use a simple Excel formula to get the same results as the VBA macro?

Dear all,

There is a macro to calculate the maximum drawdown for a stock during a time period:

http://investexcel.net/2450/maximum-drawdown-vba/

Can I ask if we do the same thing using a simple Excel formula, rather than having to use macro?

2. ## Re: Can we use a simple Excel formula to get the same results as the VBA macro?

It will be very helpful if there is a simple Excel formula for this, rather than having to use VBA. Any great idea?

3. ## Re: Can we use a simple Excel formula to get the same results as the VBA macro?

You would need to analyze the logic of the function to see what it is doing.

I'm not a financials guy, so take my analysis for what it is worth. It appears that the function calculates every possible combination of x(j)/x(i)-1 (% change) where j>=i and returns the minimum value from these calculations.

I don't see a simple, single cell formula that can replicate this calculation. I'm not very good with the complex, single cell array functions that a lot of people like to use. I expect this could be fairly simple if you are willing to use a helper column.

I haven't rigorously tested the math, but it seems like, the minimum %change relative to each point will be at the minimum data point after that point. So, assuming data is in A2:A20 (comma delimited):
``Please Login or Register  to view this content.``
Then use the =MIN() function on column C.

4. ## Re: Can we use a simple Excel formula to get the same results as the VBA macro?

Thanks, MrShorty. I am now convinced that there will not be a single formula to handle this. I'll use multiple separate steps to calculate it

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

#### 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