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

# Reverse XIRR Calculation/Formula

0

### Discussion

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: 1735) 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.

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

## Answers

0

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.