nested if function problem

Trying to model the following but im not plugging in the right nested if statement. Please help. Would even be better if I can develop this model dynamically.

Here is my problem> modeling financial debt recoupment by investors and borrowers (revenue versus cost)

x>0<=5,000,000 (x=revenue)

y=2,000,000 ( y=cost)

A=x B=x C=x D=investor E=borrower

If A>y up to 110% of y but not less than 90% of y, then A returns 90% of A to D and 10% of A to E

If B>y up to 150% BUT not less than 110% of y, then B returns 50% of B to D and 50% of B to E

If C>y up to 200% but not less than 150% of y, then C returns 30% of C to D and 70% of C to E

Thanks!

Im also trying to build it such that the rates can be changed without having to work directly in the cells.

Help would be appreciated.

will do Carim

The attached file has it done two ways.

Option 1:
Assumes you actually wanted all 3 columns separately listed. (You'll probably want to adjust the <= to < in some instances so there is no overlap in the ranges).... That said, I don't recommend this way. (Actually, I think this particular one is probably just a bad interpretation of what you wanted - but I left it in anyway.)

Option 2:
You probably want it this way, with the ranges independent of each other.

Scott

Thanks,

I couldn't attach the zip file Carim but I'll get to it from here. Thanks Maistrye! How can I change the rates without working in the cells...if you have any idea how i could make this dynamic I would be grateful.

The setup isn't too bad, just need to know some things.

Was it Option 2 that was closer to what you wanted?

What values stay the same for each line, and which values would change for each line? Do the x, y values change for each?

Do only the percentages to D/E change, with the ranges remaining constant? Vice versa? Both change?

Do the number of rates change (ie. can one line have 3, another 4, etc)?

What should be the breakdown if the value is below the lowest or above the highest rate? (ie. if an investment returns, say, 250%, or as little as 10%)

What happens on the borders of a range (ie. at 110%), does it go (using your example) to 90/10 or 50/50 breakdown? (I assumed it went to the 50/50, but that might be a bad assumption.)

Scott

yes option 2 was closer...what I really needed

the y value (the cost) or 2 million did not change only the x values...how much revenue was generated

Both the D/E percentages and the upper lower range of percentages may change (does this change the model you constructed)? I wanted to construct something dynamic so that if D/E became 60/40 or 40/60 the amount of recoupment given to investors and borrowers also changes

the number of rates won't change...their are only three recoupment levels

if the rate of return is lower than 90% say 10% as you suggest....all the money would flow to the investors(can we add this to the model)...i have used your example to model from 150% up to 1000% return, meaning that past a recoupment of 150% the investors would receive 30 cents on the dollar in perpetuity


anything up to 110% reverts to 90/10 anything above that say for instance 1.11, than it reverts to 50/50......was your last question an assumption that was included?