(%42%) Cash Flow Analysis: NPV, IRR, MIRR

[Werner]

NPV, IRR and MIRR calculations involving multiple uneven cash flows at regular intervals.
This program replaces (and adds on to) my previous entry here.
It will run in all flavours of the 42: HP42S, Emu42, Free42, DM42.
No local variables were used.

1. Interactive mode
----------------------

usage: XEQ "CFA" -> [CFj] [IRR%] [NPV] [+%] [-%] [MIRR%]

CFj : matrix of cashflows CF0..CFn in column 1 and (optionally in column 2) their corresponding frequency Nj
IRR% : Internal Rate of Return, or discount rate for NPV calculation
NPV : Net Present Value
+% : re-investment rate applied to positive cashflows
-% : financing rate applied to negative cashflows
MIRR% : Modified Internal Rate of Return

example 1
---------

j CFj
0 -100 000
1 18 000
2 -50 000
3 25 000
4 25 000
5 225 000

1.XEQ "CFA"

2.create the matrix

6 ENTER 1 NEWMAT EDIT
[[-100000 ]
[ 18000]
[-50000]
[ 25000]
[ 25000]
[225000 ]]


3.EXITALL R/S (EXITALL also exits from the menu, use R/S to get back)

4. [CFj] -> CFj=[ 6x1 Matrix ]

5. 10 [IRR%] -> IRR%=10

6. [NPV] -> 50606.83

7. [IRR%] -> 19.33

8. 10 [+%] -> +%=10

9. 5 [-%] -> -%=5

10. [MIRR%] -> 16.29

example 2
---------
j CFj
0 -100000
1 -100000
2 40000
3 40000
4 40000
5 40000
6 -10000
7 35000
8 35000
9 35000
10 35000
11 35000
12 -10000
13 30000
14 30000
15 30000
16 30000
17 30000
18 -10000

now create the 7x2 matrix

[[-100000 2]
[ 40000 4]
[ -10000 1]
[ 35000 5]
[ -10000 1]
[ 30000 5 ]
[ -10000 1]]

and determine
- NPV with discount rate 8% (52933.21)
- IRR% (12.19)
- MIRR% with reinvestment rate 8% and financing rate 6% (9.25)

2 .Program mode
-------------------

The routines NPV, IRR% and MIRR% can also be called from within a program.
They all need the variable CFj to hold the cash flows (and frequencies), and additionally:
NPV: IRR% discount rate
MIRR%: the re-investment rate +% and financing rate -%

Program listing:

Code:

00 { 445-Byte Prgm }
01▸LBL "CFA"
02 MVAR "CFj"
03 MVAR "IRR%"
04 MVAR "NPV"
05 MVAR "+%"
06 MVAR "-%"
07 MVAR "MIRR%"
08▸LBL 10
09 VARMENU "CFA"
10 RTN
11 FC? 47 @ VARMENU no longer active
12 GTO 10
13 ALENG
14 ASTO ST Y
15 XEQ IND ST X
16 GTO 10
17▸LBL 03
18 "CFj"
19 ASTO ST X
20▸LBL 05
21 X≠Y?
22 GTO IND ST Y
23▸LBL 02
24 RCL IND ST Y
25 RTN
26▸LBL 04
27▸LBL "IRR%"
28 PGMSLV "NPV"
29 CLX
30 STO "IRR%"
31 100
32 SOLVE "IRR%"
33 RTN

34▸LBL "NPV"
35 MVAR "CFj"
36 MVAR "IRR%"
37 1
38 RCL "IRR%"
39 %
40 + @ R
41 0 @ sum
42 INDEX "CFj"
43 J-
44 J-
45 J+
46 FC? 76
47 GTO 07
48▸LBL 06 @ all Nj=1, use Horner scheme
49 RCLEL
50 +
51 RCL÷ ST Y @ R
52 J-
53 FC? 77
54 GTO 06
55 GTO 04
56▸LBL 07 @ CFj Nj, index at Nj
57 RCLEL
58 J-
59 ABS
60 DSE ST L
61 GTO 00
62 CLX @ Nj=1
63 RCLEL
64 +
65 RCL÷ ST Y @ R
66 GTO 01
67▸LBL 00 @ Nj>1, group CFj
68 RCL ST Z @ R Nj sum R
69 X<>Y
70 Y↑X
71 STO÷ ST Y
72 X<> ST L
73 +/-
74 0.01
75 RCL× "IRR%"
76 XEQ "Ni"
77 RCLEL
78 ×
79 -
80▸LBL 01
81 J-
82 FC? 77
83 GTO 07
84▸LBL 04
85 ×
86 RTN

87▸LBL "MIRR%"
88 MVAR "CFj"
89 MVAR "+%" @ reinvestment rate, apply to positive CFj
90 MVAR "-%" @ financing rate, apply to negative CFj
91 CLX @ FV
92 ENTER @ PV
93 INDEX "CFj"
94 J+
95 J-
96 FC? 76
97 GTO 09
98▸LBL 08 @ X:PV Y:FV, all Nj=1
99 RCL "-%"
100 %
101 +
102 X<>Y
103 RCL "+%"
104 %
105 +
106 X<>Y
107 RCLEL
108 X<0?
109 STO+ ST Y
110 X>0?
111 STO+ ST Z
112 R↓
113 J+
114 FC? 77
115 GTO 08
116 +/-
117 ÷
118 RCL "CFj"
119 DIM?
120 -
121 GTO 04
122▸LBL 09 @ PV FV, CFj Nj, positioned on CFj
@ FV := FV*(1+'+%'/100)^Nj
@ PV := PV*(1+'-%'/100)^Nj
123 J+
124 1
125 RCL "+%"
126 %
127 +
128 RCLEL
129 Y↑X
130 STO× ST Z
131 SIGN
132 RCL "-%"
133 %
134 +
135 RCLEL
136 Y↑X
137 STO× ST Y
138 X<> ST L @ Nj
139 J-
140 RCLEL
141 X<0?
142 GTO 00
143 CLX
144 0.01
145 RCL× "+%"
146 XEQ "Ni"
147 RCLEL
148 ×
149 STO+ ST Z
150 R↓
151 GTO 01
152▸LBL 00
153 CLX
154 0.01
155 RCL× "-%"
156 XEQ "Ni"
157 RCLEL
158 ×
159 +
160▸LBL 01
161 I+
162 FC? 76
163 GTO 09
164 +/-
165 ÷
166 RCL "CFj" @ determine sum Nj
167 TRANS
168 RSUM
169 EDIT
170 →
171 ENTER
172 EXITALL
173 R↓
174 DSE ST X
175▸LBL 04
176 1/X
177 Y↑X
178 RCL "-%"
179 %
180 +
181 100
182 STO× ST Y
183 -
184 RTN

185▸LBL "Ni" @ ((1+i)^N - 1)/i
186 X=0?
187 GTO 00
188 LN1+X
189 STO× ST Y
190 X<> ST L
191 X<>Y
192 E↑X-1
193 X<>Y
194 ÷
195 RTN
196▸LBL 00
197 +
198 END

Cheers, Werner

[garudaquila]

is this available on C47 ?