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.

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.

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.