Formula/macro that determines required sale price to satisfy input IRR

Hey guys and gals,

I am trying to figure our a dynamic formula/macro that will allow me to calculated an asset sale price, given fixed annual cash flows, that achieve an input IRR (on investment equity) given a certain timeframe.

A little more detail:
-Investment horizon is 5 years
-Each year has fixed distributions to investment equity
-Year 5 I am modeling a sale. I am trying to figure our what profit distributions (from sale) would need to look like in order to get certain investor's to a specific IRR.

So all in all, I want to know what the total number in year 5 (give annual distributions Year 1-4) should be to achieve a certain IRR. I can easily do this with solver, but I was wondering if there was a way I could automate this process since I am changing the IRR constant fairly frequently. Please see the attachment if my description is confusing.


Thanks for taking a look!
IRR Problem.xlsx

LOOK AT THE FORMULAS FROM THE TABLE BELOW THE GIVEN EXAMPLE IN THE LAST YEAR.

1) It establishes the NPV of the previous years
2) It pushes forward the negative result to the desired year (in this case year 5)

Thank you!

Any time!!!!