Reverse IRR Calculation? (Determine annual cash flow to meet target IRR)

[spaniard25]

Hello,

I have what seems to be a fairly straightforward problem, but I have searched the forums and found limited information on the topic. The problem:

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 constant, annual payments. For a given IRR target (24% in this case), I need to determine what cash flow is necessary (15 periodic annual payments) to hit that target.

Is this possible? Currently I am manually using goal seek. Is a goal seek macro my only option?

Thanks,

spaniard25

 

[VBA Geek]

You can use Solver which is more powerful than Goal Seek


How to Use the Solver Tool in Excel - YouTube

 

[shg]

	A​	B​	C​
1​	target IRR​	24%​	B1: Input
2​	payment​	$249.92 ​	B2: =PMT(24%, 15, B3)
3​	initial outlay​	($ 1,000.00)​	B3: Input
4​	1​	$ 249.92​	B4 and down: =$B$2
5​	2​	$ 249.92​
6​	3​	$ 249.92​
7​	4​	$ 249.92​
8​	5​	$ 249.92​
9​	6​	$ 249.92​
10​	7​	$ 249.92​
11​	8​	$ 249.92​
12​	9​	$ 249.92​
13​	10​	$ 249.92​
14​	11​	$ 249.92​
15​	12​	$ 249.92​
16​	13​	$ 249.92​
17​	14​	$ 249.92​
18​	15​	$ 249.92​
19​	check​	24.00%​	B19 and down: =IRR(B3:B18)

 

[spaniard25]

Thank you shg! That is exactly what I was looking for, I'm embarrassed I didn't think of that.

 

[shg]

You're welcome.

The formula in B2 should be

=PMT(B1, 15, B3)

 

[spaniard25]

I now need to complicate this problem a little bit.

I still have the goal of calculating the annual cash flow required to meet a target IRR (24%), but this time the first five annual payments are fixed & known. I need to calculate the annual payment required for payments 6 through 15 to meet the target IRR.

Using shg's example above, let's say payments for years 1-5 are $300, I need the logic that will determine the annual payments for years 6-15 such that the overall IRR is 24.0% (it's ~$140.3, found using goal seek).

-spaniard25

 

[joeu2004]

I still have the goal of calculating the annual cash flow required to meet a target IRR (24%), but this time the first five annual payments are fixed & known. I need to calculate the annual payment required for payments 6 through 15 to meet the target IRR.

Using shg's example above, let's say payments for years 1-5 are $300, I need the logic that will determine the annual payments for years 6-15 such that the overall IRR is 24.0% (it's ~$140.3, found using goal seek).

=PMT(B1,B3,-FV(B1,B2,B4,-B5))

where B1=24% (IRR), B2=5 (1st number of pmts), B3=10 (2nd number of pmts), B4=300 (pmt for B2 periods), B5=1000 (initial cash flow).

Note that all dollar amounts are positive.

 

[spaniard25]

Joeu2004 thank you so much! I should have paid more attention in my engineering economics course

 

[spaniard25]

For future readers, this question was also posted in the ExcelForum.com and an alternative answer was presented by shg. Link here.

 

[bmac777]

this might be a silly question - but what about the same problem as above, but this time the initial capital outlay is what we're solving for to meet the target IRR?