Forum Discussion
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% |
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%.
4 Replies
=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.
- 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.
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%.
