Reverse irr w/ irregular cash flows

[dfxryanjr]

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.

[MrShorty]

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?

[dfxryanjr]

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.

[joeu2004]

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.

[MrShorty]

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.