Documentation/How Tos/Calc: Derivation of Financial Formulas
Derivation of Financial Formulas
One factor that may hold back the use of Calc's Financial Functions is that of not understanding exactly how they are calculated. Some users may feel more comfortable knowing how the formulas have been derived, and this page is for them.
Most users will of course not wish to investigate further, and can ignore this page.
!!This page is currently in development!!
PMT
We take out a loan. We will calculate an unchanging periodic payment which will pay (fixed rate) interest charges, and will steadily reduce the sum borrowed until there is a certain sum still owing after n periods.
m = payment each period (does not change)
n = number of periods (a period may be a month, a year or whatever)
p = principal (the capital sum borrowed)
f = the final balance owing after n periods
r = interest rate (fixed rate) per period (not necessarily per month or year)
If the payments are made at the end of each period:
At the end of the first period:
interest payable is pr
capital repaid = m - pr
therefore capital still owed = p - (m - pr) = p(1+r) - m
At the end of the second period:
interest payable = (p(1+r) - m )r
capital repaid = m - (p(1+r) - m )r
therefore capital still owed = [p(1+r) - m] - [m - (p(1+r) - m )r]
= p(1+r) - m - m + p(1+r)r - mr
= p(1+r)(1+r) - m - m(1+r)
= p(1+r)2 - m - m(1+r)
= p(1+r)2 - m(1 + (1+r))
At the end of the third period:
interest payable = (p(1+r)2 - m - m(1+r))r
capital repaid = m - (p(1+r)2 - m - m(1+r) )r
therefore capital still owed = [p(1+r)2 - m - m(1+r)] - [m - (p(1+r)2 - m - m(1+r))r]
= p(1+r)2 - m - m(1+r) - m + (p(1+r)2 - m - m(1+r))r
= p(1+r)2 - m - m(1+r) - m + p(1+r)2r - mr - m(1+r)r
= p(1+r)2 + p(1+r)2r - m - m(1+r) - m(1+r) - m(1+r)r
= p(1+r)3 - m - m(1+r) - m(1+r)2
= p(1+r)3 - m(1 + (1+r) + (1+r)2)
Now
1 + (1+r) + (1+r)2
looks like a geometric series 1+ a + a2 .... ax-1
which = (1+ax)/(1-a) (a standard result for a geometric sequence)
Here:
1 + (1+r) + (1+r)2 ..... (1+r)x-1 = (1-(1+r)x)/(1-(1+r)) = (1-(1+r)x)/(-r) = ((1+r)x-1)/r
This is true for x = 3.
For any x, if the capital owed at start of xth period = p(1+r)x - m((1+r)x-1)/r
then at the end of the (x+1)th period:
interest payable = (p(1+r)x - m((1+r)x-1)/r)r
capital repaid = m - (p(1+r)x - m((1+r)x-1)/r)r
therefore capital still owed
= p(1+r)x - m((1+r)x-1)/r - [m - (p(1+r)x - m((1+r)x-1)/r)r]
= p(1+r)x + p(1+r)xr - m((1+r)x-1)/r + m((1+r)x-1)/r)r - m
= p(1+r)x(1+r) - (m((1+r)x-1)/r)(1+r) - m
= p(1+r)x+1 - m((1+r)x+1-(1+r))/r - m
= p(1+r)x+1 - m((1+r)x+1-1)/r + mr/r - m
= p(1+r)x+1 - m((1+r)x+1-1)/r
Thus if the formula is true for any x, it is true for x+1. The result is therefore true for all x. Therefore:
At the end of the nth period:
capital still owed = f
= p(1+r)n - m(1 + (1+r) + (1+r)2 ..... (1+r)n-1)
= p(1+r)n - m((1+r)n-1)/r
Therefore
m = [p(1+r)n - f]r/((1+r)n-1)
Now:
(1+r)n expands to
1 + nr + n(n+1)r2/2 ....etc
For very small values of r where r2 is neglible
(1+r)n may be approximated by 1 + nr and we have:
f = p(1+r)n</sup> - m((1+r)n-1)/r
≈ p(1+nr) - m((1+nr)-1)/r
=p(1+nr) - mn
=>
m = (p(1+nr) - f)/n
If payments are made at the start of each period:
(including at the start of the first period)
At the start of the first period:
interest payable is 0
capital repaid = m
therefore capital still owed = p - m
At the start of the second period:
interest payable is (p-m)r
capital repaid = m - (p-m)r
therefore capital still owed = p - m - (m - (p-m)r)
= p - m - m + (p-m)r = p - m - m + pr - mr
= p(1+r) - m - m(1+r)
At the start of the third period:
interest payable is (p(1+r) - m - m(1+r))r
capital repaid = m - (p(1+r) - m - m(1+r))r
therefore capital still owed = p(1+r) - m - m(1+r) - (m - (p(1+r) - m - m(1+r))r)
= p(1+r) - m - m(1+r) - m + (p(1+r) - m - m(1+r))r
= p(1+r) - m - m(1+r) - m + p(1+r)r - mr - m(1+r)r
= p(1+r) + p(1+r)r - m - m - mr - m(1+r) - m(1+r)r
= p(1+r) 2 - m - m(1+r) - m(1+r) 2
At the start of the nth period:
capital still owed = p(1+r)n</i>-1<i> - m(1 + (1+r) + (1+r)2 ..... </sup>(1+r)n</i>-1)
= p(1+r)n-1 - m((1+r)n-1)/r
At the end of the nth period:
interest payable/owed is (<i>p(1+r)n</i>-1<i> - m((1+r)n-1)/r)r
There is no payment
capital still owed = f
= p(1+r)n</i>-1<i> - m((1+r)n-1)/r + (p(1+r)n</i>-1<i> - m((1+r)n-1)/r)r
= p(1+r)n-1 - m((1+r)n-1)/r + p(1+r)n-1r - m((1+r)n-1)
= p(1+r)n-1 + p(1+r)n-1r - m((1+r)n-1)/r - m((1+r)n-1)
= p(1+r)n - m((1+r)n-1) (1 + 1/r)
= p(1+r)n - m((1+r)n-1) (1 + r)/r
= p(1+r)n - m((1+r)n+1-(1+r)) /r
= p(1+r)n - m(1 + r)((1+r)n-1)/r
=>
m = [p(1+r)n - f]r/[(1+r)((1+r)n-1)]
