Help with Nested IF Then statement?
 

Hello. I am not sure how to model/structure an IF THEN statement in excel. Or if the result I am looking for requires a nested statement or not.

Basically, I have an excel cash flow problem. A certain partner is entitled to a performance fee if a certain threshhold is achieved:

PROBLEM SET-UP: If the annual cash flow is greater than $12.5 million, then Partner A gets 10% of the amount ABOVE this $12.5 million threshold. HOWEVER, this 10% performance fee is capped at 1% of total project revenue in any given year.

ROW 1 is the annual cash flow amount: $10 million in Year 1, $14 million in Year 2, etc.
ROW 2 is the annual cash flow threshold of the $12.5 million: This remains fixed.

So in year 1 there is not performance fee paid out, but in year 2 there is $150,000 fee paid out to Partner A. (10% of the $1.5 million)

ROW 3 is the total annual cap based on 1% of total revenue: Year 1: $110,000, Year 2: $200,000, etc.

So I have an IF statement saying, IF(ROW 1 is greater than ROW 2, then multiply 10% times the difference, otherwise ZERO)

But how do I model in the condition that IF ROW 3 exceeds a certain amount this is the amount the forumla should yield? Is this an ELSE statment or OR or AND???

How might this look? Thank you.

Help with Nested IF Then statement?
 

excel_novice12 wrote:

Hello. I am not sure how to model/structure an IF THEN statement in
excel. Or if the result I am looking for requires a nested statement or
not.

Basically, I have an excel cash flow problem. A certain partner is
entitled to a performance fee if a certain threshhold is achieved:

PROBLEM SET-UP: If the annual cash flow is greater than $12.5 million,
then Partner A gets 10% of the amount ABOVE this $12.5 million
threshold. HOWEVER, this 10% performance fee is capped at 1% of total
project revenue in any given year.

ROW 1 is the annual cash flow amount: $10 million in Year 1, $14
million in Year 2, etc.
ROW 2 is the annual cash flow threshold of the $12.5 million: This
remains fixed.

So in year 1 there is not performance fee paid out, but in year 2 there
is $150,000 fee paid out to Partner A. (10% of the $1.5 million)

ROW 3 is the total annual cap based on 1% of total revenue: Year 1:
$110,000, Year 2: $200,000, etc.

So I have an IF statement saying, IF(ROW 1 is greater than ROW 2, then
multiply 10% times the difference, otherwise ZERO)

But how do I model in the condition that IF ROW 3 exceeds a certain
amount this is the amount the forumla should yield? Is this an ELSE
statment or OR or AND???

How might this look? Thank you.

Like this:
=IF(A1A2,IF(((A1-A2)*0.1)A3,A3,(A1-A2)*0.1),0)
....where A1 is the cash flow, A2 is the threshold, and A3 is the cap.

Put that somewhere in column A of the "performance fee" row (A4 or
wherever) and then copy it across. (If the first column isn't A, adjust as
necessary *before* copying.)

The "else" of an IF is simply whatever's after the second comma:
IF(test,action if true,action if false)

(Excel has both AND and OR, but in this specific case, neither is needed.)

--
I guess the only thing we have in common is our love of boobs.

Help with Nested IF Then statement?
 

Spencer101 wrote:

I'm not sure I understand the maths behind your question as if the
revenue for year 2 is $14m I do not see how 1% of that is $200k.

Revenue and cash flow aren't the same thing.
http://en.wikipedia.org/wiki/Cash_flow
http://en.wikipedia.org/wiki/Revenue

--
Had this been an actual emergency, we would have fled in terror,
and you would not have been informed.