Forum Discussion

Copper Contributor

Jan 17, 2022

Solved

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...

excel

Formulas and Functions

JoeUser2004
Jan 18, 2022
SSIMPSON505

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%.

SSIMPSON505

Copper Contributor

Jan 17, 2022

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.

JoeUser2004

Bronze Contributor

Jan 18, 2022

SSIMPSON505

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%.

SSIMPSON505
Copper Contributor
Jan 18, 2022
Thank you. That worked perfectly!JoeUser2004