IRR Problem: Required inflows to solve for a desired IRR

[steg_smith]

Hi, imagine I have a project that lasts for 20 years that has a beginning outflow of $10 and in the first 10 years there are additional outflows of $2. Assume that the inflows are constant and they last from year 3 up until year 20. Is there a formulaic way (i.e. not using VBA, goal seek, etc) of finding out what this constant inflow needs to be to get me to a 10% IRR?

Many thanks in advance.

 

[rndthinktank]

I don't believe in my experience you can construct a formula to take into consideration what you want to do; you'd need to have it constructed like so:

Calculating Internal Rate of Return (IRR) using Excel - Excel VBA Templates

 

[rndthinktank]

Speaking of IRRs I need to calculate some!

 

[steg_smith]

Thanks, but even if I construct my spreadsheet like they have in the link you posted, it doesn't seem to help solve my issue.

 

[rndthinktank]

Why can't you use goalseek? Let me flick through my CFA book and I'll get back to you.

 

[steg_smith]

Many thanks. I am using goal seek now but I have about 100 of these projects and forecasts on the outflows are often changed, thus even with my basic goal seek macro it is still a time consuming process to update. That is why I would like to see if there is a formulaic way.

 

[shg]

Row\Col	A​	B​	C​	D​	E​	F​
1​	Year​	Outflow​	Inflow​
2​	0​	$(10.0000)​	$ - ​			Investment refers to B2
3​	1​	$ (2.0000)​	$ - ​			C3: =IF((A3 >= InflowBeg) * (A3 <= InflowEnd), Amt, 0)
4​	2​	$ (2.0000)​	$ - ​
5​	3​	$ (2.0000)​	$ 3.2884 ​
6​	4​	$ (2.0000)​	$ 3.2884 ​
7​	5​	$ (2.0000)​	$ 3.2884 ​
8​	6​	$ (2.0000)​	$ 3.2884 ​
9​	7​	$ (2.0000)​	$ 3.2884 ​
10​	8​	$ (2.0000)​	$ 3.2884 ​
11​	9​	$ (2.0000)​	$ 3.2884 ​
12​	10​	$ (2.0000)​	$ 3.2884 ​
13​	11​	$ -​	$ 3.2884 ​
14​	12​	$ -​	$ 3.2884 ​
15​	13​	$ -​	$ 3.2884 ​
16​	14​	$ -​	$ 3.2884 ​
17​	15​	$ -​	$ 3.2884 ​
18​	16​	$ -​	$ 3.2884 ​
19​	17​	$ -​	$ 3.2884 ​
20​	18​	$ -​	$ 3.2884 ​
21​	19​	$ -​	$ 3.2884 ​
22​	20​	$ -​	$ 3.2884 ​
23​
24​				Rate​	10%​	E24: Input
25​				k​	0.90909​	E25: =1/(1+rate)
26​				NPVOutflow​	($22.29)​	E26: =NPV(rate, $B$3:$B$22) + Investment
27​				InflowBeg​	3​	E27: Input
28​				InflowEnd​	20​	E28: Input
29​				Amt​	$3.2884 ​	E29: =-(1 - k) / (k ^ (InflowBeg) - k ^ (InflowEnd + 1)) * NPVOutflow
30​				Check: NPV(Inflow)​	$22.29 ​	E30: =NPV(rate, $C$3:$C$22)
31​				Check: IRR(net)​	10.000%​	E31: =IRR($B$2:$B$22 + $C$2:$C$22)

The table is only used to get the NPV of the outflow and to check the result.