Max IRR based on Exit Year

**SilverBullet17** · 03-07-2017, 07:52 AM

Hi Guys

I am currently trying to find a built in excel formula/function (not very experienced with Macro's just yet) which will allow me to find out the year in which I am able to maximise my Levered IRR (Cell C34) based on the exit year input assumption (orange highlighted cell C12). Basically, I am looking for a way of finding this without having to go into Cell C12 and manually click Year 4 or 5 or 6 etc. to find out which of the years provide the highest IRR. Perhaps some "IF" combination including the "LARGE" function as well?

Any help or suggestions on this issue and/or the model itself would be greatly appreciated!

Regards

SilverBullet

**SilverBullet17** · 03-08-2017, 07:32 AM

Bump - any help here please?

**MrShorty** · 03-08-2017, 12:57 PM

I don't use these tools in my own work, but I could see Scenario Manager or a Data Table (under What if Analysis tools) being used for something like this.

Scenario manager: Create each scenario (3, 4, 5, 6, 7 years), then run the summary report for the scenario manager and see which scenario has the highest IRR. http://www.techrepublic.com/article/...analysis-tool/
One variable data table: http://www.excel-easy.com/examples/data-tables.html

At this point, I would probably lean towards the data table analysis.

**SilverBullet17** · 03-10-2017, 07:16 AM

Awesome thanks the Data Table rec. was great (its a lot easier to set up than I initially thought) - any chance you work with solver? I would like to use solver to max out the IRR?

**MrShorty** · 03-10-2017, 11:42 AM

I use Solver frequently. In this case, when I tried Solver, it gave me errors whenever Solver would try non-integer values (even with a criteria of C12=int). With only three possible input values, a brute force try all three algorithm (instead of the more sophisticated algorithms that Solver uses) seemed much more effective.