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