I want to pick an IRR=r (say 15%), and determine how much would have to be paid in the last period of the cash flow sequence to achieve this IRR.

e.g.

using wikipedia e.g.

0 -100

1 40

2 59

3 55

4 x

Solving the summation equation for x

x = the amount that needs to be paid to reach an internal rate of return of 15%

r = .15

NPV = 0 = -100 + 40/(1+.15)^1 + 59/(1+.15)^2 + 55/(1+.15)^3 + x/(1+.15)^4 for x

0= -100 + 34.78261 + 44.61248 + 36.16339 + x/(1+.15)^4

0 = 15.5585 + x/(1+.15)^4

-15.5585 = x/1.749006

-15.5585*1.749006 = x

-27.2119 = x

(note: in this example the value of x is negative, but in most cases it will be positive)

please see bayparkcapital.com/topiclistE.php for progress to date

e.g.

using wikipedia e.g.

0 -100

1 40

2 59

3 55

4 x

Solving the summation equation for x

x = the amount that needs to be paid to reach an internal rate of return of 15%

r = .15

NPV = 0 = -100 + 40/(1+.15)^1 + 59/(1+.15)^2 + 55/(1+.15)^3 + x/(1+.15)^4 for x

0= -100 + 34.78261 + 44.61248 + 36.16339 + x/(1+.15)^4

0 = 15.5585 + x/(1+.15)^4

-15.5585 = x/1.749006

-15.5585*1.749006 = x

-27.2119 = x

(note: in this example the value of x is negative, but in most cases it will be positive)

please see bayparkcapital.com/topicli

Assume that your data are stored in an array like this

$seq = array(0=>-100, 1=>40, 2=>59, 3=>55);

$period_to_solve = 4;

$rate = 0.15;

//First sum everything up

for ($x=0; $x<$period_to_solve; $x++)

{

$sum += $seq[$x] * pow($rate + 1, $x);

}

// NPV = {sum} + x/(1+rate)^4

// -{sum} * (1+rate)^4 = x

$final_period = 0 - $sum * pow(1+$rate,$period_to_sol

It all seems to work except on little thing

The $sum should be equal to 15.5585. I ran another example using 30, 32, and 40, but the results in Excel are not the same as using the function above.

Thanks for the help,

www.bayparkcapital.com/G.p

I will post a couple more questions shortly.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

$sum += $seq[$x] * pow($rate + 1, $x);

to

$sum += $seq[$x] / pow($rate + 1, $x);