Solving the TVM equation for the interest rate

04152018, 08:39 AM
Post: #1




Solving the TVM equation for the interest rate
Looking at the recent contributions to the General Software Library made me once again think about solving the classic TVM equation for the interest rate. Since there is no closed solution an iterative method is required, for instance the classic Newton method. This requires an initial guess for the interest rate, and I wonder if there is some kind of "best practice" to do so.
Some time ago this has been discussed in the old forum. Maybe you want to take a look at this thread. But maybe we don't have to reinvent the wheel: I wonder how the algorithms in existing financial calculators do it. So if anyone has some insights on this please let the world know. Then I have been looking at various programs for programmable calculators:  The TVM program in the HP41 Standard Pac starts with + or – 1E–9.  The Advantage ROM seems to simply take 1/n as the initial guess (please correct me if I'm wrong here). This seems to work surprisingly well. The guess may be far from the final result, but it converges well from there. At least in the examples I tried and for i>0.  The TI58/59 Master Library program (ML19) takes a slightly different approach (it distinguishes between PV and FV problems) and starts with dedicated guesses, for instance with FV/PMT – PMT/(FV·n²) for PV=0.  I remember I saw a flowchart of the HP80 algorithm somewhere in an old HP Journal. Maybe this has some information on how the HP80 did it.  The 12C... no idea. I just tried this on the "official" HP 12C emulator: CLEAR FIN 10 [n] –1000 [PMT] 10000 [FV] [i] returns exactly 0, as expected. Change FV: 15000 [FV] [i] yields 8,732% Change FV back to the previous value: 10000 [FV] [i] now returns 4,334 E–11% OK, this is sufficiently close to zero, but why does this return a different result than before where it was exactly zero? 12000 [FV] [i] => 3,989% 10000 [FV] [i] => 2,146 E–11% 10000 [FV] [i] => 0% So the result seems to depend on previous input ?! All in all:  What formulas would you suggest to set an initial guess for calculating the interest rate?  What is going on in the 12C (see above)? Dieter 

04152018, 10:46 AM
(This post was last modified: 04152018 11:00 AM by Gamo.)
Post: #2




RE: Solving the TVM equation for the interest rate
I was wondering on how HP doing this TVM on finding the interest [i] also.
I'm happen to read more about this in HP Journal Issue October 1977 start from page 22 in the article state that the previous HP80 Financial Calculator used 3 separate TVM formula which their performance deteriorated unacceptably when they were applied to the new more general equation. On the newer desktop model the HP92 used better general equation using one formula all together and did work out very well. This topic is very complicated for me and I like to share this information to anyone who like to know about this especially to Dieter. I try to implement this single TVM equation into the Newton's Method program by using Register as their TVM variables like STO 1 for [n] STO 2 for [I] and so on and not working yet. Link to this HP Journal: https://www.dropbox.com/s/6beqloidryfdim...7.pdf?dl=0 Gamo 

04152018, 02:55 PM
(This post was last modified: 04152018 02:56 PM by Dieter.)
Post: #3




RE: Solving the TVM equation for the interest rate
(04152018 10:46 AM)Gamo Wrote: I was wondering on how HP doing this TVM on finding the interest [i] also. As far as I can tell the HP92 got the more universal TVM equation as we know it today. The 1977 article says that solving for the interest rate uses the Newton method based on an initial guess which is accurate to 5 decimal places (!). But there is no clue on how they did this. (04152018 10:46 AM)Gamo Wrote: Link to this HP Journal: https://www.dropbox.com/s/6beqloidryfdim...7.pdf?dl=0 Here is a link to all available HP Journals on the HP website. The mentioned HP80 article can be found in the May 1973 issue. There even is a flowchart, but with a wrong description of how the initial interest rate is calculated. Appendix A on page 8 has a different formula that looks better. There even is a more or less complete description of the interest rate algorithm (if PV is given an FV=0). Note that the HP80 did not use the sign convention of later HP financial calculators. Dieter 

04152018, 04:37 PM
Post: #4




RE: Solving the TVM equation for the interest rate
I looked into the very same subject when I wrote the HP41 MCODE version of TVM$.
I don't recall exactly how or why I came to this, but the expression I used for the initial guess was: i0 = [ abs( PV + n*PMT + FV) ]^1/n Surely this isn't the best, or may be not even sensible  but it works just fine for all tests and still holding up nicely. 

04152018, 05:30 PM
Post: #5




RE: Solving the TVM equation for the interest rate
(04152018 08:39 AM)Dieter Wrote: So the result seems to depend on previous input ?! I believe you are right Dieter. Try this on your HP12C emulator. CLEAR FIN n = 10 PMT = 1,000 FV = 10,000 i = 0.001 If you solve for i, the answer is 2.152976E11, rather than 0 when you leave the value of i at 0. This proves that since we changed the initial value in i from 0 to 0.001, it changed the result. It's almost like it uses the value in the i register as a initial guess. Just as you concluded before. I also took the equation of solving TVM problems without an odd period on page 186 of the April 2008 user's guide and played around with it on my HP 50g and HP Prime. The equation is attached to this post. The Prime had to chime in because without a proper initial guess, the 50g would take minutes to solve for i. Also, without an initial guess, the Prime and 50g would get a really big number for a solution. I guess I learned why you needed a initial estimate. To answer your first question, I think the 1/n estimate would be fine. Every time I used the equation to solve for i on my 50g and Prime solver, the initial guess of 1/n worked FANTASTIC. I was able to solve for i in seconds on my 50g with that initial estimate of 1/n. I also tried Angel Martin's initial guess equation on my 50g and Prime and it works too. I just had to divide it by 100 to get the percentage as a decimal. I hope this helps. 

04152018, 06:08 PM
Post: #6




RE: Solving the TVM equation for the interest rate
(04152018 05:30 PM)Carsen Wrote: If you solve for i, the answer is 2.152976E11, rather than 0 when you leave the value of i at 0. This proves that since we changed the initial value in i from 0 to 0.001, it changed the result. It's almost like it uses the value in the i register as a initial guess. Just as you concluded before. I can't say which of the previous values influences the new result, but at least one of them seems to do so. (04152018 05:30 PM)Carsen Wrote: The Prime had to chime in because without a proper initial guess, the 50g would take minutes to solve for i. There always is some kind of initial guess  the solver has to start somewhere. But if you don't give one and/or the solver does not set one on its own, the results can be random. (04152018 05:30 PM)Carsen Wrote: To answer your first question, I think the 1/n estimate would be fine. Every time I used the equation to solve for i on my 50g and Prime solver, the initial guess of 1/n worked FANTASTIC. I was able to solve for i in seconds on my 50g with that initial estimate of 1/n. I now have written an experimental version for the 11C / 15C and added this to the thread started by Gamo. The initial guess is calculated in a way I derived some time ago, cf. the formula at the end of this post. Could you also try this one and report the results? Dieter 

04152018, 08:48 PM
Post: #7




RE: Solving the TVM equation for the interest rate
Problem #1: n=32 PV=6,000 FV=10,000 PMT=0 i=???
Answers: 12C=1.609139492% 15C=1.609139501% Prime=1.60913949196% Problem #2: n=36 PV=13,000 PMT=372.53 FV=0 i=??? Answers: 12C=0.1692576094% 15C=0.1692569782% Prime=0.169257604168% Problem #3: n=36 PV=5,750 PMT=176.89 FV=0 i=??? Answers: 12C=0.56266017270% 15C=0.5626013555% Prime=0.562601726101% Problem #4: n=360 PV=75,000 PMT=425.84 FV0 i=??? Answers: 12C=0.458302324% 15C=0.4583302039% Prime=0.458330232637% The 12C results is from my 12C bought in 2016. The 15C results is your (Dieter's) program. The Prime's results is using the TVM formula in the 12C manual using your initial estimate formula. Looking at the 15C and the Prime's results, I would say that the estimate works really well. I wonder if there is a even better way to produce a estimate. 

04152018, 09:38 PM
(This post was last modified: 04152018 09:41 PM by Dieter.)
Post: #8




RE: Solving the TVM equation for the interest rate
(04152018 08:48 PM)Carsen Wrote: Problem #1: n=32 PV=6,000 FV=10,000 PMT=0 i=??? First of all: thank you for the time and effort you spent on this. However, the quality of the estimate cannot be judged from the final result: this depends on the numerical accuracy during the iteration and its implementation. Since the 12C calculates with 13 digits internally and because it also has means for exact evaluation of e^x–1 and ln(1+x) the results from this calculator are inherently more accurate. The relevant figure for the quality of the initial estimate here is the number of required iterations. I have included a loop counter in my 15C version and it looks like the results are quite good: Problem 1: n=3 iterations, estimate was 1,57%, result is 1,61%. Problem 2: the guess is very good (0,171% vs. 0,169% exact) and already after the second loop the absolute error is less than 2E–8. So once again one should expect the error to drop below the given threshold after 3 iterations. But in this case the Newton algorithm finally has problems to get the desired number of significant digits, so it takes 11 iterations. This nicely shows the numeric pitfalls mentioned above. But this is not related to the initial guess. Problem 3: n=3 iterations, estimate was 0,58%, result is 0,56%. Problem 4: n=4 iterations, estimate was 0,58%, result is 0,46%. (04152018 08:48 PM)Carsen Wrote: I wonder if there is a even better way to produce a estimate. Compare it with the number of required loops based on other initial guesses. ;) Dieter 

04162018, 03:44 AM
Post: #9




RE: Solving the TVM equation for the interest rate
The initial guess using the formula given by Dieter is really a good one ....
Interesting would be to generalize to an uneven cashflow ... Andi 

04162018, 05:00 AM
Post: #10




RE: Solving the TVM equation for the interest rate
(04152018 04:37 PM)Ángel Martin Wrote: I looked into the very same subject when I wrote the HP41 MCODE version of TVM$. There is somewhere in the calculator literature a very good approximation. I don't remember where. I spent some time trying to figure out how it worked and finally realized that it was a continued fraction approximation to the solution. It's only got 5 or 6 terms and this suitable as a starting point. The problem is that it does not tell you whether there is a solution for the given input. There can be 0, 1, 2 or more interest rates that generate the same income stream for given starting values. 

04162018, 06:34 PM
Post: #11




RE: Solving the TVM equation for the interest rate
(04152018 09:38 PM)Dieter Wrote: Problem 2: the guess is very good (0,171% vs. 0,169% exact) and already after the second loop the absolute error is less than 2E–8. So once again one should expect the error to drop below the given threshold after 3 iterations. But in this case the Newton algorithm finally has problems to get the desired number of significant digits, so it takes 11 iterations. This nicely shows the numeric pitfalls mentioned above. But this is not related to the initial guess. The major reason for this behaviour is the relative (!) error threshold in the 11C/15C program. Due to digit cancellation especially for small interest rates this target sometimes is not met as the true calculated error is inexact due to roundoff errors. So i has up to 9 decimals, but the number of significant digits decreases as i gets close to zero. That's why I'd suggest switching to an absolute error check, i.e. simply remove line 78 RCL 2 and 79 / from the code. This way the iteration converges after merely 2 loops. Please also note my remarks in post #9 of the linked thread. Dieter 

04172018, 08:59 AM
(This post was last modified: 04172018 12:29 PM by Gamo.)
Post: #12




RE: Solving the TVM equation for the interest rate
In the HP11C Solutions Handbook got one program to solve for Interest given [n] [PMT] [PV]
This show the Formula and Initial guess used as well. Take a look at the attached file. Is this a better initial guess? Using the example problem from Carsen I put program from 11C Solutions Handbook to HP15C official HP emulator for Android and here the result. Problem #2: n=36 PV=13,000 PMT=372.53 FV=0 i=??? Answers: 12C=0.1692576094% 15C=0.1692569782% Prime=0.169257604168% *From 11C handbook program: 0.1692572638% Problem #3: n=36 PV=5,750 PMT=176.89 FV=0 i=??? Answers: 12C=0.56266017270% 15C=0.5626013555% Prime=0.562601726101% *From 11C handbook program: 0.5626017349% Problem #4: n=360 PV=75,000 PMT=425.84 FV0 i=??? Answers: 12C=0.4583302324% 15C=0.4583302039% Prime=0.458330232637% *From 11C handbook program: 0.4583302172% Gamo 

04172018, 06:50 PM
(This post was last modified: 04172018 06:59 PM by Dieter.)
Post: #13




RE: Solving the TVM equation for the interest rate
(04172018 08:59 AM)Gamo Wrote: In the HP11C Solutions Handbook got one program to solve for Interest given [n] [PMT] [PV] Here the estimate is PMT/PV – PV/PMT/n² (where PV and PMT follow the sign convention). This seems to work well, but it implies that FV is zero so that only PMT and PV are considered. The mentioned program was written for mortgage loans as a special case of the TVM equation. Dieter 

04192018, 02:56 PM
(This post was last modified: 04192018 03:29 PM by Jeff_Kearns.)
Post: #14




RE: Solving the TVM equation for the interest rate
(04152018 08:48 PM)Carsen Wrote: Problem #1: n=32 PV=6,000 FV=10,000 PMT=0 i=??? When I try using a standard initial estimate of 0.001% for the interest rate for each of the above examples with the HP15C TVM program (40 lines in length), I get the following results. I then compared them to the HP19BII (reference result), and the HP29C (71 line adaptation using Stefan Vorkoetter's Equation Solver for the HP19C): Problem #1: n=32 PV=6,000 FV=10,000 PMT=0 i=??? Answers: 15C=1.609139493%; 19BII=1.60913949223%; 29C=1.609139493 Problem #2: n=36 PV=13,000 PMT=372.53 FV=0 i=??? Answers: 15C=0.169257612%; 19BII=0.16925760942%; 29C=0.169257597 Problem #3: n=36 PV=5,750 PMT=176.89 FV=0 i=??? Answers: 15C=0.562601729%; 19BII=0.56260172701%; 29C=0.562601732 Problem #4: n=360 PV=75,000 PMT=425.84 FV0 i=??? Answers: 15C=0.458330232%; 19BII=0.45833023240%; 29C=0.458330233 I actually use the HP15C LE instead of the 15C since it is much, much faster ;). The 15C program is located here. The 29C program can be found here. For the fourth example, I initially got an error on the 29C, but then I simply changed the estimate range from [0.001% to 0.2%] and got a satisfactory answer. The 15C program is an adaptation of the Pioneer's (42S/35S/33S/32Sii/32S) Accurate TVM routine, modified using Karl Schneider's technique for invoking SOLVE with the routine written as a MISO (multipleinput, singleoutput) function, using indirect addressing. As I recall, Nick stated back in August 2016 that the program fails in special cases where I = 0, but then why use a TVM program at all when there is no interest rate? TVM implies some interest, after all. I have found this program easy to use with mnemonics to remember the storage registers, and quite useful for my purposes. The mnemonics are kind of silly (No Interest Before Payment of Funds; where N=STO 1, I=STO 2, B or PV=STO 3, P=STO 4, and F=STO 5) but they are easy to remember. The 29C program also works on the 11C. I may be missing the point of this thread, and apologize if that is the case. If it is, can someone please explain the issue. Jeff 

04192018, 09:45 PM
(This post was last modified: 04192018 09:59 PM by Dieter.)
Post: #15




RE: Solving the TVM equation for the interest rate
(04192018 02:56 PM)Jeff_Kearns Wrote: The 15C program is an adaptation of the Pioneer's (42S/35S/33S/32Sii/32S) Accurate TVM routine, ... If I remember correctly this implies the use of ln(1+x) and exp(x)–1 for a more correct calculation without digit cancellation. (04192018 02:56 PM)Jeff_Kearns Wrote: I may be missing the point of this thread, and apologize if that is the case. If it is, can someone please explain the issue. Simply read the first post. ;) The discussion is about the way an initial estimate for the iterative interest rate calculation is determined. It should be reasonably close to the true result and lead to the final solution in not too many iterations. For the FV=0 case (cf. the above mentioned program in the 11C manual) an estimate of PMT/PV – PV/PMT/n² was proposed. This indeed is a good first guess for this particular case, as it has two nice properties: First, if –FV = n·PMT the interest rate is zero, and in this case the estimate is zero as well. Second, as n gets larger and eventually approaches infinity, the interest rate approaches the limit PMT/PV, which is also true for the estimate. Between these two limits the estimate seems reasonably close to the true result (at least in the cases I tried), so the estimate works well for interest rates ≥ 0. However, in cases where the interest rate is negative the estimate can be significantly off. But at least the first one or two iterations usually get it back on track. So this works quite well for this particular case where FV=0 and i≥0. On the other hand, the estimate I proposed is more universal as it considers both PV and FV. If I did the math correctly, it agrees with the true result at i=0 and n=1. As n increases the estimate is slightly high, but close, and it seems works very well. For very large n this estimate finally approaches 2x the true result. Maybe this can be improved by tweaking the formula a bit. Dieter 

« Next Oldest  Next Newest »

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