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

Posted on 2013-11-25
Medium Priority
Last Modified: 2013-11-26
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.

Thanks in advance!
Question by:rescapacctgit
LVL 81

Accepted Solution

byundt earned 1980 total points
ID: 39676620
The last payment is given by the following formula:

This formula uses the cell addresses in your posted workbook:
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.

Author Closing Comment

ID: 39678966
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!

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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.

Join & Write a Comment

This is an article on how to answer questions, earn points and become an expert.
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question