How to calculate monthly loan payment with fixed monthly drawdown

Solution 1:

Functions such as PMT shield users from having to understand the mathematics underlying Excel's financial calculation capabilities. To get to the type of expression quoted in https://superuser.com/a/871411 it is necessary to understand those mathematics and then adapt them to deal with the scenario outlined.

The basic mathematical relationships involved are:

v(i+1) = v(i)*(1+r) - p  
v(1) = L  
v(n+1) = 0  

where an amount L is borrowed for n periods at an interest rate of r per period and payment amounts of p are made at the end of each period. v(i) is the amount of the loan outstanding at the beginning of the i'th period.

The first relationship (equation)
v(i+1) = v(i)*(1+r) - p

simply says, in words, that the amount outstanding at the beginning of period i is increased by adding interest accrued during period i before being reduced by the payment amount made at the end of period i to get the amount outstanding at the beginning of the following period (period i+1).

The other two equations simply state the start and end conditions of the loan.

Note that if the payments p are made at the start of each period (rather than the end) the first equation would change to:
v(i+1) = (v(i)-p)*(1+r) and v(i) would be the amount outstanding at the start of period i immediately before the payment p is made.

The analysis below, which determines p in terms of L, r and n, assumes payments are made at the end of each period.

Mathematical analysis

This starts with the relationship between the loan amount outstanding in successive periods
v(i+1) = v(i)*(1+r) - p [Equation 1]

since Equation 1 applies to all periods, it follows that
v(i+2) = v(i+1)*(1+r) - p

Now using Equation 1 to substitute for v(i+1) in this second equation yields
v(i+2) = (v(i)*(1+r) - p) * (1+r) - p

Which, with a little re-arranging can be written as
v(i+2) = v(i)*(1+r)^2 - p * ((1+r) + 1) [Equation 2]

Again, it follows from Equation 1 that
v(i+3) = v(i+2)*(1+r) - p

so, substituting for v(i+2) using Equation 2 yields
v(i+3) = (v(i)*(1+r)^2 - p * ((1+r) + 1)) * (1+r) - p

which can be re-arranged as
v(i+3) = v(i)*(1+r)^3 - p * ((1+r)^2 + (1+r) + 1) [Equation 3]

Equations 1, 2 and 3, respectively, express v(i+1), v(i+2) and v(i+3) in terms v(i), r and p. There is an emergent pattern (*) in Equations 1, 2 and 3 which can be used to write a general Equation m as
v(i+m) = v(i)*(1+r)^m - p * ((1+r)^(m-1) + (1+r)^(m-2) + ... + (1+r) + 1) [Equation m]

The factor that p multiplies is a finite geometric series written backwards. A geometric series (Google it) is a sum in which each successive term is the previous term multiplied by a constant amount.

For a general finite geometric series written
S(m) = 1 + x + x^2 + x^3 + ... + x^(m-1)

there is a well-known expression that
S(m) = (x^m - 1)/(x - 1)

In Equation m, the geometric series is written backwards and x = 1+r, so the equation can be simplified to
v(i+m) = v(i)*(1+r)^m - p((1+r)^m - 1)/(1+r - 1))

or, simplifying the final denominator term
v(i+m) = v(i)*(1+r)^m - p((1+r)^m - 1))/r [Equation m']

Now, set the general value m to the number of periods n, set i to 1 and note the boundary conditions that
v(1) = L and
v(n+1) = 0

Doing this provides a version of Equation m' as
0 = L*(1+r)^n - p((1+r)^n - 1)/r

which, with a bit of re-arranging can be written as
p = (L * r * (1+r)^n)/((1+r)^n - 1)

or, dividing the numerator and denominator on right hand side by (1+r)^n
p = (L*r)/(1 - (1+r)^(-n)) [Equation for p]

which is, effectively, the formula found previously.

Scenario with additional borrowing

Here, assume that at the start of each period (including the first), an additional amount b is borrowed. v(i) is now the amount of loan outstanding at the start period i, immediately before the amount b is added to the loan.

The relationships are now

v(i+1) = (v(i)+b)*(1+r) - p  
v(1) = L  
v(n+1) = 0  

applying the same sort of analysis as set out above about leads to the analogy of Equation m' as
v(i+m) = v(i) * (1+r)^m + b * (1+r)*((1+r)^m - 1)/r - p * ((1+r)^m -1)/r

which after applying the start and end conditions can be resolved, after a bit of manipulation, to:
p = (L * r)/(1 - (1+r)^(-n)) + b * (1+r)

Potentially, there are four different scenarios: two possibilities each for when payment and borrowing transactions are made - either can be start or end of the periods - so two possibilities for each of two types of transaction giving four possibilities in total. Each scenario is amenable to the sort of analysis set out above. Payments at end of each period and additional borrowing at the start is the scenario analysed - the remaining three possible scenarios are left as an exercise for the reader.

Warning

In practice, where the periods are months, financial institutions often use daily interest calculations to recognise that there are different lengths to each month and some (looking at you, Barclaycard UK) even vary the dates from month-to-month when interest is applied to accounts. So, generally, PMT calculations and those based on the analysis above provide reasonable but not precise estimates of what happens in reality.

(*)True mathematicians would, of course, not just rely on an observation of an emergent pattern as being "the truth" but would set out to prove (or disprove) the general truth of that pattern. For simplicity, I have omitted the proof involved in showing Equation m is true in general but, trust me (I have a couple of degrees in mathematical subjects), the proof exists.