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