IF / AND / OR Formula

[Hankles]

Hi,

I am trying to ascertain the number of days it would take the Firm to exit each stock held across two strategies (each with a number of portfolios), given the percentage weight holding in each of the portfolios in the strategy, the value of each portfolio in dollars, the average daily $ traded and a percentage of daily "participation" of that average daily value - effectively a limit that the firm would trade on any given day to avoid moving the prices (e.g. 30% of average daily value traded).

I have a formula that calculates the number of days to liquidate each holding (see below), but i want to amend the formula to prioritise the firms available liquidity (i.e. 30% of the average daily value traded in any one stock) to one of the strategies and then allocate the remaining liquidity to the other strategy if necessary, returning the number of days to liquidate 100% of each position in each strategy.

I am using the formula below to get calculate the number of days to liquidate each stock.

=IF(SUMPRODUCT($E$6:$H$6,E7:H7)/ (D7*$N$6)=0, 0, SUMPRODUCT($E$6:$H$6,E7:H7)/ (D7*$N$6))

where;
E6:H6 is the $ value of each portfolio in Strategy 1
E7:H7 is the %age weight of each holding in each portfolio for Strategy 1
D7 is the average daily value traded in each stock
N6 is the firm limit for participation (e.g. 30% of average daily value traded)

So, if strategy 1 holds more that 30% of the average daily value traded then apply 100% of the firms 30% of average daily limit to that strategy until it has sold out of 100% of that holding, then apply 100% of that limit to Strategy 2. Likewise if Strategy 1 doesn't have any exposure to a particular stock but Strategy 2 does then apply 100% of the firm's limit to Strategy 2 ....

Does anyone have any ideas or suggestions here? I tend to get a bit muddled around the syntax of these formulas and it’s likely to be quite convoluted …

Thanks in advance !

 

[Joe4]

Re: IF / AND / OR Formula help please....

Welcome to the Board!

Firstly, your formula seems unnecessarily redundant to me. It looks like it is saying if the result of some calculation is zero, return zero, otherwise return the calculation.
If that is the case, you don't need an IF statement at all, just the calculation, i.e.

[COLOR=#333333]=SUMPRODUCT($E$6:$H$6,E7:H7)/ (D7*$N$6)[/COLOR]

Regarding your question, it looks a bit involved and confusing. It might help if you can post some examples of your data and expected results.
You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.