How do I find the residual for a fixed IRR?

July 23, 2012 4:12 PM

How do l find the residual sales value that will generate a given IRR when the investment and cash flows are fixed?

I am working on an excel model for an investor proposal. The sales people want to offer 100% of the residual value of an asset to the investor up to an IRR of 15%. Above 15% they want to split the residual value 50%/50% with the investor. They feel this makes the investor confident that the sales group has the proper incentive to sell as high as possible.

The investment amount in my model is fixed. The cash flows are fixed. The model is currently kicking out a 18.07% return. I want a formula that will subtract from the residual sales proceeds until the IRR is 15%. The remaining sales proceeds will be split 50/50.

It is actually a touch more complicated, as 10%, 15%, and 20% percent are respective hurdles. But if I can figure out how to manipulate the residual to generate a specified IRR, I will be set.

Thank you!

posted by jefficator to Work & Money (3 answers total)

Do you need something more dynamic than the Goal Seek tool? That's the easiest way to do it.
posted by mullacc at 4:58 PM on July 23, 2012

Response by poster: What is that?
posted by jefficator at 5:06 PM on July 23, 2012

Its a tool under the data pulldown menu. It basically iterates a variable to find a solution. Its the easiest way to do this. You would basically tell it to change your residual value until the IRR reaches the goal you've chosen.
posted by JPD at 5:40 PM on July 23, 2012