# 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?   Register To Reply

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?  Register To Reply

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.  Register To Reply

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   Register To Reply