# Reverse irr w/ irregular cash flows

1. ## 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. ## 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?

3. ## Re: Reverse irr w/ irregular cash flows

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. ## Re: Reverse irr w/ irregular cash flows

Originally Posted by dfxryanjr
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. ## 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.

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