Reverse IRR creates non-logical circular reference answer / #NUM result

I would guess that a successive approximations algorithm does not converge for this problem. I would try more algebra.

To expand on the algebra -- which should be fairly easy with only 1 value:

A1=NPV(10%,B2)=B2/((1+0.1)^1) From the formula given in the help file for NPV(). Substitute expression for B2 into this:
A1=(1000-C3)/(1.1^1)=(1000-C3)/1.1 Substitute expression for C3 into this:
A1=(1000-A1*(1/lev-1))/1.1 lev=leverage. Even though A1 is on both sides of the equation, a little algebra should allow us to solve for A1. If you need a refresher on the algebra, I might suggest something like: http://www.purplemath.com/modules/solvelin3.htm

Can you solve it from there?

Originally Posted by hartmanjameson

4 Assumptions -
[....]
years: 3 years

Math:
A1 = NPV(10%,B2)

First, if the investment is over 3 years, the formula in A1 should be =NPV(10%,0,0,B2).

When C3 is 429, that does yield 429 in A1.

But it would be more flexible to use the formula =PV(10%,3,0,-B2).

Then it is easy for 3 to be a variable (cell reference), as well as 10%.

For your design, I think the following formulas do what you want:

A1: =IF(B5=0, 0, B6 / ((1+B7)^B8 + 1/B5 - 1))
B2: =B6 - C3
C3: =IF(B5=0, B6, A1 * (1/B5 - 1))

where:
B5, leverage: 50% or 60%
B6, FV: 1000
B7, IRR: 10%
B8, years: 3

The formula makes sense when B5 (leverage) is between 0% and 100%.

-----

Second, however, I think you have the definition of A1 and C3 reversed.

When B5 (leverage) is 60%, A1 (equity) becomes 60% of the total (A1+C3).

To me, "60% leveraged" means that C3 (debt) is 60%, not equity.

If you agree, either repurpose A1 (debt) and C3 (equity) or exchange the formulas.

ERRATA.... The last sentence is wrong. I was just looking at it arithmetically. But logically, neither suggestion makes sense, since you want A1 to be the PV of the portion of the FV of the investment that is equity. If you agree with my interpretation of "60% leveraged", the formulas in A1 and C3 must be revised. I have not given any thought about how. (Honestly, I do not understand your definition of debt. It appears to be a future value.)