SOLVED

New Contributor

# Excel - Finding a payment to get to specific IRR

Is there a way to determine the next positive cashflow on a specified date that would return a particular IRR for that series.

I have to limit a cashflow to the amount that would return a 17% IRR in a series. I know when the IRR gets to above 17% based on a particular cashflow amount, but I need to know the amount within that cashflow that gets to 17% IRR. See my example below.

 IRR 1/1/2021 -550 3/31/2021 1 0.00% 6/30/2021 9 -99.98% 9/30/2021 9 -99.44% 12/31/2021 14 -96.46% 3/31/2022 5 -94.45% 6/30/2022 14 -87.33% 9/30/2022 14 -80.13% 12/31/2022 13 -73.33% 3/31/2023 157 -34.73% 6/30/2023 162 -13.76% 9/30/2023 162 0.78% 12/31/2023 161 11.62% 3/31/2024 154 19.68% 6/30/2024 160 26.34%
4 Replies

# Re: Excel - Finding a payment to get to specific IRR

=INDEX(B1:B15,MATCH(TRUE,D1:D15>17,0))

Maybe with this formula as in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.

# Re: Excel - Finding a payment to get to specific IRR

Thank you for the response but this only tells me which cashflow amount gets me to a 17% or above IRR. I need to know what is the exact amount of that cashflow that will return exactly 17% IRR. IN my example the 154 on 3/31/2024 will get me above 17%. I need to be able to calculate the exact amount of that cashflow that puts that IRR at 17%. I can estimate it to be 100 but I need to have this calculate not me estimate by trial and error.
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Excel - Finding a payment to get to specific IRR

If the goal IRR is positive (greater than zero), we can use XNPV as follows:

=-XNPV(D1, B2:B14, A2:A14) * (1+D1)^((A15-A2)/365)

More generally, we can use SUMPRODUCT as follows for non-positive as well as positive IRRs:

=-SUMPRODUCT(B2:B14 / (1+D1)^((A2:A14-A2)/365)) * (1+D1)^((A15-A2)/365)

where D1 contains the goal IRR (e.g. 17%).

(We cannot use XNPV with a non-positive goal IRR.  It is a defect, IMHO.)

Caveat: Although the cash flow in B15 will be accurate for the goal IRR, Excel XIRR might not return exactly the goal IRR because of limitations in its iterative algorithm.  In this example, XIRR returns 16.9999998807907% (17% - 1.19E-09).  But empirically, we find that the next closest XIRR return is 17.0000010728836%
(17% + 1.07E-08), which is not as close to 17%.

# Re: Excel - Finding a payment to get to specific IRR

Thank you. That worked perfectly!@Joe User