Reverse XIRR Calculation/Formula


Is there a formula that finds the final cash flow in a series that results in a specific XIRR??



How do you define the "final" XIRR? Or, put differently, how does the "final" XIRR differ from XIRR? Do you wish to extract the cash flow associated with the last date in an unsorted list of cash flows?
Variatus (rep: 933) Jan 16, '18 at 8:07 pm
Let me clarify.  I'm trying to back into what the final payment needs to be in a series of cash flows to achieve a specific XIRR. 
IE: I know how much payments 1-4 are, but I need to find out how much I need in paymnet 5 to reach a 10% XIRR.
Joe Blow (rep: 2) Jan 17, '18 at 10:42 am
Add to Discussion


Selected Answer

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.


Answer the Question

You must create an account to use the forum. Create an Account or Login