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 #n-1, 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 copy-down instructions.
Bookmarks