Originally Posted by
dfxryanjr
Goal is to determine what PAYMENT would deliver a set IRR given a series of irregular cash flows. Example:
Initial investment is 100.
Payment 1 thru 5 = 3, 4, 2, 1,7
These payments are irregular as are all future payments.
The goal is to determine (for each Payment date [1, 2, 3, 4, 5...]) what value would deliver a set IRR of 10%.
So at payment 5, instead of 7, what would payment need to be to deliver a 10% IRR.
My understanding is: for each payment #n, you will provide the previous payments #0 through #n1, where payment #0 is the initial investment. And although the payment amounts are irregular, they occur at regular intervals.
Your design might look something like the following table.

A

B

C

D

E

1

Pmt#

Prior
Pmts

Last
Pmt 
Check

Reqd
IRR

2

0

100 


10.00%

3

1 
3 
110.00 
10.00% 

4

2 
3 
117.70 
10.00% 

5

3 
8 
126.17 
10.00% 

6

4 
5 
129.99 
10.00% 

7

5 
6 
137.49 
10.00% 

8

6 
6 
144.63 
10.00% 

9

7 
5 
152.50 
10.00% 

10

8 
8 
162.25 
10.00% 

11

9 
1 
169.67 
10.00% 

12

10 

185.54 
10.00% 

The formula is derived algebraically from the mathematical formula in the NPV help page (click here).
Aside.... The formula in C3 could be simplified, to wit: =B2*(1+E2). But that complicates the copydown instructions.
Bookmarks