Calculate Initial investment to achieve specific IRR

[Hoister]

I am trying to build a formula that will return my year 0 cash outflow to achieve a 12% IRR on a series of future cashflows.

The cashflows would be:

Year 0: X (some negative value)
Year 1: 400K
Year 2: 450K
year 3: 475K
year 4: 500K

IRR: 12%

I am struggling to come up with a formula that would allow me to easily solve for X in scenarios of different lengths and cashflow values.

 

[DRSteele]

Well, there is no closed-form formula that can solve for X.

You can use the Goal Seek functionality (found in the Data | Whatif section of the ribbon). Set the cell containing the IRR formula to 12% by changing the Year 0 value and that will reckon the first value. It beats typing in numbers randomly to bootstrap the solution.

 

[DRSteele]

I misunderstood and spoke too soon. Of course there is a way to get the initial investment.

Book1
	E	F
1	Target IRR	12%
2
3	year	value
4	0	($1,371.73)
5	1	$400.00
6	2	$450.00
7	3	$475.00
8	4	$500.00
9
10
11	IRR	12%
Sheet1

Cell Formulas
Range	Formula
F4	F4	=-NPV(F1,F5:F8)
F11	F11	=IRR(F4:F8)

 

[Hoister]

I misunderstood and spoke too soon. Of course there is a way to get the initial investment.

Book1
	E	F
1	Target IRR	12%
2
3	year	value
4	0	($1,371.73)
5	1	$400.00
6	2	$450.00
7	3	$475.00
8	4	$500.00
9
10
11	IRR	12%
Sheet1

Cell Formulas
Range	Formula
F4	F4	=-NPV(F1,F5:F8)
F11	F11	=IRR(F4:F8)

Thank you!