Solving the TVM equation for the interest rate

[Dieter]

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 (ML-19) 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

[Gamo]

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 HP-80 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 HP-92 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

[Dieter]

(04-15-2018 10:46 AM)Gamo Wrote:  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 HP-80 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 HP-92 used better general equation using one formula all together and did work out very well.

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.

(04-15-2018 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

[Ángel Martin]

I looked into the very same subject when I wrote the HP-41 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.

[Carsen]

(04-15-2018 08:39 AM)Dieter Wrote:  So the result seems to depend on previous input ?!

I believe you are right Dieter. Try this on your HP-12C emulator.

CLEAR FIN
n = 10
PMT = -1,000
FV = 10,000
i = 0.001

If you solve for i, the answer is 2.152976E-11, 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.

[Dieter]

(04-15-2018 05:30 PM)Carsen Wrote:  If you solve for i, the answer is 2.152976E-11, 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.

(04-15-2018 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.

(04-15-2018 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 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 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

[Carsen]

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 FV-0 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.

[Dieter]

(04-15-2018 08:48 PM)Carsen Wrote:  Problem #1: n=32 PV=-6,000 FV=10,000 PMT=0 i=???
Answers: 12C=1.609139492% 15C=1.609139501% Prime=1.60913949196%
...
Looking at the 15C and the Prime's results, I would say that the estimate works really well.

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

(04-15-2018 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

[AndiGer]

The initial guess using the formula given by Dieter is really a good one ....
Interesting would be to generalize to an uneven cashflow ...
Andi

[ttw]

(04-15-2018 04:37 PM)Ángel Martin Wrote:  I looked into the very same subject when I wrote the HP-41 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.

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.

[Dieter]

(04-15-2018 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

[Gamo]

In the HP-11C 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 HP-15C 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 FV-0 i=???
Answers: 12C=0.4583302324% 15C=0.4583302039% Prime=0.458330232637%
*From 11C handbook program: 0.4583302172%

Gamo

[Dieter]

(04-17-2018 08:59 AM)Gamo Wrote:  In the HP-11C Solutions Handbook got one program to solve for Interest given [n] [PMT] [PV]
This show the Formula and Initial guess used as well.

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

[Jeff_Kearns]

(04-15-2018 08:48 PM)Carsen Wrote:  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 FV-0 i=???
Answers: 12C=0.458302324% 15C=0.4583302039% Prime=0.458330232637%

When I try using a standard initial estimate of 0.001% for the interest rate for each of the above examples with the HP-15C TVM program (40 lines in length), I get the following results. I then compared them to the HP-19BII (reference result), and the HP-29C (71 line adaptation using Stefan Vorkoetter's Equation Solver for the HP-19C):

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 FV-0 i=???
Answers: 15C=0.458330232%; 19BII=0.45833023240%; 29C=0.458330233

I actually use the HP-15C 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 (multiple-input, single-output) 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

[Dieter]

(04-19-2018 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.

(04-19-2018 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

[Gamo]

The HP-55 Mathematics Programs book got two type of TVM program to find the Interest rate.

Direct Reduction Loan: Interest Rate on page: 48

Sinking Fund: Interest Rate on page: 60

----------------------------------------------

Sinking Fund Interest Rate program also provided the Suggested Guess routines.

This doesn't provide the formula used in this program but I traced the suggested guess
routine and got the formula.

Suggested Guess Formula:

[((FV/PMT) - n) x 2] ÷ [(n - 1)^2 + (FV/PMT)]

----------------------------------------------
Example:

$40,000 to be accumulated in 11 years; annual payments of $2,564

The interest rate needed is approximately _____ %.

Using the Suggested Guest routine i = 0.0796

Answer: Interest Rate = 0.0678 or 6.78%

Remark:
HP-11C took about 20 seconds
HP-12C took about the same time as 11C so
I'm wondering that the 12C might use this suggested guess routine.


Gamo 2/2020

[Albert Chan]

(02-12-2020 10:50 AM)Gamo Wrote:  Suggested Guess Formula:

[((FV/PMT) - n) x 2] ÷ [(n - 1)^2 + (FV/PMT)]

----------------------------------------------
Example:

$40,000 to be accumulated in 11 years; annual payments of $2,564

The interest rate needed is approximately _____ %.

Using the Suggested Guest routine i = 0.0796

Answer: Interest Rate = 0.0678 or 6.78%

I found that harmonic mean of two extreme guesses gives good rate estimate (M=PMT, F=FV, P=PV)

I1=(F+P+M*N) / (N*((1-N)/2*M - P))
I2=(F+P+M*N) / ((N-1)/2*(F-P) - P)

I1 assumed no compounding, thus over-estimate the rate, by quite a bit.
I2 over-estimated compounding effect, thus under-estimated rates, is better estimate than I1.
For how it is derived, see https://www.hpmuseum.org/forum/thread-14...#pid125408

For above example, N=11, F=40000, P=0, M=-2564

I1 = 11796/141020 ≈ 8.365%
I2 = 11796/200000 ≈ 5.898%

I ≈ 2/(1/I1 + 1/I2) = 11796/((141020+200000)/2) = 11796/170510 ≈ 6.918%, over-estimated 0.138%

Or, we can skip I1,I2: I ≈ 4*(F+P+M*N)/((N-1)*(F-M*N)-(3*N+1)*P)

[ttw]

I used to have a continued fraction approximation for interest rate (from an old finance book whose title I no longer remember.) It was quite accurate over a very wide range of initial conditions. I'll see if I can find it. (None of the newer texts had this formula.) I'll try the Internet Archive to see if I can find the book.

One method we used to use was to figure out the rate assuming simple interest then adjust by some factor (like dividing by 1.5 or 2.0). This made a pretty good starting point. There are input values which do not have a real solution for the interest rate though. One could also replace Newton's method with something more robust like the secant or even binary interval halving for the first few steps.

[Albert Chan]

This formula is based on Pade[1,1], I centered 0, of NFV = F + P + ((1+I)^N-1)*(P+M/I)

Solving Pade[1,1] approximated NFV = 0, for 1/I :

\( \Large {1\over I} ≈
{\binom{N}{3}M + \binom{N}{2}P \over \binom{N}{2}M + \binom{N}{1}P} -
{\binom{N}{2}M + \binom{N}{1}P \over F + P + M N}\)

Doing the same example, N=11, F=40000, P=0, M=-2564

1/I ≈ (-423060/-141020) - (-141020/11796) = 44102/2949

I ≈ 2949/44102 ≈ 6.687%, which under-estimated true rate (6.780%) by tiny 0.093%

[ttw]

(02-12-2020 05:25 PM)Albert Chan Wrote:  This formula is based on Pade[1,1], I centered 0, of NFV = F + P + ((1+I)^N-1)*(P+M/I)

Solving Pade[1,1] approximated NFV = 0, for 1/I :

\( \Large {1\over I} ≈
{\binom{N}{3}M + \binom{N}{2}P \over \binom{N}{2}M + \binom{N}{1}P} -
{\binom{N}{2}M + \binom{N}{1}P \over F + P + M N}\)

Doing the same example, N=11, F=40000, P=0, M=-2564

1/I ≈ (-423060/-141020) - (-141020/11796) = 44102/2949

I ≈ 2949/44102 ≈ 6.687%, which under-estimated true rate (6.780%) by tiny 0.093%

One could use this formula as an initial approximation to Newton's method or something else. A good initial approximation is necessary for all iterations and the your [1,1] Pade looks really good.