Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Reverse XIRR Calculation/Formula

0

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

Answer
Discuss

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: 4889) 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

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.

Discuss


Answer the Question

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