Help with Nested IF Then statement?

[excel_novice12]

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

Basically, I have an excel cash flow problem. A certain partner i
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 millio
threshold. HOWEVER, this 10% performance fee is capped at 1% of tota
project revenue in any given year.

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

So in year 1 there is not performance fee paid out, but in year 2 ther
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, the
multiply 10% times the difference, otherwise ZERO)

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

How might this look? Thank you

 

[Auric__]

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(A1>A2,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.)

 

[Spencer101]

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

Basically, I have an excel cash flow problem. A certain partner i
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 millio
threshold. HOWEVER, this 10% performance fee is capped at 1% of tota
project revenue in any given year.

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

So in year 1 there is not performance fee paid out, but in year 2 ther
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, the
multiply 10% times the difference, otherwise ZERO)

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

How might this look? Thank you.

Hi,

Perhaps an example workbook might be useful.
I'm not sure I understand the maths behind your question as if th
revenue for year 2 is $14m I do not see how 1% of that is $200k.

Maybe I'm just overlooking something... maybe there's informatio
missing from the above. But either way, an example workbook would b
very helpful.

S

 

[Auric__]

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