Sinje J. Butler
Deriving the Formula For Calculating
the House Payment on a Mortgage
The following investigation will go through the process of setting up an amortization schedule for a 30-year mortgage with an initial principal balance of $84,500 at a fixed rate of 7.875%, in order to derive a formula for calculating the house payment on this loan.
This process begins by setting up
an excel spreadsheet that looks as follows.
Principal |
84500 |
|
|
Apr |
7.875 |
% |
|
Periods |
30 |
years |
|
Payment |
612.68 |
|
|
|
|
|
|
period |
interest |
principal |
|
initial
principal |
|
84500.00 |
|
1 |
554.53 |
58.15 |
84441.85 |
2 |
554.15 |
58.53 |
84383.32 |
3 |
553.77 |
58.91 |
84324.41 |
4 |
553.38 |
59.30 |
84265.11 |
5 |
552.99 |
59.69 |
84205.42 |
6 |
552.60 |
60.08 |
84145.33 |
7 |
552.20 |
60.48 |
84084.86 |
8 |
551.81 |
60.87 |
84023.98 |
|
|
|
|
The interest for period one is
calculated as follows
The principal paid for the period
is calculated by subtracting the interest for the period from the house
payment.
The new principal balance is then calculated by subtracting the principal paid from the current principal balance.
Then the interest for period 2 is calculated by multiplying the monthly interest by the new principal balance.
This process is continued for 360 periods. Please see attached EXCEL SPREADSHEET for the complete amortization schedule.
Now that we understand how an amortization schedule is set up, we can begin to derive the formula for calculating the house payment. Let
I = monthly interest
P = Principal Balance
N = # of periods
X = Monthly house payment
By setting up a spreadsheet as the one pictured above and substituting the numbers with these variables we obtain the following.
|
interest |
principal
paid |
outstanding
balance |
1 |
I*P |
X-I*P |
P-(X-I*P) |
|
|
|
|
2 |
I*(P-(X-I*P)) |
X-(I*(P-(X-I*P)) |
P-(X-I*P)-(X-(I*(P-(X-I*P)))=0 |
Repeating the above process to the nth level and then solving for X gives the monthly house payment.
Let’s solve for X
when n = 2, 3 and 4.
P-(X-IP) – (X-(I(P-(X-PI)))) = 0
Or
or
or
or
Notice that when we have solved for X in the case of n = 4, the denominator of the formula will equal
if the denominator is multiplied by I and then
is decreased by one. Thus,
Thus, we can rewrite the equation as follows
.
The same can be done for n = 2 and n = 3.
So, in conclusion it appears the formula for calculating the monthly house payment is