Solved

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

Posted on 2013-11-25
2
352 Views
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!
XIRR-Algebra-Problem.xlsx
0
Comment
Question by:rescapacctgit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 495 total points
ID: 39676620
The last payment is given by the following formula:
=-XNPV(F21,C9:C20,B9:B20)*(1+F21)^((B21-B9)/365)

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.
0
 

Author Closing Comment

by:rescapacctgit
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!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Named Range 31 46
PBI - need to format a new column with formula 6 14
Checking references in VBA 3 24
Hash on Excel 13 41
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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