(12C) Modified Internal Rate of Return - MIRR

[Joe_H]

I thought it would be an interesting challenge to program MIRR into the HP-12C. The Internal Rate of Return (IRR) is a standard feature on the calculator [f IRR] and is widely used in industry and is very well known. It used to challenge the older slower models I gather as they could be some seconds or considerably longer before iterating to the i in the TVM that resulted in a NPV of 0. Despite being computationally tough it is well understood in industry and that is the reason why it is used despite its inherent flaw of overstating the true profitability by treating all positive cashflows as if they would earn the same rate of return as the investment itself was producing. For example, if a business was earning on average 8% on investments and was investing in a new venture that had a considerably higher rate of return than normal it would however treat cashflow from that investment as if it was earning at that higher rate when IRR is calculated rather than the more likely 8% it is going to earn (an example of reversion to the mean I guess). This leads to an IRR that is too high.

Modified IRR is one solution that is quite popular and is offered on many business calculators (e.g. TI BAII+ Professional and HP-30b and probably other modern business HP's). It discounts the negative cashflows at the safe/normal/WACC rate (the company's cost of capital rate is most popular). Positive cashflows are future valued at a higher reinvestment rate. In the example above, the safe rate could be say, 5% and 8% the reinvestment rate they normally earn on investments. Putting these calculated PV and FV into the TVM (with PMT=0 and the correct n) will solve for i and that will be the MIRR. Normally, MIRR is lower than IRR but it doesn't have to be.

I want this to program in the HP-12C to use the Cashflow registers as the returns cannot be equal in such a scenario with some positive and some negative. This means that no R registers are available for variable storage and only the TVM registers and stack are available for storage - just like the Discounted Payback Period program I posted some days ago. I also wanted to avoid editing or changing the cashflow entries as typically you want to rerun such analysis with different rates IRR v MIRR v NPV etc. and don't want to have to keep correcting it with the consequent risk of mistakes and so on. This proved to be a much bigger challenge than I had expected. I'll describe the program and challenges in another post below.

[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.

[Joe_H]

Final post!

Overall, it was a great challenge and really interesting puzzle but the solution isn't really that practical as the program runs to 96 entries and only 7 registers remain so the max length of the cashflow can only be CF0-CF6 which isn't reasonable. I think there is a shorter way to do it but it involves deleting cashflow entries but I'll give it a shot for the hell of it.

The program offers greater possibilities on the Platinum as there are more registers and a couple can be used for storage which should reduce the program size a bit and also leave room for a long enough cashflow sequence of 12-15 or so (unlikely to do anything higher than that on a hand calculator).

If anyone has any thoughts on how the existing program could be shortened please let me know.

[Gamo]

Very interesting MIRR program !!

Here is a manual calculation using TVM from this link: Example 4.2

http://www.tvmcalcs.com/index.php/calcul...p12c_page3

Thanks

Gamo