Solved

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

Posted on 2013-11-25
316 Views
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
0
Question by:rescapacctgit

LVL 80

Accepted Solution

byundt earned 495 total points
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 Closing Comment

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

## Featured Post

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Outlook Free & Paid Tools