# Excel XIRR: What withdrawal do I need to get a desired XIRR

So .... here is what I am trying to do ... or rather ... what I've been asked to do.

Given a random cash flow over a period of time, I need to figure out what final withdrawal amount would yield a particular XIRR.

See attached for an example .... I don't even know if this is possible .... but even if it isn't .... I'm going to have to come up with something.

XIRR-Algebra-Problem.xlsx
###### Who is Participating?

Commented:
The last payment is given by the following formula:
=-XNPV(F21,C9:C20,B9:B20)*(1+F21)^((B21-B9)/365)

F21   = 9% annual interest rate
C9:C20 are payments
B9:B20 are dates of those payments
B21 is the date of the last payment

If you look at the Help for the XIRR function, you will see a mathematical equation just above the example. I backsolved this equation for the last payment P_N (P subscript N). The result is the Excel formula you see above.
0

Author Commented:
Thank you - this absolutely works and it is quite clever.

I'm still trying to completely understand it but so far .... all of our real-life scenarios are working.

You saved Thanksgiving at my home this year!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.