(12C) Modified Internal Rate of Return  MIRR

06152019, 09:27 PM
(This post was last modified: 06202019 06:53 PM by Joe_H.)
Post: #1




(12C) Modified Internal Rate of Return  MIRR
I thought it would be an interesting challenge to program MIRR into the HP12C. 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 HP30b 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 HP12C 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. 

06152019, 10:17 PM
Post: #2




RE: (12C) Modified Rate of Return  MIRR
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 (NN') 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 noninteger 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:
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 HP12C. 

06152019, 10:24 PM
Post: #3




RE: (12C) Modified Rate of Return  MIRR
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 CF0CF6 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 1215 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. 

06162019, 05:22 AM
(This post was last modified: 06162019 05:23 AM by Gamo.)
Post: #4




RE: (12C) Modified Rate of Return  MIRR
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 

« Next Oldest  Next Newest »

User(s) browsing this thread: 1 Guest(s)