Reverse IRR Calculation with two payment schedules (Target IRR, need to determine payment)

[spaniard25]

Hello,

I need to do the reverse of the function =IRR(), which is to spit out the annual cash flow required to meet a target IRR. The initial capital outlay is provided, and the cash flows are 15 annual payments. The first 5 annual payments are fixed and known, what I need to determine is the value of the payments for years 6 through 15 such that I achieve my IRR target (24% in this case).

I am aware of the PMT() function - (thanks to shg for helping me with this in another forum!), but I'm not sure if I can use it with the two payment schedules.

For example:
($1,000) initial capital outlay
$300 annual cash flow for years 1 - 5
What is the annual cash flow required in years 6 - 15 such that there is a 24.0% IRR?

(the answer is ~$140.2, found with goal seek. Can this be done with formulas?)

Thanks,

spaniard25

[shg]

The payment is the amount that makes the NPV of the whole cash flow = 0 at 24%.

	A	B	C	D
1		rr	24%	C1: Input
2		s	0.8065	C2: =1 / (1 + rr)
3		m	6	C3: Input
4		n	16	C4: Input
5
6	Per	Amt
7	0	($1,000.00)	($142.25)	C7: =NPV(rr, B7:B12)
8	1	$300.00	$ 140.44	C8: =-$C$7 * (1 - s) / (s^(m+1) - s^(n+1))
9	2	$300.00	($0.00)	C9: =NPV(rr, B7:B22)
10	3	$300.00	24.00%	C10: =IRR(B7:B22)
11	4	$300.00
12	5	$300.00
13	6	$140.44		B13 and down: =$C$8
14	7	$140.44
15	8	$140.44
16	9	$140.44
17	10	$140.44
18	11	$140.44
19	12	$140.44
20	13	$140.44
21	14	$140.44
22	15	$140.44

I can't rationalize why n=16 instead of 15 at the moment, other than it gives the right answer.

[spaniard25]

Thanks shg. Over at the MrExcel forums (where you helped me with a simpler version of this problem), user joeu2004 used the FV() function embedded in the PMT() function. Fundamentally the end result is the same, it looks like you used actual financial definitions instead of excel formulas. Link here.

Either way, thanks a lot. It's a pleasure to receive help from intelligent and friendly folks such as yourself.

spaniard25

[shg]

You're welcome. Please observe forum courtesy and post a link when you cross-post on another forum.

[faitas]

The payment is the amount that makes the NPV of the whole cash flow = 0 at 24%.

	A	B	C	D
1		rr	24%	C1: Input
2		s	0.8065	C2: =1 / (1 + rr)
3		m	6	C3: Input
4		n	16	C4: Input
5
6	Per	Amt
7	0	($1,000.00)	($142.25)	C7: =NPV(rr, B7:B12)
8	1	$300.00	$ 140.44	C8: =-$C$7 * (1 - s) / (s^(m+1) - s^(n+1))
9	2	$300.00	($0.00)	C9: =NPV(rr, B7:B22)
10	3	$300.00	24.00%	C10: =IRR(B7:B22)
11	4	$300.00
12	5	$300.00
13	6	$140.44		B13 and down: =$C$8
14	7	$140.44
15	8	$140.44
16	9	$140.44
17	10	$140.44
18	11	$140.44
19	12	$140.44
20	13	$140.44
21	14	$140.44
22	15	$140.44

I can't rationalize why n=16 instead of 15 at the moment, other than it gives the right answer.

Hello,
How can you adjust this to have 20 different paying schedules? One each year?!
Is that possible?

[FDibbins]

faitas welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.