+ Reply to Thread
Results 1 to 5 of 5

Reverse irr w/ irregular cash flows

  1. #1
    Registered User
    Join Date
    07-01-2013
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    43

    Lightbulb Reverse irr w/ irregular cash flows

    Warning: This may not have a solution.

    Goal is to determine what PAYMENT would deliver a set IRR given a series of irregular cash flows.

    Example:

    Initial investment is -100.
    Payment 1 thru 5 = 3, 4, 2, 1,7
    These payments are irregular as are all future payments.

    The goal is to determine (for each Payment date [1, 2, 3, 4, 5...]) what value would deliver a set IRR of 10%.

    So at payment 5, instead of 7, what would payment need to be to deliver a 10% IRR.

    This has been attempted for predictable cash flows but not irregular cash flows.

    Thanks.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    Re: Reverse irr w/ irregular cash flows

    After 12 hours, I was hoping one of our more financially literate members would have chimed in. Are you familiar with all of Excel's built in financial functions (https://support.office.com/en-us/art...rs=en-US&ad=US )? I am aware that they exist, but I am not knowledgeable in the details of when each function is appropriate -- in particular (as it applies to this problem) understanding the differences between the PV, NPV, and XNPV (and related) functions.

    My first guess, from someone outside of financials, would be to use the XIRR() function to compute the irr of the cash flows. Then use Solver (or goal seek, though I think Solver will be more robust) to set the XIRR() to 0.1 by changing whichever cash flow you want. Would that work, or is there something I am missing?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    07-01-2013
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Reverse irr w/ irregular cash flows

    Thnx for reply.

    The ideal output is a dynamic column that changes as cashflows change AND which shows the # to achieve 10% IRR on a monthly basis.

    As such, the solver/goal seek is always an option but needs to be refreshed every time cashflow changes AND is for 1 month at a time.

    I am trying to bring a formula solution into existence for dynamic column.

    PMT is an option using NPV formula for the FV input, but this doesn't deliver correct IRR.

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Reverse irr w/ irregular cash flows

    Quote Originally Posted by dfxryanjr View Post
    Goal is to determine what PAYMENT would deliver a set IRR given a series of irregular cash flows. Example:
    Initial investment is -100.
    Payment 1 thru 5 = 3, 4, 2, 1,7
    These payments are irregular as are all future payments.
    The goal is to determine (for each Payment date [1, 2, 3, 4, 5...]) what value would deliver a set IRR of 10%.
    So at payment 5, instead of 7, what would payment need to be to deliver a 10% IRR.
    My understanding is: for each payment #n, you will provide the previous payments #0 through #n-1, where payment #0 is the initial investment. And although the payment amounts are irregular, they occur at regular intervals.

    Your design might look something like the following table.


    A
    B
    C
    D
    E

    1

    Pmt#
    Prior
    Pmts
    Last
    Pmt

    Check
    Reqd
    IRR
    2
    0
    -100

    10.00%
    3
    1 3 110.00 10.00%
    4
    2 3 117.70 10.00%
    5
    3 8 126.17 10.00%
    6
    4 5 129.99 10.00%
    7
    5 6 137.49 10.00%
    8
    6 6 144.63 10.00%
    9
    7 5 152.50 10.00%
    10
    8 8 162.25 10.00%
    11
    9 1 169.67 10.00%
    12
    10
    185.54 10.00%
    Please Login or Register  to view this content.
    The formula is derived algebraically from the mathematical formula in the NPV help page (click here).

    Aside.... The formula in C3 could be simplified, to wit: =-B2*(1+E2). But that complicates the copy-down instructions.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    Re: Reverse irr w/ irregular cash flows

    I am trying to bring a formula solution into existence for dynamic column.
    Assuming the XNPV/XIRR equations are correct for this problem, I see no way to solve for "rate" without using numerical methods in some way.
    from the help files XNPV=sum(for i=1 to N)Pi/(1+rate)^((di-d)/365) XNPV function uses Pi, rate, and di to calculate XNPV. XIRR() function takes Pi and di and determines the rate that makes XNPV=0.

    One can program root finding algorithms directly in the spreadsheet. I have some 2D Newton-Raphson type examples here: https://www.excelforum.com/tips-and-...ind-roots.html It should be fairly straightforward to program this into the spreadsheet when you only want to change one of the Pi's. In theory, the algorithms extends to higher dimensions (changing more than one of the Pi's), but the computations quickly become rather complex, and you probably end up with situations with multiple possible solutions. Your example only talks about changing one of the Pi's. Looking forward, how many of the Pi's will you possibly want to change?

    As such, the solver/goal seek is always an option but needs to be refreshed every time cashflow changes
    This is true, though there are lots of examples out there of executing Goal Seek/Solver models as part of change and/or calculate event procedures, so that it doesn't require user input to execute the solver/goal seek model.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] uncertain cash-flows timing. create monthly cash report
    By excobra in forum Excel General
    Replies: 3
    Last Post: 05-14-2014, 11:10 AM
  2. formula accounting for cash flows.
    By Gaunta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2011, 06:54 PM
  3. Waterfall Cash Model, dividing up cash flows based on IRR
    By tomservo2009 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-21-2008, 05:57 PM
  4. [SOLVED] IRR for 2 cash flows
    By Maxymus in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2006, 08:45 PM
  5. IRR formula for monthly cash flows
    By MB Burgis in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-11-2006, 11:10 PM
  6. [SOLVED] PV of uneven stream of cash flows
    By PJF in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-29-2005, 10:05 PM
  7. Present Value of Remaining Cash Flows
    By Stratuser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2005, 02:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1