Forum Discussion
Excel - Finding a payment to get to specific IRR
- Jan 18, 2022
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%.
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%.
- SSIMPSON505Jan 18, 2022Copper Contributor
Thank you. That worked perfectly!JoeUser2004