One for the Mathematicians...

January 07, 2014
David Scouller

Whilst there are far more eaiser and convenient ways to caluclate Principle and Interest repayments, some may like to learn the maths behind the on-line calulators.

Below is an explanation of the maths involved.

There is a PMT function in Excel that makes part 3 very easy. ( in Bold below )

1. Determine the number of payments over the life of the loan by multiplying the term, which is most often given in years, by the payment frequency, in payments per year. For example, a monthly payment plan has 12 payments per year.

For a 30-year mortgage, a monthly payment plan yields 360 payments.

2. Determine the period interest rate by dividing the annual percentage rate, or APR, by the payment frequency. For a 6 percent APR loan paid monthly, the period interest rate is 0.06 / 12 = 0.005, or 0.5%.

3. Calculate the payment for the loan. The equation is: P = L[c(1 + c)^n] / [(1 + c)^n - 1]. P is the payment, while L is the loan value; for example, $750,000.

The period interest rate from Step 2 is c, and the number of payments from Step 1 is n.

Using this equation, the payment is $4496.46. 

Calculate the total principle and interest payment for the loan. It is easiest to do this in Microsoft Excel, using the PMT function. Type this formula into the function bar in Excel and substitute your loan specifics for the variables: =PMT(Interest rate,Number of periods, Principle balance as a negative,0). In the example above, the formula would look like "=PMT(.005,360,- 750,000,0),"

4. Multiply the loan value by the period interest rate to determine the first month's interest, and then subtract that amount from the monthly payment to get the first month's principal. $750,000 * 0.005 = $3,750 $4,496.46 - $3,750 = $746.46

5. Subtract the principal from the current loan value to get the new loan value. Then repeat steps four and five until the loan value reaches zero. $750,000 - $746.46 = $749,253.54 $749,253.54 * 0.005 = $3,746.27, the 2nd month's interest $4,496.46 - $3,746.27 = $750.19, the 2nd month's principal

This becomes particularly useful when loaded into a spread sheet which allows you to do some modelling and experiment with the outcome of making additional repayments.

If you would like a copy of the spread sheet model - please contact me on 0414 259 699, or send me an email.

Posted in: Tips

Contact us today.

Additional Comments? * :