TVM equation principles Message #21 Posted by John H Meyers on 9 Aug 2007, 5:31 a.m., in response to message #20 by John H Meyers
This long-winded excursion explores the merits of different equations
upon which a TVM solver could be based,
if instead of the closed-form solutions available for all variables except interest,
we seek a single suitable formula to use with numeric root-hunting algorithms,
to solve all cases of the common five-variable TVM application.
Some principles to consider:
o Reasonable initial guesses should always send the root hunter
in the right direction, to locate a true and meaningful root,
for all "shapes" of problems, initial guesses, and when solving for any variable.
Suggested formulas of general type 'f(n,i%yr,PV,PMT,FV,PYR,Begin?)=0'
often differ from one another by having both sides multiplied
or divided by some expression -- the right side, of course,
always remaining zero under these transformations.
Sometimes we see the FV term having no other variables
in its coefficient,
and sometimes the PV term is chosen
to have the honor instead;
each of these has a characteristic
which is particularly good
if the cash flows are "loaded"
towards one end or the other of the time line,
but may turn out disadvantageously in an oppositely "loaded" case.
Solving for N can also produce a surprise, when for some initial guesses,
the shape of the entire function, plotted vs. N, may send the numeric solver
off in the wrong direction, heading away from the real answer,
flying out towards infinity in a vain effort to make the left side zero.
Among the versions of this formula which I have tried,
the best resistance to that effect in all collected problems I could throw at it
was obtained when the coefficient of the "middle" PMT term
(the "cash flow over the central region of the problem")
was made independent of i (at least when payments occur at end of periods),
rather than the very first (PV) or very last (FV) term,
in which case the PV and FV terms each have coefficients involving "i",
remaining in the ratio (1+i)^n to one another,
as they always are anyway, in all forms of the equation.
But in this form of the equation, unlike some other forms,
there is a certain resemblance of these "end region" terms to one another,
and anomalies involving poor initial guesses seem to be reduced,
for cases that have the largest cash flow "at the wrong end"
for whichever "unbalanced" equation was otherwise chosen.
A final tweak which seemed to improve solving for N
was to multiply the entire equation by N,
which tends to discourage the numeric solver from getting attracted
to exploring distant galaxies when solving for N :)
o It's useful to confine the search for an interest rate
to values which are logically possible, which are i > -100%
(there is no limit in the positive direction).
However, the numeric solver isn't conscious of this,
and can at times wander into an alternate financial universe itself,
announcing -157%, say, as a mathematically correct (but meaningless) answer.
If a new variable is introduced, say j=LN(1+i),
and then the equation is expressed in terms of j,
the legitimate range for j is now from
-infinity to +infinity,
which neatly and perfectly re-maps the range for j
Another rather elegant feature of this remapping is that if you were
to run time backwards,
exactly reversing the sequence of cash flows of an original problem,
the result is still a valid cash flow sequence,
in which j simply changes sign (and payments flip between "begininning"
vs. "end" of their respective periods), making the problem symmetrical.
A very small rounding error may have to be accepted as a trade-off
for some of these strategies, in return for other forms of robustness
in the ability of the overall algorithm to take on all conceivable cases.
o Cases involving large N and small I present a special challenge to accuracy.
In those cases, directly computing such expressions as '(1+i)^n-1'
tends to lose precision,
because significant digits drop off immediately when adding 1 to small i,
and subtracting 1 at the end may also cause another loss of significance
(also keep in mind that 'i' can legitimately have negative values,
producing different extreme ranges for the final result).
Rewriting '(1+i)^n-1' as 'EXPM(n*LNP1(i))' and
'1-(1+i)^-n' as '-EXPM(-n*LNP1(i))' avoids that loss of significance,
whenever the given calculator implements these as built-in functions,
which are mutually inverse and pass thru the origin (0,0);
this technique was apparently introduced way back at the time
of the HP22 financial model, although the EXPM and LNP1 functions
were not then made directly available to the user.
Note also that with the above-suggested change of variable, we have
j=LNP1(i), '(1+i)^n-1=EXPM(n*j)', '1-(1+i)^-n=-EXPM(-n*j)' and i=EXPM(j),
giving the equation complete symmetry, in the fact that
a reversal of time corresponds to a change in the sign of j,
whose valid range remains -infinity to +infinity.
If anyone would like to try out various equations on a "breadboard rig,"
before investing in detailed implementations:
@ Testing equations on HP48/49/50 (ignoring the built-in TVM solver :)
@ TVM Equation in terms of I (valid only I > -100%)
@ favoring linear middle term and balanced end terms:
'(PV*I/-EXPM(-N*LNP1(I))+PMT*(1+B?*I)+FV*I/EXPM(N*LNP1(I)))*N'
'TVMI' STO
@ More symmetrical version in terms of J (-infinity to +infinity)
@ 'EXPM(J)=EXP(J)-1' for calcs not having built-in EXPM
'(PV*EXPM(J)/-EXPM(-N*J)+PMT*(1+B?*EXPM(J))+FV*EXPM(J)/EXPM(N*J))*N'
'TVMJ' STO
@ Sample problem (B?=1 if payments occur at beginning of periods)
{ -136 50 75 3 0 } { PV PMT FV N B? } STO
TVMI 'I' 1 ROOT @ 0.25 is correct answer
TVMJ 'J' 1 ROOT EXPM @ 0.25 is correct answer
1 'B?' STO TVMI 'PMT' DUP RCL ROOT @ 40 (payment at beginning)
@ Simple solver menu (using equation in terms of I):
TVMI { { N I PV PMT FV B? } } + STEQ 30 MENU HALT
@ continue via CONT
@ In terms of Uniform Series Present/Future Value (like HP17b/19b)
'USPV(N,I)=-EXPM(-N*LNP1(I))/I' DEFINE
'USFV(N,I)=EXPM(N*LNP1(I))/I' DEFINE
@ If 'EXPM(J)=EXP(J)-1' 'LNP1(I)=LN(1+I)' aren't built-in, then
@ 'USPV(N,I)=(1-(1+I)^-N)/I'
@ 'USFV(N,I)=((1+I)^N-1)/I'
{ '(PV/USPV(N,I)+PMT*(1+B?*I)+FV/USFV(N,I))*N' @ TVM equation!
{ N I PV PMT FV B? @ This menu includes USPV USFV keys
{ "USPV" \<< N I USPV \>> }
{ "USFV" \<< N I USFV \>> } } } STEQ 30 MENU HALT
@ continue via CONT
@ MES solver menu (to solve symmetrical "J" equation first)
TVMJ { 'I=EXPM(J)' } + STEQ 63 SF @ Rshift changes var state
MINIT "TVM" { N I PV PMT FV B? J } MITM MSOLVR @ menu order
@ Use Rshift with ALL menu key to see how solved.
@ If the "MES" solver is unfamiliar,
@ a manual may be need to be consulted.
Modern HP financial calculators, as you know,
actually have a slightly different set of variables in their TVM menu,
consisting of N, I%YR, PV, PMT, FV, PYR and BEG[in] mode,
where I%YR is the annual interest rate in percent
and PYR is the number of payments per year,
so that what we were calling I is actually '(I%YR/PYR)/100';
I use this same set of values in my HP32Sii and HP42S formulas,
of course having to choose non-mnemonic single letters on 32Sii
to distinguish Payment from Present value, and from PYR :)
I was going to append my HP32Sii formula and HP42S program,
but I can't seem to find where I wrote them down,
so I'll post this now, and perhaps come back later,
if ever I do find where I hid those listings...
--
Edited: 9 Aug 2007, 5:54 a.m.
|