TVM solve for interest rate, revisited

[Albert Chan]

(11-20-2024 10:52 AM)Albert Chan Wrote:  f = c0 + sum((c1+k*x)/(r+1)^(x+1), x=0 .. n-1)
...
f = (K*(c0 r² + c1 r + k) - ((c1+k*n) r + k)) / (K r²)

From Ángel Martin's thread for IRR rate calculation, where c1 increase by step k each period.
If we add the fv term, above IRR calculations can solve TVM problem! (just set step k = 0)

Let's change variables name to match TVM: pv=c0, c1=pmt, step s=k, and add fv to the mix.

Code:

PV                      PMT           FV                        SCALE

pv*r^2 + (pmt*r+s)      0           fv*r² - s*n*r - (pmt*r+s)   1/r²                
pv*r^2               (pmt*r+s)*r    fv*r² - s*n*r               1/r²
pv                   (pmt+s/r)      fv - s*n/r                  1

K*PV + FV = 0      ; row 1
K = -FV/PV = -(fv*r² - s*n*r - (pmt*r+s)) / (pv*r^2 + (pmt*r + s))

If s = 0, formula reduced to plain TVM, we have:

K = (pmt - fv*r) / (pmt + pv*r) > 0

This explained why edge rate are [pmt/fv, pmt/-pv].
They are simply the roots for top and bottom!

Same idea for s≠0, only the edges may now have 2 roots, not 1

Just like we solve for plain TVM, we solve npmt=0, for rate
Just like we solve for plain TVM, we fit npmt (tiny rate) with a quadratic.

Code:

function npmt_step(n,s,pv,pmt,fv)
    return function(r)
        if abs(r) > 1e-6 then return npmt(n, r, pv, pmt+s/r, fv-n*s/r) end
        return pmt  - s * (1-n)/2   * (1 - (1+n)/6*r * (1 - r/2)) +
               (fv * (1 + (1-n)/2*r * (1 - (1+n)/6*r)) +
                pv * (1 + (1+n)/2*r * (1 - (1-n)/6*r))) / n
    end
end

lua> f = npmt_step(5, 5, -25, 10, 0) -- quoted IRR example
lua> S.newton(f, 0.7, nil, nil, true)
0.7                                     0
0.5670681661803514          2
0.5672303344166845          4
0.5672303344358538          6

lua> f = npmt_step(36, 0, 30000, -550, -15000) -- car lease example
lua> S.newton(f, 0, nil, nil, true)
0                                        0
0.0058695853637202935     2
0.005805080703249694       4
0.0058050728194123016     6
0.005805072819420133       7
lua> _ * 1200 -- APR %
6.96608738330416

Update: npmt_step() with an extra term for pv,fv, emphasized symmetry.

Cas> K := exp(log1p(r)*n);   /* = (1+r)^n */
Cas> C := n*r / (1-1/K);       /* compounding factor */
Cas> series(C, r)

\(1+\frac{(1+n)}{2} \cdot r+\frac{(-1+n^{2})}{12} \cdot r^{2}+\frac{(1-n^{2})}{24} \cdot r^{3}+\frac{(-19+20\cdot n^{2}-n^{4})}{720} \cdot r^{4}+\frac{(9-10\cdot n^{2}+n^{4})}{480} \cdot r^{5}+r^{6} \mathrm{order\_size}\left(r\right)\)

Code:

function npmt_step(n,s,pv,pmt,fv)
    return function(r)
        if abs(r) > 1e-6 then return npmt(n, r, pv, pmt+s/r, fv-n*s/r) end
        return ((pv + fv + n*pmt) +
              (  pv*r    * (1+n)/2 * (1 - (1-n)/6*r * (1 - r/2))) +
              ((fv*r-n*s)* (1-n)/2 * (1 - (1+n)/6*r * (1 - r/2)))) / n
    end
end

(small rate first term)*n = pv + fv + n*pmt = pv + (fv-n*s/r) + n*(pmt+s/r)

[Albert Chan]

Previous post TVM with steps option, for HP71B (updated npmt_step())

Code:

10 DESTROY ALL @ INPUT "N,S,P,M,F= ";N,S,P,M,F
20 DEF FNM(N,I,P,M,F)=((P+F)/EXPM1(LOGP1(I)*N)+P)*I+M
30 DEF FNP(N,I)=(1+N)/2*(1-(1-N)*I*(2-I)/12)
40 DEF FNF(I) @ DISP I @ IF ABS(I)>.0001 THEN FNF=FNM(N,I,P,M+S/I,F-N*S/I) @ END
50 FNF=(P+F+N*M+P*I*FNP(N,I)+(F*I-N*S)*FNP(-N,I))/N @ END DEF
60 DEF FNI(A,B)=FNROOT(A,B,FNF(FVAR))
70 INPUT "GUESS I= ";I @ DISP FNI(I-.001,I+.001)

>run
N,S,P,M,F= 5,5,-25,10,0
GUESS I= 0.7
.699
.701
.567068171084
.633034085542
.567230379538
.567230334434
.567230334434

>run
N,S,P,M,F= 36,0,30000,-550,-15000
GUESS I= 0
-.001
.001
5.86958536385E-3
5.80447251183E-3
5.80507274543E-3
5.83732905464E-3
5.80507281939E-3
5.82120093702E-3
5.80507281943E-3
5.80507281943E-3
>res * 1200 ! APR %
6.96608738332

[Albert Chan]

I moved IRR calculations posts here, which is more TVM related.

(11-03-2024 08:02 AM)Ángel Martin Wrote:  Example: With C0=(250.000,00), C1 = 100.000,00, N=5
arithmetic, k=50.000,000 => IRR = 56.72%

We may avoid summing all the terms.

(10-27-2024 06:09 PM)Albert Chan Wrote:  \(\displaystyle
\sum_{x=0}^{\infty} \frac{x^\underline{n}}{(r+1)^{x+1}}
= \frac{n!}{r^{n+1} }
\)

Let r = IRR rate, K=(r+1)^n, we solve IRR formula, f=0 for r

f = c0 + sum((c1+k*x)/(r+1)^(x+1), x=0 .. n-1)
  = c0 + sum((c1+k*x)/(r+1)^(x+1), x=0 .. ∞) - sum((c1+k*x)/(r+1)^(x+1), x=n .. ∞)
  = c0 + sum((c1+k*x)/(r+1)^(x+1), x=0 .. ∞) - sum((c1+k*(y+n))/(r+1)^(y+1), y=0 .. ∞) / K
  = c0 + (c1/r + k/r²) - ((c1+k*n)/r + k/r²) / K

f = (K*(c0 r² + c1 r + k) - ((c1+k*n) r + k)) / (K r²)

numerator = 0 implied f = 0, we solve for K to get an idea where IRR is

K = ((c1+k*n) r + k) / (c0 r² + c1 r + k)

Let's shrink OP example dollar amount by factor 1e4

lua> c0, c1, k, n = -25, 10, 5, 5
lua> -c1/(2*c0), sqrt(c1^2-4*c0*k)/(2*c0)
0.2      -0.48989794855663565       -- denominator positive when r within 0.2 ± 0.5
lua> -k/(c1+k*n)
-0.14285714285714285                  -- numerator positive when r > -0.142857...

K>0 --> r = -0.14 .. 0.70

---

We can solve f=0 using TVM function

f = ((c0 r² + c1 r + k) - ((c1+k*n) r + k)/K) / r²
f = c0 + (c1+k/r) * (1-1/K)/r + (-k/r*n) / K

Compare this to NPV function, f = NPV

NPV = PV + PMT * (1-1/K)/r + FV / K

It is faster and more stable to solve npmt = 0 than NPV = 0

lua> EFF = fn'r,n: expm1(log1p(r)*n)' -- = (1+r)^n - 1
lua> npmt = fn'n,r,pv,pmt,fv: ((pv+fv)/EFF(r,n)+pv)*r + pmt'

lua> f2 = fn'r,z: z=k/r; npmt(n, r, c0, c1+z, -z*n)'
lua> S.newton(f2, 0.7, nil, nil, true)
0.7                                 0
0.5670681661803514      2
0.5672303344166845      4
0.5672303344358538      6

Turns out rate guess analysis is a waste. f2 look like a striaght line!

plot -25*(r+1)/((1+r)^5-1) - 25*r + (10+5/r), r = 0 .. 1

---

We can also match directly with NPV, with longer but equivalent formula (≡ f2)

lua> f3 = fn'r: npmt(n, r, k+r*(c1+r*c0), 0, -k-r*(c1+k*n)) / (r*r)'
lua> S.newton(f3, 0.7, nil, nil, true)
0.7                                 0
0.5670681661803325      2
0.5672303344166845      4
0.5672303344358538      6

Note: f3(0) = npmt(n,0,k,0,-k) / 0² = 0/0 = nan

We had to do taylor series to figure out IRR for r→0

n*f3(r→0) = (c0 + c1*n + k*n*(n-1)/2)
                + (c0*(n+1)/2 - k*n*(n^2-1)/12) * r
                + (c0*(n^2-1)/12 + k*n*(n^2-1)/24) * r^2
                + ...

Constant term is really (PV + n*PMT + FV). If it is 0, solution is r=0.
Start from r=0, below is rate for next Newton step. (very good estimate if r is small)

lua> -(c0 + c1*n + k*n*(n-1)/2) / (c0*(n+1)/2 - k*n*(n^2-1)/12)
0.6
lua> S.newton(f3, 0, nil, nil, true)
0                                    0
0.5999999422985695      2
0.5672257610449343      4
0.5672303344382047      6
0.5672303344358538      7

S.Newton's have no trouble with f(x0=0), because it only use f(x0±h) points.

Note 2: math for valid intervals may not a waste.
It is possible IRR have 2 roots (need 2 guesses), if c1 and k have opposite sign.

[Albert Chan]

I moved IRR calculations posts here, which is more TVM related.
This is to give a reasonable rate guess for previous post.

FV numbers can be moved to PV, if we are willing to paid interest to compensate.

[PV, PMT, FV] ≡ [PV, PMT, FV] + [FV, -FV*I, -FV] = [PV+FV, PMT-FV*I, 0]

Code:

PV                      PMT           FV                    SCALE
c0*r^2 + (c1*r+k)       0           -k*n*r - (c1*r+k)       1/r²                
c0*r^2               (c1*r+k)*r     -k*n*r                  1/r²
c0                    c1+k/r        -k/r*n                  1

Here are more complicated rules for transfer amount among above 3 variables
Let K = (1+I)^N
Let D = C(N=-N) = N*I/(K-1)

NPMT = C*PV + N*PMT + D*FV = 0

Divide by (N*I), we have:

PV/(1-1/K) + PMT/I + FV/(K-1) = 0

Example, if we transfer PMT term (k/r) to FV
FV/(K-1) = -PMT/I      → FV = -PMT * (K-1)/I

FV = -k/r*n + (k/r) * (K-1)/r = (K-1 - r*n)/r^2 * k

Just to confirm math.

lua> c0, c1, k, n = -25, 10, 5, 5
lua> f7 = fn'r: npmt(n, r, c0, c1, (EFF(r,n)-r*n)/(r*r)*k)'
lua> S.newton(f7, 0.7, nil, nil, true)
0.7                                 0
0.5670681661803387      2
0.5672303344166845      4
0.5672303344358538      6

We push the mess to FV in order to get good estimate for rate.

FV(r→0) = n*(n-1)/2 * k      -- incremental payments all moved to FV

lua> tvm(n, nil, c0, c1, n*(n-1)/2*k)      -- minimum IRR estimate
0.4788459989003278

For maximum IRR estimate, we keep last incremental payment the same.
The rest = n*z, we spread out to payments, each payment gained z

FV' + last payment = c(n) - c(1)
(n*(n-1)/2*k - n*z) + z = (n-1) * k
n*(n-1)/2*k = (n-1) * (k+z)

--> z = (n/2-1) * k
--> FV' = ((c(n) - c(1)) - z = (n-1)*k - (n/2-1)*k = n/2 * k

lua> tvm(n, nil, c0, c1 + (n/2-1)*k, n/2*k) -- maximum IRR estimate
0.6721638276116096

IRR = 0.478846 .. 0.672164 ≈ 0.5755 ± 0.0967

If we use 1 tvm calculation to guess IRR, I would split (n*z) 50:50, to aim for center.

FV'' = FV' + (n*z)/2 = n/2*k + n/2*k*(n/2-1) = (n/2)^2 * k

lua> tvm(n, nil, c0, c1 + (n-2)/4*k, n*n/4*k) -- IRR estimate
0.5667853993026831

Or, we can use simple edge rate PMT/-PV

IRR ≈ (c1 + (n-2)/4*k) / -c0 = (10 + 3.75) / 25 = 0.55

[Albert Chan]

I moved IRR calculations posts here, which is more TVM related.

(11-03-2024 08:02 AM)Ángel Martin Wrote:  LBL "IRR*" for the geometric: Cn = C1* (1+k)^(n-1)

Example: With C0=(250.000,00), C1 = 100.000,00, N=5
geometric, k=0.05 (5%) => IRR = 32.50%

g = c0 + sum(c1*(1+k)^x/(1+r)^(x+1), x = 0 .. n-1)
   = c0 + c1/(1+k) * sum(((1+k)/(1+r))^(x+1), x = 0 .. n-1)

Let R = (1+r)/(1+k) - 1

sum(((1+k)/(1+r))^(x+1), x = 0 .. ∞) = sum(1/(1+R)^(x+1), x = 0 .. ∞) = 1/R

g = c0 + c1/(1+k) * (sum(0 .. ∞) - sum(n .. ∞))
g = c0 + c1/(1+k) * (1 - 1/(1+R)^n))/R

(11-20-2024 10:52 AM)Albert Chan Wrote:  NPV = PV + PMT * (1-1/K)/r + FV / K

This is the easy one, solvable with TVM

1+r = (1+R) * (1+k) = R * (1+k) + (1+k)
r = R * (1+k) + k

lua> c0, c1, k, n = -25, 10, 0.05, 5
lua> tvm(n, nil, c0+c0*k, c1, 0) * (1 + k) + k -- IRR
0.324990692736928