Need to Reverse XIRR?

[NewWorldRonin]

Hello,
I've seen similar problems and related (solved) solutions so I don't think what I'm requesting is impossible, but here goes...

In the attached you will an XIRR calculated in cell A1 that pivots off of row 1 and row 36.

I would like to instead change A1 to a desired XIRR outcome and have row 36 then adjust and become the free cash flows needed to produce this outcome. Knowing that these cash flows could literally be just about anything with thousands of different permutations, I would like to take the cash flows and distribute them proportionately across months based on the original proportions of the "Base Case" as shown in row 38.

Any help is appreciated.

Attached Files

 

[TheVillageIdiot]

If you want the cash flows to be proportional to the relative scale from row 38, how would that work? Are the percentages in row 38 the relative amounts from the calculations in row 36?

If you scale the cash flows in the same proportion, the resulting XIRR is the same. Are you trying to smooth the cash flows that get to a desired return?

I may be misreading the request, but the easiest way to get to the target is to change the last value until the XIRR calculation matches the desired return. I haven't tried this, but there should be a closed form solution if you change the last value (GoalSeek might help). Likely have to unannualize the XIRR result and then annualize after you get the values to match.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.