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