Categories
Computers Programming Python

Fun With Numbers

Periodically, I try to take a look at our home finances to see if there’s something that can be done to find some hidden stash of money. So far, my efforts have been for naught.

One expense I always investigate is our mortgage payment. I’ve always tried to pay ahead on the mortgage to save on future interest payments. So yesterday I got curious about what the best way to pay the curtailment- at the same time as the payment or halfway through the month or some other day of the month? I could have resorted to a web page that calculates amortization tables, but what fun is that?

So I wrote some python code that can be used to generate a repayment table.

Here’s the meat of it:

Months = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31] 
Month = 0

def setPaymentParameters(payment, rate, day = 0):

    monthlyrate = rate / 100.0 / 12

    def _calcMonth(principal, curtailment = 0):

        def _calcADB(principal, curtailment, day):
            global Month, Months

            dim = Months[Month]
            Month += 1
            if Month == 12:
                Month = 0
            return ((day * (principal + curtailment)) + ((dim - day) * principal))/dim

        # _calcMonth code starts here...
        adb = _calcADB(principal, curtailment, day)
        interest = adb * monthlyrate
        return (principal,
                adb,
                interest, 
                payment-interest,  # principal payment
                curtailment,
                (payment-interest)+curtailment,  # total principal payment
                principal-((payment-interest)+curtailment))

    return _calcMonth

So the setPaymentParameters function returns a function that will calculate the monthly interest, principal payment and so forth for a single month. The function returned is a closure over the set monthly payment, the interest rate and the day of month a theoretical curtailment payment is made. No curtailment is necessary for the function to work.

In order to determine the effect of curtailments separate from the normal payment, the calculation uses an average daily balance method. For instance, a normal payment is typically made on the 1st of the month and a separate curtailment payment is made on the 15th. The average is calculated by summing the days the principal the post-payment level and adding the sum of the days the principal is at the post-curtailment level. Then divide by the total number of days to get the average daily balance. In the absence of a curtailment, the calculation simplifies to the prinicipal balance at the beginning of the period.

Following is an example of how to use the function:

Rate = float(5.25)
Payment = float(1000.00)

Amortization = []
CalcMonth = setPaymentParameters(Payment, Rate, 15)
Principal = float(150000.00)
while (Principal > 0):
    t = CalcMonth(Principal)
    Amortization.append(t)
    Principal = t[6]

print len(Amortization)
interestTotal = float(0.0)
for i in range(len(Amortization)):
    print map(lambda x: format(x, ".2f"), Amortization[i])
    interestTotal += Amortization[i][2]

print format(interestTotal, ".2f")

The output won’t be particularly pretty, but it will list the total number of payments made to payoff the loan, followed by a breakdown of the effect of each monthly payment, followed by a calculation of the total interest paid. A monthly payment line will look like this:

['150000.00', '150000.00', '656.25', '343.75', '0.00', '343.75', '149656.25']

From left to right, we have the beginning principal, the average daily balance, the interest for the month, the principal paydown, the principal curtailment, the total principal paydown and finally the principal balance after the payment is applied. Each subsequent month uses this final principal balance number as the beginning balance.

The above snippet doesn’t use a curtailment payment to accelerate the paydown of the mortgage. To do that, the while loop needs to be modified slightly:

Curtailment = float(500.00)
while (Principal > 0):
    if len(Amortization) == 0:
        temp = CalcMonth(Principal)
        t = (temp[0],
             temp[1], 
             temp[2],
             temp[3], 
             Curtailment, 
             Payment-temp[2]+Curtailment,
             Principal-(Payment-temp[2]+Curtailment))
    else:
        t = CalcMonth(Principal, Curtailment)
    Amortization.append(t)
    Principal = t[6]

The modification is needed for the first payment. Since it’s the first payment, no curtailment is made, so the interest is calculated on the entire loan amount. The returned payment info needs to be modified then, manually inserting the curtailment payment. Thereafter, all calculations use the curtailment.

Here are the first couple of payment output lines:

['150000.00', '150000.00', '656.25', '343.75', '500.00', '843.75', '149156.25']
['149156.25', '149424.11', '653.73', '346.27', '500.00', '846.27', '148309.98']

The curtailment payment is included and the ending principal balance includes the extra payment. Notice the second line’s average daily balance number, which is higher than the starting principal balance. To fully understand that, first notice that the setPaymentParameters was called with the day set to 15, meaning the curtailment payment is applied on the 15th of the month, not the same day as the normal payment. Therefore, there are 15 days where the principal sits without the curtailment payment applied. Then the payment is applied for the remainder of the month. The end result is the ADB, which is used to calculate interest, is slightly higher than the principal balance after the curtailment.

The final answer to my question about the optimal day to apply the curtailment turned out to be- it saves the most money if the curtailment is paid on the same day as the normal payment. This makes sense since in general, paying earlier means the outstanding principal is reduced quicker, therefore interest is minimized.

But, that’s not the whole picture. Sometimes, for monthly household cash flow purposes, it is preferable to make multiple smaller payments. Will that result in a big difference in total interest paid? The answer there turns out to be no, it won’t. Depending on the amount owed and repayment length, the difference is only a few hundred dollars.