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

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

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

2. ## Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

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.

3. ## Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

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

4. ## Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

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

5. ## Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

Originally Posted by 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.
Hello,
How can you adjust this to have 20 different paying schedules? One each year?!
Is that possible?

6. ## Re: Reverse IRR Calculation with two payment schedules (Target IRR, need to determine paym

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.

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.

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

#### 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