Determine the Proceeds Required to Achieve a Specific IRR

Q: “I have a case study interview next Monday; I will be asked to calculate the cash flows necessary to meet a certain IRR hurdle rate. Can you please provide me with instructions as to how to do so – cant seem to find a succinct answer online!”

A: The math is fortunately pretty straightforward for this calculation. I believe what throws people off is that an IRR is often explained as the rate that makes the net present value of all cash flows equal to zero. On account of this definition people think about working backwards from the cash flows received. But it can also be thought of as the annualized effective compounded return rate. This language makes it easier to think about growing an investment (cash outflow) by an annual rate of return to achieve an outcome. 

In other words, if you are provided an IRR of 20% and asked to determine the proceeds achieved in year 5, the result is simple: Your investment will grow by 20% for 5 years.

(1 + 20%)(1 + 20%)(1 + 20%)(1 + 20%)(1 + 20%)

or…

(1 + 20%)^5

This works out to 2.49. So you can take the dollars invested, and multiply by 2.49 to determine the proceeds required to achieve an IRR of 20% over 5 years.

Assuming you invested X dollars, the proceeds required to achieve an IRR of 20% over 5 years is as follows: 

X*(1 + 20%)^5

Please see video for additional detail:

In Excel, you can also use the =YEARFRAC() function to automatically adjust this calculation for different dates. The formula is as follows:
 
=X*(1 + IRR)^YEARFRAC(date1,date2)
 
Where X is the investment made, IRR is the desired IRR, “date1” is the investment date and “date2” is the exit date.
 
Note: Keep in mind all of this is relevant when you assume that all proceeds are realized in the exit year. It is most relevant to an LBO scenario assuming no dividends. 
 
This video also includes a calculation to permit showing multiple IRRs along the same row (another question I receive frequently). This only works if you have one cash outflow (the investment) and one cash inflow (all proceeds received in the same period). 
 
I will upload the calculation mentioned in the video momentarily.
 
The Excel template referenced in the video is available HERE.
 
IRR Formula Made Simple: