▼
Posts: 18
Threads: 2
Joined: Oct 2008
I have been trying to use several different examples I found on this forum for solving MIRR with my new hp17BII and none of them seem to be producing the correct value, or else they take a long time to solve, were designed to require iterative solving, which is slow.
Solving MIRR manually is very fast with this calculator, so it seems silly that I can't program the same thing in to the solver. I tried to write one of my own also, but it came up with NO SOLUTION.
Does anyone have any good suggestions for a solver program that will solve for MIRR.
I'm about ready to return this calculator and just get the TIBAII which has MIRR built in already. But I would love to keep this one in case I need to program other simpler stuff in the future. But so far, not having much luck. I tried to use the equations listed in the back of the manual to solve for NPV, just to see if I am on the right track and that comes up with incorrect values also compared to what I get in Excel.
Thanks
▼
Posts: 174
Threads: 20
Joined: Sep 2006
Dewdman42,
be aware that the BAII+ professional solution requires to solve for IRR first. "Ill behaved" CFs (where you need MIRR most) can't be solved  see recent example in this forum (latest archive).
There is a MIRR algorithm which does work on my "gold" 17bII+, which is based on the 19b version.
Quote:
MODIRR:
(1+MIRR%/100)^SIGMA(L:1:SIZEC($):1:#T($:L))
=
SIGMA(J
:0:SIZEC($):1
:MAX(FLOW($:J):0)*USFV(RSK%:#T($:J))
*SPFV(RSK%:SIGMA(L:J+1:SIZEC($):1:#T($:L)))
)/(MIN(FLOW($:0):0)
+SIGMA(J
:1:SIZEC($):1
:MIN(FLOW($:J):0)*USPV(SAF%:#T($:J))
*SPPV(SAF%:SIGMA(L:1:J1:1:#T($:L))))
)
Best regards,
Peter A. Gebhardt
PS: You also should give W. Bruce Maguire II's solution a try (which does include FMRR too).
http://www.hpmuseum.org/cgisys/cgiwrap/hpmuseum/articles.cgi?read=234
Edited: 7 Oct 2008, 11:04 a.m.
Posts: 1,392
Threads: 142
Joined: Jun 2007
This formula should work, as long as you enter a NEGATIVE number for NPV:
MIRR=100x((NFV/(NPV))^(1/N)1)
▼
Posts: 18
Threads: 2
Joined: Oct 2008
guys, thank you both for replying. I did try that long example from Bruce and checked my typing 3 times. The FMRR version did not work at all. The MIRR from him that was in the forum post by him in 2001 but not in the article section of this site, that version did work, but it takes a long time, it looks like because it has to solve iteratively because of the way its coded. I think it should be possible to form the equation in such a way as to solve the MIRR directly, rather quickly...which is why I tried to do it myself.
I will try these shorter versions you have both provided. Thanks for that. One thing, on the second example, as far as I know the 17BII does not provide an NPV or NFV function, so I can't use that formula exactly(its the same formula provided in the back of the manual).
NPV has to be substituted with the following:
CF0 + SIGMA(i:1:sizec(CASHFLOW):1:(CFi x USPV(RATE:#T(CASHFLOW:i)) x SPPV(RATE:Ni))
Where Ni is the total number of cash flows prior to this one.
NFV is even more complicated since it contains an NPV component as well as SPFV()
I tried to code something myself last night using these equations in the back, but came up with wrong answers..
I will try Peter's version now...Thanks Peter.
And also thanks for letting me know about the BAII. I already have one ordered, so one of these two calculators will go back by the end of this week. I'd like to keep the HP in case I learn how to program other simpler things. Seems like a good calculator. In general, I find IRR useless and only want to use MIRR in order to avoid overly generous reinvestment rates in the final result. I don't know why all the calculators don't include MIRR because from what I can tell, its easier to solve then IRR and makes more sense for most people to use it. I also made a short cheat sheet for how to calculate MIRR manually on the 17bII according to the manual and its actually very easy, especially if the entry cost is the only negative cash flow. Doing it that way involves a single NFV calculation followed by an FV calculation and its very fast, 10 keystrokes at most. But having something in the solver would be even more ideal if I can get it to work.
▼
Posts: 18
Threads: 2
Joined: Oct 2008
Peter, I tried your solution and it works well. THANKS! Seems to do it directly too, no iteration required. Good idea using min and max.
Thanks again.
The thing that is interesting about Bruce's original solution is that you can enter P/YR it then do the interest rate conversions automatically. Its somewhat confusing to follow the logic to understand how it is that does these extra calculations without effecting the main equation that is being solved. This is probably why it ends up having to work iteratively I guess.
Edited: 7 Oct 2008, 5:40 p.m.
▼
Posts: 174
Threads: 20
Joined: Sep 2006
Dewdman42,
the compliments should be directed solely to the ingenious team of programmers & technical writers, who developed such fine software and documentation.
We always stand on the shoulders of great men (and women) who had been there before!
Quote:
Good idea using min and max.
The solution proposed by Don is charming because it minimizes the steps of calculating the MIRR if you either don't have access to a standalone SOLVER solution or you want to save RAM (obviously no longer a problem with the 32k of the newer models  but believe me, I'm already asking for "more memory free" ;)
And yes, you are right, there is no SOLVER equivilant to the menu based NPV/NFV.
Best regards
Peter A. Gebhardt
▼
Posts: 18
Threads: 2
Joined: Oct 2008
Did you get that solution from someplace else? Where did you find it? I have been trying to find a collection of ready made programs for the 17bII, but can't seem to find a source for them. Partly I'm just looking to gain some better understanding about how it works, how I can combine different menu items with seperate solutions per menu item, etc.
Now I'm trying to take it a step further also to do some additional calculating, but running into problems again, any suggestions appreciated. Essentially, when I have a monthly cash flow situation, I want to calculate the MIRR, as now, but convert the final value to an EFFECTIVE annual rate. Converting the nominal monthly rate to annual effective basically means doing this:
annual = (monthlynominal+1)^121
I've tried integrating the inverse of this into the solution you provided, but so far coming up empty. At one point I sorta got it to work but it was doing something wrong and ending up with the correct answer but with E24 added, which is obviously not the right answer.
Regarding manual methods, I find the method in the manual the easiest, but you do need two seperate cash flow sheets to do it, if there are more negative flows beyond the entry cost. Then you take NPV of negatives(in their own cashflow sheet), store 1. NFV of positives(in their own cashflow sheet) store 2. Then go to TVM and use the RCL 1 for the PV and RCL 2 for the FV and solve for i%. Works very fast and easy, particularly if the only negative cash flow is the entry cost, then only one cash flow sheet is needed for all the positives with CF0 set to zero in that sheet. use the entry cost for PV and the NFV of positives for FV, solve for i%
Edited: 7 Oct 2008, 6:52 p.m.
▼
Posts: 174
Threads: 20
Joined: Sep 2006
Quote:
Did you get that solution from someplace else? Where did you find it?
The solution I've provided is the modified program from the "HP19BII Owner's Manual Color", p.258 (It's on Daves DVD, which is highly recommended, because it contains another hidden gem  the "HP27S/19B Technical Applications" manual!)
Quote:
I've tried integrating the inverse of this into the solution you provided, but so far coming up empty. At one point I sorta got it to work but it was doing something wrong and ending up with the correct answer but with E24 added, which is obviously not the right answer.
0*(
L(EMIR%:100*((1+MIRR%/100)^121))
+EMIR%
)
+(1+MIRR%/100 ....
=
....
This will eventually lead to iterations (I'm writing above from memory).
You also can put the 0*(...) part into a 2nd SOLVER equation.
Best regards
Peter A. Gebhardt
Edited: 7 Oct 2008, 7:21 p.m.
▼
Posts: 1,392
Threads: 142
Joined: Jun 2007
Thanks Peter and Dewdman. Yes, my little equation assumed you already knew NPV and NFV.
Here are some resources that might help you. First, the Applications Manual that Peter mentioned is a veritable goldmine of information about the solver, and has many great examples, mostly from the scientific world and a couple, at least, that won't work on the 17bii+ solver (prime factors and LCD/GCM). The solver in the original 17b and bii is different than the solver in the +, unfortunately. There are workarounds, but it is sometimes an exercise in frustration getting some equations to work on the +.
Gene Wright has some excellent tutorials on the 17bii+ here.
A man named John Tirone has written a book (Professional Real Estate Problem Solving Using the HP17BII) that has many great solver equations, mostly in the real estate realm, obviously. It contains solver equations for these things. You can get it on Amazon.com.
Hope this helps.
▼
Posts: 18
Threads: 2
Joined: Oct 2008
Thanks for those resources. I will check them all out.
Posts: 18
Threads: 2
Joined: Oct 2008
So I just bought the book on Real estate stuff with 17bII. I think that will help a lot. i also found the user manual for the 19B online at HP's site. I see the MIRR solution in there. I got the tutorials from Gene Wright and will look through them. Looks like the 19B has more explanation than the 17B manual does about how the solver works. I also notice that the list of functions in the 19B manual has some functions that are not listed in the 17B manual. are those functions available to me in the 17B as well? For example, PV, PMT, FV, etc. ??
This technical application manual that is on the DVD, sounds like would probably be good. Kind of pricy to get that DVD just for that though, so I will probably go with the other book I just ordered for now and try to figure things out.
So the solver on the 17B pretty much works just like the 19B and 27S?
▼
Posts: 1,545
Threads: 168
Joined: Jul 2005
No, those functions from the 19b are NOT available on the 17b or 17b2+.
Posts: 1,392
Threads: 142
Joined: Jun 2007
I don't have a 19B, but I think it had scientific functions like SIN, COS, and TAN, that the 17b lacks. Also, the TVM variables like PV and FV on the 17b do not carry over to the solver equations, unfortunately. If you use the variable PV in the solver, it is just another variable and has no relation to the PV in the TVM menu.
The solver on the 17b and 17bii works "exactly" like the solver on the 19b and 27s, AFAIK. The solver on the 17bii+ (both versions, gold and the new retrolooking silver), however, differs from the solver on the earlier 17b and bii. If you stay away from the L and G functions, you "probably" won't see any difference (other than slower execution speed on the +). But if you modify menu variables with the L function, watch out! You probably won't get the results you would expect. See the article I wrote on that subject in the Articles section (numbers 830 and 712).
▼
Posts: 18
Threads: 2
Joined: Oct 2008
Thanks for that, that's interesting. is the 17bii+ just broken or is it an expected behavior that HP considers an improvement?
Posts: 1,392
Threads: 142
Joined: Jun 2007
Peter and Dewdman, I have a question. In the 17Bii+ manual on pages 210 to 212, they show an example problem on MIRR, and the final answer they get is 12.18. I get the same answer if I do it manually, like they did.
But if I use Peter's equation, I get 11.86, and if I use Excel I get 11.86 also, which leads me to wonder if the manual is wrong, which means that the manual process of calculating MIRR is also wrong.
Any ideas?
▼
Posts: 18
Threads: 2
Joined: Oct 2008
Right now I'm using Peter's solver and getting the right answer, and is the same thing I get with the manual process indicated in the manual and the same answer I get with Excel.
You're probably looking at the cash flows slightly wrong in one case or the other in some way. Remember that in Excel to calculate NPV you need to exclude the first entry cost cell from the range of cells and then add it to the end.
=NPV(.05,A2:A5) + A1
(where A1 has the first entry cost cell of the cash flow series)
if you're using the MIRR() function in excel, then it should include the whole range, just like on Peter's function. And the result should match.
for Peter's algorithm use only one cash flow with all the negs and positives, INCLUDING the entry cost.
Edited: 7 Oct 2008, 9:16 p.m.
Posts: 18
Threads: 2
Joined: Oct 2008
hmm. Actually, when I put the example from the book into Excel I'm not getting the same answer as the manual either. Must have something to do with the negative cash flows. I'm not sure who is correct right now. I will have to play around with that some more.
Thanks for pointing that out.
Posts: 18
Threads: 2
Joined: Oct 2008
Ok, I know the reason for the discrepancy. The cash flow in the book example is monthly. So the safe and risk rates need to be divided by 12. This is done in the book example manually. For excel and for Peter's solution you need to also divide the risk and safe rates by 12 before entering them in to get the nominal monthly rates to use.
However the book manual method returns the nominal ANNUAL rate. Peter's solution and Excel both return the PERIODIC nominal rate. So multiply that periodic result by 12 and you will get 12.18% for everyone as the annual nominal rate.
▼
Posts: 1,392
Threads: 142
Joined: Jun 2007
Posts: 18
Threads: 2
Joined: Oct 2008
ok, so here is what I'm trying to do now.
(MIR%/100+1)^SIGMA(L:1:SIZEC($):1:#T($:L))
*(MIN(FLOW($:0):0)
+SIGMA(J :1:SIZEC($):1 :MIN(FLOW($:J):0)*USPV(SAF%/PYR:#T($:J))
*SPPV(SAF%/PYR:SIGMA(L:1:J1:1:#T($:L)))))
+ SIGMA(J :0:SIZEC($):1 :MAX(FLOW($:J):0)*USFV(RSK%/PYR:#T($:J))
*SPFV(RSK%/PYR:SIGMA(L:J+1:SIZEC($):1:#T($:L)))))
I changed the equation from the 19B manual so that the whole thing equals zero. It works this way. My intention moving forward is to try to use the IF(S(MIR%): function so that I can put the solution for EFF% into the same equation but different menu solver. I'm trying the following and two things are happening wrong. For one thing, the MIR solve is going through the iterator now, which I thought should not happen since MIR only appears once in the TRUE expression for the IF function. According to the manual it can appear twice, once in the TRUE and once in the FALSE expression. Somehow the iterator is still getting called up though. Any ideas?
The other problem is that the solve for EFF is coming up with junk answer. Here's what I'm trying:
MIRR: IF(S(MIR%):
(MIR%/100+1)^SIGMA(L:1:SIZEC($):1:#T($:L))
*(MIN(FLOW($:0):0)
+SIGMA(J :1:SIZEC($):1 :MIN(FLOW($:J):0)*USPV(SAF%/PYR:#T($:J))
*SPPV(SAF%/PYR:SIGMA(L:1:J1:1:#T($:L)))))
+ SIGMA(J :0:SIZEC($):1 :MAX(FLOW($:J):0)*USFV(RSK%/PYR:#T($:J))
*SPFV(RSK%/PYR:SIGMA(L:J+1:SIZEC($):1:#T($:L))))
:(MIR%+1)^PYR1EFF%)
Any ideas?
▼
Posts: 1,392
Threads: 142
Joined: Jun 2007
Two things.
The 17bii+ solver does iteration in cases where it would not have done iteration on the original 17b and bii. The old rules about when iteration will occur don't apply to the + solver. I'm not exactly sure why that is, but I know it is true.
In your last equation, IF(S(MIR%): you need MIR% at the end of this clause. I don't know that that would effect EFF, but it might.
▼
Posts: 18
Threads: 2
Joined: Oct 2008
I'm not sure I understand where you mean to put MIR% or why. Can you please elaborate?
That sucks to hear that HP screwed up the logic on the 17bII+, iterating when it does not need to is disappointing. It seems like about the best current model financial calculator to buy, but its disappointing to hear stuff like that.
▼
Posts: 1,392
Threads: 142
Joined: Jun 2007
Yes, look at the 17bii+ manual, page 178 where it gives an example of the S function. It is necessary to subtract the value you are solving for from the answer. For instance, suppose you could solve an equation for A or B. It would look like this:
IF(S(A): code to calc A A:(solving for B) code to calc B B)=0
You can omit the =0 and it is understood that the equation = 0, but I'm pretty sure you have to subtract the value you are solving for, else it won't work.
HP is aware of the 17bii+ solver deficiencies I have mentioned; perhaps one day they will be fixed.
▼
Posts: 18
Threads: 2
Joined: Oct 2008
oh I see what you are getting at. Well in the book examples they were moving things around to arrive at a comparable equation.
KGx2.21=LB
therefore
KGx2.21LB=0
Its the same equation, but rearranged to put zero on one side.
The equation I'm trying to solve in my FALSE expression above is:
EFF%=(MIR%+1)^PYR1
therefore
0=(MIR%+1)^PYR1EFF%
Am I missing something?
▼
Posts: 1,392
Threads: 142
Joined: Jun 2007
Make your nexttolast line read:
*SPFV(RSK%/PYR:SIGMA(L:J+1:SIZEC($):1:#T($:L))))MIR%
Then try it.
▼
Posts: 18
Threads: 2
Joined: Oct 2008
That didn't make any difference in the result, though I'm surprised by that because putting MIR% to the end of the equation does NOT equal zero.
This solution is a bit different than the S() examples in the book because MIR% is not by itself on the left side of the equation. So its not a matter of merely subtracting it from both sides to get the left side to equal zero.
Here is the original solution from Peter(I have seperated it into chunks to see how things are rearranged:

MODIRR: (1+MIRR%/100)^SIGMA(L:1:SIZEC($):1:#T($:L))
=
SIGMA(J :0:SIZEC($):1 :MAX(FLOW($:J):0)*USFV(RSK%:#T($:J))
*SPFV(RSK%:SIGMA(L:J+1:SIZEC($):1:#T($:L))) )
/
(
MIN(FLOW($:0):0)
+SIGMA(J :1:SIZEC($):1 :MIN(FLOW($:J):0)*USPV(SAF%:#T($:J))
*SPPV(SAF%:SIGMA(L:1:J1:1:#T($:L))))
)

There are 3 main blocks on the right side of the equation. The second two blocks are grouped together. Multiply this on both sides:

MODIRR: (1+MIRR%/100)^SIGMA(L:1:SIZEC($):1:#T($:L))
* (
MIN(FLOW($:0):0)
+SIGMA(J :1:SIZEC($):1 :MIN(FLOW($:J):0)*USPV(SAF%:#T($:J))
*SPPV(SAF%:SIGMA(L:1:J1:1:#T($:L))))
)
=
SIGMA(J :0:SIZEC($):1 :MAX(FLOW($:J):0)*USFV(RSK%:#T($:J))
*SPFV(RSK%:SIGMA(L:J+1:SIZEC($):1:#T($:L))) )

Then add the one remaining group on the right side over to the left side to arrive at zero on the right:

MODIRR: (1+MIRR%/100)^SIGMA(L:1:SIZEC($):1:#T($:L))
* (
MIN(FLOW($:0):0)
+SIGMA(J :1:SIZEC($):1 :MIN(FLOW($:J):0)*USPV(SAF%:#T($:J))
*SPPV(SAF%:SIGMA(L:1:J1:1:#T($:L))))
)
+ SIGMA(J :0:SIZEC($):1 :MAX(FLOW($:J):0)*USFV(RSK%:#T($:J))
*SPFV(RSK%:SIGMA(L:J+1:SIZEC($):1:#T($:L))) )
= 0

MIRR is buried inside there. Solving first for an equation that equates to zero is what S() is expecting to see as I understand it.
The above equation does solve MIRR correctly and doesn't iterate to do it. However only when I move it inside S() does it start to iterate, even without the ELSE clause present. But its entirely possible that the nature of this equation does not work well with S(). Perhaps S() needs to see something with MIR% on the end. First I would need to figure out an equation that has MIRR alone on the left side

MIR$=some_complicated algorithm that I couldn't figure out
therefore
0=some_complicated_algorithmMIR%

Every time I have tried to isolate MIRR onto the left side alone, I've come up with bogus results. if anyone else can figure it out, love to hear it.
Edited: 7 Oct 2008, 11:02 p.m.
▼
Posts: 1,392
Threads: 142
Joined: Jun 2007
Good luck Dewdman. I hope someone can jump in here and show us something that works with S() and, hopefully, explain why it works. Trying to figure out the intricacies of this solver is, well, I'm going to bed!
Posts: 18
Threads: 2
Joined: Oct 2008
Here is what I guess the equation would need to look like according to the last thing I said:
MIR%=(100*((SIGMA(J :0:SIZEC($):1 :MAX(FLOW($:J):0)*USFV(RSK%:#T($:J))
*SPFV(RSK%:SIGMA(L:J+1:SIZEC($):1:#T($:L))))/(MIN(FLOW($:0):0)
+SIGMA(J :1:SIZEC($):1 :MIN(FLOW($:J):0)*USPV(SAF%:#T($:J))
*SPPV(SAF%:SIGMA(L:1:J1:1:#T($:L))))))
^INV(SIGMA(L:1:SIZEC($):1:#T($:L))))1
I think I tried this earlier and got bogus results, probably because of the INV() function that is introducing errors, But I will try it again.
Edited: 7 Oct 2008, 11:36 p.m. after one or more responses were posted
▼
Posts: 18
Threads: 2
Joined: Oct 2008
nope, it didn't work. bogus result
Edited: 7 Oct 2008, 11:39 p.m.
