Is there a formula that finds the final cash flow in a series that results in a specific XIRR??
Is there a formula that finds the final cash flow in a series that results in a specific XIRR??
Use the GoalSeek function for this which you find in the dropdown that opens from the What-If Analysis menu on teh ribbon's Data tab.
In your XIRR table add a date for the target to be calculated and a random value (or nothing) for the final cash flow to be calculated. You must also enter the XIRR() function to include both the target date and the value to be calculated.
In my test the value to be calculated was in F9 and the XIRR formula in G9. In the GoalSeek dialog box I entered "Set Cell: G9" > "To value: [0.5]" > "By changing cell: F9". Press OK to execute the calculation and read the "final" cash flow in F9.