Jan 17 2022 12:16 PM
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% |
Jan 17 2022 12:35 PM
=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.
Jan 17 2022 12:52 PM
Jan 17 2022 08:03 PM - edited Jan 18 2022 07:09 AM
Solution
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%.
Jan 18 2022 08:15 AM
Thank you. That worked perfectly!@Joe User
Jan 17 2022 08:03 PM - edited Jan 18 2022 07:09 AM
Solution
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%.