(12C) Modified Internal Rate of Return - MIRR

[Joe_H]

As described above the program has to discount or PV any net negative cashflows at the capital cost rate (I'll call it i and the TVM register holding it [i]). Positive net cashflows have to be compounded or future valued at the higher reinvestment rate I'll call i*. The program also has to take into account the Nj at each step of the cashflow entered into the calculator (normally it's 1 but doesn't have to be and often isn't - I didn't want to place such a restriction). So the variables I have to deal with are:

n - automatically set when cashflow entered; it's the number of entries in the sequence
N - the total number of periods allowing for some Nj's > 1
i - the cost of capital rate
i* - the reinvestment rate
PV - the accumulator of PV's as the cashflow is read through
FV - the accumulator of FV's as the cashflow is read through
N' - the counter for the main loop - needed to know what period/year it is for the PV/FVing
CFj - the net cashflow for a given year
Nj - the number of periods that cashflow amount applies to - this is most often 1

That's 9 variables that have to be stored for use and some changing in the program.

The regular registers available are [n], [i], [PV], [PMT], [FV], the 4 stack registers and LSTx. That's 10 in total but really only 5 are normal storage and the others are much more difficult to use (particularly LSTx which barely counts as a 'storage' location).

Anyhow, I succeeded in the end using every available resource including the full stack as well as LStx. Talk about 'stackrobatics' as I've seen it referred to as. I wasn't sure how I could do it but reading the statistical functions by Sverdrup back in 1976 (thanks for that ref SlideRule!) gave me the basic idea of how to manage it. Clearly, N and N' are integers while i and i* are percents but could be in decimal form so an N and an i both could be stored together in a single register (maybe this is common in HP programming but is new to me). That now means I effectively have 12 storage locations and 9 variables so some room for moving things around and space for pushing things like 0's or 1's onto the stack etc. However, I ran into a problem with the positive cashflow loop as calculating the compounding exponent (N-N') meant Nj was going to drop off the stack and recovering it was not a practical option as the program was already big (n had been decremented after reading CFj an automatic feature). As Nj is an integer the same approach couldn't be used. Reviewing the registers [n] was the only one that just had an integer entry and it offered the only possibility. If Nj was decimalised then it could be stored with n (as long as there were no calls to read the cashflow as an error results from a non-integer n in a read). So, Nj was added to n then removed from it when there was room on the stack and returned to an integer. That solved the final problem (I thought it had beaten me before I came up with that solution!).

The program is below. To run it do as follows:

f CLEAR REG
{enter cashflow sequence in normal manner}
[n] - is already set by cashflow entry
[i] - cost of capital in % form which is easier
[PV],[PMT]&[FV] should be 0 from clearing registers
N {make sure that it is sum(Nj)}
ENTER
i*% {the reinvestment rate in % form}
ENTER
R/S

Code:


// Modified Internal Rate of Return (MIRR) for HP 12C - written by Joe Henry, Dublin - June 2019
// TVM registers are n = {set by CF entry}, [i] = finance/WACC rate i%, PV = 0, PMT = 0, FV = 0
// X,Y have reinvestment rate i*% and Z has the total number of cashflow periods N (different from n if any Nj's > 1)


01 EEX            // Start by calculating decimal i & i* and storing N.i in [i] and N'.i* in PMT
02 2
03 /
04 +            // X now contains N'.i* which is stored in PMT 
05 STO PMT        // this N' is the outer loop counter and the decimal reinvestment rate (i*) is the fractional part
06 g INTG        // recover N in order to store it in [i]
07 RCL i        // [i] contains the capital investment rate i%
08 EEX
09 2
10 /
11 +            // X now contains N.i which is stored in [i] 
12 STO i         // this N is not decremented in loop
13 RCL g Nj        // ::1 start of main loop
14 RCL g CFj
15 0
16 x<=y?         // if payment CFj is positive next line is executed
17 g GTO 37        // Goto ::4 positive payment loop
18 Rv            // ::3 start of negative cashflow loop - remove 0 from top of stack
19 RCL i         
20 g FRAC
21 1
22 +            // 1 + 0.i calculated and on stack
23 RCL PMT
24 g INTG        // Counter variable N'
25 y^x
26 1/x            // invert, X/Y and multiply rather than divide in order to recover Cj from LST x
27 X/Y
28 *
29 g LST x
30 X/Y
31 RCL PV        // Recover PV accumulator and add this PV to it
32 +
33 STO PV        // PV contains present value of the negative cashflows at the rate i
34 Rv
35 X/Y            // setting X:Nj & Y:CFj for the common section
36 g GTO 70        // Go to ::7 - common section for both + & - loops
37 Rv            // ::4 start of positive cashflow loop
38 X/Y            // Place Nj in X and then decimalise it
39 EEX        
40 2
41 /
42 RCL n        // Need to store Nj in [n] - it's lost off stack otherwise
43 +            // to store it in [n] (already in use) need to divide it by 100 and add it to current [n] value stored like i & i*
44 STO n
45 X/Y            // Restore Cj to X on stack
46 RCL PMT        // Get and calculate 1 + 0.i*
47 g FRAC
48 1
49 +
50 RCL i        // Calculate N-N* (the compounding exponent opposite of PV)
51 g INTG
52 RCL PMT
53 g INTG
54 -
55 y^x
56 *            // FV multiplies and not divides like PV i.e. no 1/x call here
57 RCL FV        // Add FV to accumulating FV
58 +
59 STO FV
60 Rv
61 RCL n        // Recover Nj and restore n to integer value
62 g FRAC
63 g LST x
64 g INTG
65 STO n
66 Rv
67 EEX
68 2
69 *            // Set up now for common section with X:Nj & Y:CFj
70 RCL PMT         // ::7 Start of the common section for + & - loops
71 1            // This part decrements and saves N' the main loop counter
72 -
73 STO PMT        
74 Rv                    
75 1            // Nj is in X and this decrements Nj
76 -
77 x=0?            // Is Nj=0? if so exit + or - loop and go to outer loop
78 g GTO 84        // goto ::6 final stage of outer loop (decrement n) and check if cashflow finished n=-1?
79 X/Y            // Cj moved to X to check if + or -
80 0
81 x<=y?        // if x<=y then CF is 0 or positive
82 g GTO 37        // Go to ::4 positive cashflow loop
83 g GTO 18        // Go to ::3 negative cashflow loop
84 RCL n        // ::6 Outer loop of iterations - decrementing n
85 1
86 CHS
87 X/Y
88 x<=y?        // n-1 in X and -1 in Y if n-1=-1 (i.e. n had been 0) then go to final stage of program else go back to start of main loop
89 g GTO 91        // go to ::5 final program stage if n was 0
90 g GTO 13        // go to ::1 start of main loop - don't need any vars on stack as new reading of cashflow
91 RCL i        // :: 5 This is the start of the final stage of the program that sets n to N  & runs TVM calculation
92 g INTG        // N is recovered from integer part of i TVM register
93 STO n        // n = N, PV = PV negative cashflows at rate i (PV accumulator), PMT = 0 & FV = FV positive cashflows at rate i* (FV accumulator)
94 0            // TVM algorithm solves for [i] which is the MIRR
95 STO PMT        // PMT needs to be set to 0 for correct interest rate calculation in TVM algorithm
96 i            // calculate MIRR - stores in [i] and on display

Try the following cashflow:

CF0 -€105,222
CF1 +€8,792
CF2 +€9,700
CF3 +€10,480
CF4 -€2,472
CF5 +€12,093
CF6 +€182,188

with i=i*=10%

IRR calculates as 15% and MIRR as 14.25%.

And using the Nj>1 feature

CF0 -€50,000 Nj 1
CF1 +€10,000 Nj 1
CF2 -€10,000 Nj 1
CF3 +€20,000 Nj 2
CF4 -€5,000 Nj 2
CF5 +€100,00 Nj 1

At i=4.5% i*=8.5% with N=7
IRR=18.50% and MIRR=14.18%

The program runs in much less than a second on my 3rd ed HP-12C.