Multiple IRR with Goal Seek - May not have a solution

[etmzkn]

Hi,

I have a problem and can not figure out that if there is a solution (mathematically possible) or not. I tried to demonstrate the problem below. I have tried so many things (goal seek, solver or analytical approaches) but could not find a solution. Now I am thinking that there is no solution for this problem.

Here is the issue;

1. There are two different companies, say Comp. A and Comp. B.
2. These companies will make an investment together and their shares are 40% and 60% respectively.
3. Total investment amount is 100$.
4. Payments from companies for investment will be made in year 0.
5. The investment will generate a fixed revenue (in the example fixed revenue named as "a") for 3 years and this revenue will be shared by companies based on their shares in the investment. Since Comp. A will exit in this partnership at the end of Year 2, 40% - 60% for first 2 years and 0% - 100% for Year 3 respectively).
6. These companies have different return expectations which results in different IRRs (internal rate of return).
7. The payment will be derived from their IRR expectations.

I am trying to find an "a" value that satisfies above mentioned conditions and assumptions but lost my hopes. There may not be a valid solution for "a" value.

Thanks in advance.


[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Fixed revenue[/TD]
[TD]a$[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Year 0[/TD]
[TD="align: right"]Year 1[/TD]
[TD="align: right"]Year 2[/TD]
[TD="align: right"]Year 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Cash flows[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-100$[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Exit in...[/TD]
[TD]Return expectation (IRR)[/TD]
[TD]Share in investment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comp. A[/TD]
[TD]Year 2[/TD]
[TD]10%[/TD]
[TD]40%[/TD]
[TD][/TD]
[TD]-40$[/TD]
[TD="align: right"]a x 40%[/TD]
[TD="align: right"]a x 40%[/TD]
[TD="align: right"]-[/TD]
[/TR]
[TR]
[TD]Comp. B[/TD]
[TD]Year 3[/TD]
[TD]12%[/TD]
[TD]60%[/TD]
[TD][/TD]
[TD]-60$[/TD]
[TD="align: right"]a x 60%[/TD]
[TD="align: right"]a x 60%[/TD]
[TD="align: right"]a[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

If a is fixed for all three years to meet Comp B's requirements, the Comp A's is fixed at whatever is the result. You have two conditions and one degree of freedom.

 

[joeu2004]

I am trying to find an "a" value that satisfies above mentioned conditions and assumptions but lost my hopes. There may not be a valid solution for "a" value.
[....][TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Fixed revenue
[/TD]
[TD]a$
[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Year 0
[/TD]
[TD="align: right"]Year 1
[/TD]
[TD="align: right"]Year 2
[/TD]
[TD="align: right"]Year 3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Cash flows
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-100$
[/TD]
[TD="align: right"]a
[/TD]
[TD="align: right"]a
[/TD]
[TD="align: right"]a
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Exit in...
[/TD]
[TD]Return expectation (IRR)
[/TD]
[TD]Share in investment
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comp. A
[/TD]
[TD]Year 2
[/TD]
[TD]10%
[/TD]
[TD]40%
[/TD]
[TD][/TD]
[TD]-40$
[/TD]
[TD="align: right"]a x 40%
[/TD]
[TD="align: right"]a x 40%
[/TD]
[TD="align: right"]-
[/TD]
[/TR]
[TR]
[TD]Comp. B
[/TD]
[TD]Year 3
[/TD]
[TD]12%
[/TD]
[TD]60%
[/TD]
[TD][/TD]
[TD]-60$
[/TD]
[TD="align: right"]a x 60%
[/TD]
[TD="align: right"]a x 60%
[/TD]
[TD="align: right"]a
[/TD]
[/TR]
</tbody>[/TABLE]

I think we can prove that no such "a" exists, assuming that your table is a correct model for the problem that you want to solve. (I have not vetted it.)

For Company A, "a" is =PMT(10%,2,-40)/40% . That is about 57.6190476190476. Suppose that calculation is in A1.

(Note that =NPV(10%, A1*40%, A1*40%)-40 is zero.)

For Company B, =NPV(12%, A1*60%, A1*60%, A1)-60 should be zero. Instead, it is about 39.4395781271692.

(Even if "a" in Year 3 should also be a*60%, the NPV is not zero.)

However, we can use Goal Seek to determine an IRR for Company B such that its NPV is zero, given the "a" calculated for Company A.

(And if I am correct that Year 3 should also be a*60%, the IRR for Company B is =RATE(3,A1*60%,-60).)

 

[etmzkn]

If a is fixed for all three years to meet Comp B's requirements, the Comp A's is fixed at whatever is the result. You have two conditions and one degree of freedom.

As far as I understand (sorry for poor math) you say there is no solution for this equation. Am I right?

I think we can prove that no such "a" exists, assuming that your table is a correct model for the problem that you want to solve. (I have not vetted it.)

For Company A, "a" is =PMT(10%,2,-40)/40% . That is about 57.6190476190476. Suppose that calculation is in A1.

(Note that =NPV(10%, A1*40%, A1*40%)-40 is zero.)

For Company B, =NPV(12%, A1*60%, A1*60%, A1)-60 should be zero. Instead, it is about 39.4395781271692.

(Even if "a" in Year 3 should also be a*60%, the NPV is not zero.)

However, we can use Goal Seek to determine an IRR for Company B such that its NPV is zero, given the "a" calculated for Company A.

(And if I am correct that Year 3 should also be a*60%, the IRR for Company B is =RATE(3,A1*60%,-60).)

And also you say that there should be only one determinant for this problem (in your solution Comp. A's IRR is satisfied but Comp. B's IRR changes due to this condition) and there is no solution for both conditions satisfied. Did I get it correct?

Thank you for your replies.

 

[joeu2004]

you say that there should be only one determinant for this problem (in your solution Comp. A's IRR is satisfied but Comp. B's IRR changes due to this condition) and there is no solution for both conditions satisfied. Did I get it correct?

Yes, that is what I demonstrated.

 

[shg]

you say there is no solution for this equation. Am I right?

Yes. It's equivalent to trying to fit a line of the form y=m*x to pass through two arbitrary points.