Mortgage amortization methods and schedules are well known to accountants, real estate professionals and other financial professionals. CPAs learn how to develop and use mortgage repayments in introductory accounting courses. Traditionally, beginning accounting students calculate the period interest expense by multiplying the effective interest rate for the period by the outstanding loan balance, calculate the period principal paid by subtracting the period interest expense from the total payment period, then calculate the new remaining mortgage balance. In the past, when the author used the method described above, rounding errors inevitably crept into the calculations. This article provides a different method for developing an amortization table that minimizes or eliminates rounding errors.
A proposed alternative
The author suggests that instead of starting the amortization schedule by calculating interest charges, a user begins by calculating the remaining mortgage principal balance. But how do you know the remaining principal balance before calculating the interest expense and the principal payment for a period? Since a mortgage is a kind of ordinary annuity, a user can calculate the present value of the annuity or, equivalently, the remaining mortgage balance using a present value from an annuity formula. The author uses a 36 month mortgage to demonstrate the above method and compares the proposed method with the more traditional method. The proposed method is easily extendable to longer periods of time; however, the author uses 36 months to facilitate the presentation. Before calculating the amortization schedule, the author calculates a monthly mortgage payment for an arbitrary example.
Exhibit 1 shows mortgage details and calculated monthly mortgage payment: $ 9,816.49, calculated by Excel PMT function. The mortgage payment is also calculated by manipulating the present value (PV) of a regular annuity formula to resolve the monthly payment. Using a monthly interest rate of 0.375% and 36 total payments, the present value of a regular annuity formula calculates the present value of $ 1.00 receivable for 36 months as 33, $ 6,169, usually shown as a Present Value Factor (PVF) without the dollar. sign. Dividing the 33.6169 PVF into the starting mortgage amount of $ 330,000 yields the monthly payment of $ 9,816.49. The formula entered in Excel is displayed on line 11 of Exhibit 1. (See the sidebar for the derivation of the present value of an ordinary annuity.) The author used a ROUND function with two decimal places to conform to the usual monthly payments used for mortgages. Since the mortgage payment is made monthly, the formula requires the use of the monthly interest rate of 0.3750% and the rate must be entered into the formula in its decimal form of 0.00375.
Fortunately, the same calculation can also be done with Excel PMT function as also shown in Exhibit 1.Note that PMT places a negative sign in front of the monthly payment and does not automatically round to two decimal places as required for mortgage calculations. PMT also requires an entry for the future ending value (FR) of the mortgage, which is zero for this example. The entry for the mortgage payment type is zero, to indicate that the payment is due at the end of the period, which is the end of the month for this example.
After calculating the monthly mortgage payment, a user can then calculate the desired mortgage amortization schedule. A traditional amortization plan is presented in Exhibit 2. Note that there is a rounding error of $ 0.18 after 36 months; thus, interest expense is understated by $ 0.18 and principal paid is overstated by $ 0.18. The amortization plan proposed by the author corrects this rounding error as indicated in the following steps illustrated by Exhibit 3.
Traditional amortization schedule
Proposed amortization schedule
Step 1 is to calculate the remaining principal due after each monthly payment. For January 2017 in this example, this is equivalent to calculating the present value of a regular annuity with 35 payments remaining. The calculation requires using the monthly mortgage payment, the monthly interest rate, and the number of payments remaining to calculate the present value of the mortgage at the end of each month. For example, the current value of the mortgage balance at the end of the first month is calculated (with actual values ââdisplayed) as: ROUND (((1-1 / (1 + 0. 0 0 3 7 5) ^ (3 6 – 1)) / (. 00375)) * 9,816.49.2) = 321,421.18. The resulting remaining principal balance is shown in cell F9.
Exhibit 4 shows how to more simply get the same present value of an ordinary annuity using Microsoft Excel’s PV function, shown in columns G and H. Column F, calculated using the formula described above, and the column H, calculated using the PV function must be the same; note again that the PV function places a negative sign in front of the remaining main balance calculation and does not round off the output. The author used the ROUND function for two decimal places and entered a negative sign in front of the PV formula to get the desired decimal output and convert the output to a positive number as shown in column H.
Amortization schedule proposed with the results of the Microsoft PV function (columns G and H)
Derivation of the present value of an ordinary annuity
The derivation of the present value of an ordinary annuity uses a sums method. For an ordinary annuity, payments are made at the end of the period (the end of the month in this example). The derivation here is done using numbers related to the example, but can easily be extended to a general case.
The PVF is the sum (S) of the present values ââof the periodic payments of $ 1.00.
- Sum (S) = 1 / (1 + 0.00375)1 + 1 / (1 + 0.00375)2 +â¦ + 1 / (1 + 0.00375)(36-1) + 1 / (1 + .00375)36
- Multiply (S) by (1 + interest rate) = (1 + 0.00375) S = 1 + 1 / (1 + 0.00375)1 +â¦ + 1 / (1 + 0.00375)(36-1)
- Subtract Equation 1 from Equation 2: (1 + 0.00375) S – S = 1 – 1 / (1 + 0.00375)36
The present value of $ 1.00 paid by the buyer or received by the seller using the numbers in the example is equal to $ 33.6169. The monthly payment is obtained by dividing the initial mortgage balance by the calculated PVF of the ordinary annuity of 33.6169, for a result of $ 9,816.49 (rounded to two decimal places).
Step 2 is to subtract cell F9 from cell C9 to derive cell E9, which is the principal paid for the current period. For January 2017, the calculation is: $ 330,000.00 – $ 321,421.18 = $ 8,578.82.
Step 3 is to subtract the principal paid for the current period from the monthly payment and enter the result in cell D9. For January 2017, the calculation is: $ 9,816.49 – $ 8,578.82 = $ 1,237.67.
Step 4 is to continue the process of steps 2 and 3 until the completion of the amortization schedule.
Avoid rounding errors
Based on the results that the author has known and demonstrated here, it may be worth considering the proposed mortgage amortization approach described above as an alternative to the traditional calculation. While rounding errors may not be significant using the traditional approach, rounding errors are annoying and can be eliminated using the method proposed by the author.