Mortgage amortization methods and tables are well known to accountants, real estate professionals, and other financial professionals. CPAs learn how to develop and use mortgage amortizations in introductory accounting classes. Traditionally, beginning accounting students calculate period interest expense by multiplying the effective period interest rate by the outstanding loan balance, calculate period principal paid by subtracting the period interest expense from the total period payment, and then calculate the new remaining mortgage balance. In the past, when the author used the above-described method, rounding errors inevitably crept into the calculations. This article proposes a different method to develop an amortization schedule that minimizes or eliminates rounding errors.
A Proposed Alternative
The author proposes that, instead of beginning the amortization schedule by calculating interest expense, a user begins by calculating the remaining mortgage principal balance. But how one can know the remaining principal balance before computing a period’s interest expense and principal payment? Because a mortgage is a kind of ordinary annuity, a user can calculate the present value of the annuity or, equivalently, the remaining mortgage balance by using a present value of 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 expandable to longer time periods; however, the author uses 36 months for ease of presentation. Prior to computing the amortization schedule, the author derives a monthly mortgage payment for an arbitrary example.
Exhibit 1 shows the details of the mortgage and the calculated monthly mortgage payment: $9,816.49, calculated by the Excel PMT function. The mortgage payment is also calculated by manipulating the present value (PV) of an ordinary annuity formula to solve for the monthly payment. Using a monthly interest rate of 0.375% and 36 total payments, the pres ent value of an ordinary annuity formula calculates the present value of $1.00 to be received for 36 months as $33.6169, 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 derives the monthly payment of $9,816.49. The formula entered into Excel is shown on line 11 of Exhibit 1. (See the Sidebar for the derivation of the present value of an ordinary annuity.) The author used a two–decimal place ROUND function to conform to the usual monthly payments used for mortgages. Since the mortgage payment is made monthly, the formula requires the monthly interest rate of 0.3750% to be used, and the rate must be entered into the formula in its decimal form of 0.00375.
Exhibit 1
Mortgage Details
Fortunately, the same calculation can also be made with the Excel PMT function, as also shown in Exhibit 1.Notice 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 ending future value (FV) of the mortgage, which is zero for this example. The entry for the mortgage payment type is zero, to indicate that payment is due at the end of the period, which is the end of the month for this example.
Having computed the monthly mortgage payment, a user can then calculate the desired mortgage amortization schedule. A traditional amortization schedule is shown in Exhibit 2. Note that there is a rounding error of $0.18 at the end of 36 months; thus, interest expense is underreported by $0.18, and the principal paid is overreported by $0.18. The author’s proposed amortization schedule fixes this rounding error as shown in the following steps illustrated by Exhibit 3.
Exhibit 2
Traditional Amortization Schedule
Exhibit 3
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 an ordinary annuity with 35 remaining payments. The calculation requires using the monthly mortgage payment, the monthly interest rate, and the remaining number of payments to calculate the present value of the mortgage at the end of each month. For example, the present value of the mortgage balance at the end of the first month is calculated (with the 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 obtain the same present value of an ordinary annuity by using the Microsoft Excel PV function, shown in columns G and H. Column F, calculated using the formula described above, and Column H, calculated using the PV function should be identical; note again that the PV function places a negative sign in front of the remaining principal balance calculation and does not round the output. The author used the ROUND function for two decimal places and entered a negative sign in front of the PV formula to achieve the desired decimal output and convert the output to a positive number, as shown in column H.
Exhibit 4
Proposed Amortization Schedule with Results from Microsoft PV Function (Columns G & H)
Derivation of the Present Value of an Ordinary Annuity
The derivation of the present value of an ordinary annuity uses a method of sums. 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 made using numbers related to the example, but can easily be expanded 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+.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 derived by dividing the original 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 to the completion of the amortization schedule.
Avoiding Rounding Errors
Based on the results the author has experienced—and demonstrated here—it may be worth considering the proposed approach to mortgage amortization described above as an alternative to the traditional calculation. While the rounding errors may not be large using the traditional approach, rounding errors are annoying and can be eliminated using the author’s proposed method.
