Hello!

I am trying to get excel to output an amount which will subsequently affect itself (circularity)

Simple example would be the following investment IRR -

4 Assumptions -
desired IRR: 10%
sale price: $1000
leverage: 50% (.5)
years: 3 years

Math:

A1 = NPV(10%,B2)
B2 = ($1000-C3)
C3 = A1(1/.5-1)

Total Investment = $858
Initial Debt (C3) = $429
Initial Equity (A1) = $429

To phrase, "If I invest $429 today with 50% total leverage and sell the investment for $1000 (less: $429 of debt) I will have received 10% annual return.


The issue is when I increase leverage to ~60%. Values become illogical, Debt becomes a negative value and extremely large. I can calculate the right answer by hand but it is tedious and I also want to introduce an interest cost. I have no idea why the values returned make no sense even though at 50% leverage the formulas were working.

Any advice would be great!