Solved

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

Posted on 2013-11-25
2
336 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
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

786 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