(Free42,DM42) NPV and IRR

[Werner]

Update: replaced by this newer version that also includes MIRR
Solver program.

CF0 is separate
CFj is an nx2 matrix with the Cash flows (j=1..n) in column 1 and their frequency in column 2
CFj may also be a nx1 matrix, implying all Nj=1

uses subroutine Ni to calculate ((1+i)^N - 1)/i:

Code:

@                       X       Y
00 { 21-Byte Prgm }     i       N
01▸LBL "Ni"
02 X=0?
03 GTO 00
04 LN1+X
05 STO× ST Y
06 X<> ST L
07 X<>Y
08 E↑X-1
09 X<>Y
10 ÷
11 RTN
12▸LBL 00
13 +
14 END

Code:

00 { 140-Byte Prgm }
01▸LBL "NPV"
02 MVAR "CF0"
03 MVAR "CFj"
04 MVAR "IRR%"
05 MVAR "NPV"
06 1
07 RCL "IRR%"
08 %
09 LSTO "irr"
10 +
11 LSTO "r"
12 CLX
13 INDEX "CFj"
14 J-
15 J-
16 J+
17 FC? 76
18 GTO 03
19▸LBL 02 @ all Nj=1, use Horner scheme
20 RCLEL
21 +
22 RCL÷ "r"
23 J-
24 FC? 77
25 GTO 02
26 GTO 04
27▸LBL 03 @ CFj Nj, index at Nj
28 RCLEL
29 DSE ST X
30 GTO 00
31 J- @ Nj=1
32 CLX
33 RCLEL
34 +
35 RCL÷ "r"
36 GTO 01
37▸LBL 00 @ Nj>1, group CFj
38 CLX
39 RCL "r"
40 RCLEL
41 Y↑X
42 ÷
43 RCLEL
44 +/-
45 RCL "irr"
46 XEQ "Ni"
47 J-
48 RCLEL
49 ×
50 -
51▸LBL 01
52 J-
53 FC? 77
54 GTO 03
55▸LBL 04
56 RCL+ "CF0"
57 RCL- "NPV"
58 END

example: create the 7x2 matrix
[[1000 1]
[2000 2]
[3000 3]
[4000 4]
[5000 5]
[6000 6]
[7000 7]]

Start the solver: Shift-SOLVER, NPV
store the matrix in CFj
store -100000 in CF0
store 0 in NPV
press IRR% -> 2.0417694...

hope you like it
Werner

[Werner]

Regarding the solver: many times it just works correctly, but with the above example, if you specify 2 guesses (1 and 2) for IRR%, it still returns the correct answer, but mentions 'Extremum'? How can the same result, with he same input, be regarded as a root or an extremum, depending on the guesses?
Werner

[Pekis]

(06-12-2020 04:04 PM)Werner Wrote:  Regarding the solver: many times it just works correctly, but with the above example, if you specify 2 guesses (1 and 2) for IRR%, it still returns the correct answer, but mentions 'Extremum'? How can the same result, with he same input, be regarded as a root or an extremum, depending on the guesses?
Werner

Hello Werner,

With guesses 0.01 and 0.02, my program returns 0.020416943595, and if I press "IR" one more time, I get "Sign reversal", so I don't know what's goin' on in your program ... An extremum is impossible, I think, if first cash flow negative and the others positive ...

[Werner]

Investigated a little further..
and, of course, what looks like the same result isn't.
Supplying guesses 1 and 2 produces IRR%=2.04169...67997, which does not evaluate NPV to zero. Since the guesses do not span a root, it is possible that the algorithm stays in the positive part.
Supplying guesses 2 and 3, for instance, results in IRR%=2.024169..68027, and the resulting NPV *is* zero.

Werner

[Pekis]

(06-15-2020 01:16 PM)Werner Wrote:  Investigated a little further..
and, of course, what looks like the same result isn't.
Supplying guesses 1 and 2 produces IRR%=2.04169...67997, which does not evaluate NPV to zero. Since the guesses do not span a root, it is possible that the algorithm stays in the positive part.
Supplying guesses 2 and 3, for instance, results in IRR%=2.024169..68027, and the resulting NPV *is* zero.

Werner

With Emu42, answer is the same in my program (IRR=0.020416943595), wether with[0.01-0.02] or [0.02-0.03] as guesses.

Excel gets IRR = approx. 0.0204169435970961