Recently, I got involved in a real estate transaction. The problem is to calculate the monthly payment needed given the amount of principal borrowed (P), the monthly interest rate (r), and the length of the loan or number of payments (n). Since the payments are monthly but the interest rate is typically provided as a yearly percentage, that percentage must be divided by 1200 to give the monthly rate. Each turn of the cycle, interest on the current balance is paid first, and then the remainder of the payment is applied to principal.
Rather than figure out the formula myself, it was easy enough to look it up in wikipedia.
r = 4.25/1200.0 n = 360 P = 250000 d = 1 - (1/(1+r))**n A = P * r / d print '%3.2f' % A |
This Python script calculates that for a loan amount of 250 thousand dollars, an annual rate of 4.25%, and a term of 30 years, the payment needed is $1229.85. About 72% of the first payment is interest. I'm happy to report that the loan broker got the right answer.
A more laborious way to do the same calculation is to simulate the monthly payment cycle. Something like this code fragment:
while current_balance - monthly_payment > 0:
num_payments += 1
interest = current_balance * monthly_interest_rate
current_balance -= (monthly_payment - interest)
payoff = current_balance
|
and then adjust the monthly_payment variable until num_payments is equal to the desired value.
One advantage of the simulation is that we can investigate the effect of paying an extra sum toward principal each month. Home mortgages typically allow "prepayment" resulting in a quicker payoff and lower total interest payments over the lifetime of the loan, while other types of loan such as a car loan may not. In the example given below, an extra payment of $100 each month shortens the payback period by 50 months.
A second advantage is that the simulation allowed me to model in a simple way the influence of inflation. As you know, inflation refers to a general rise in the price of goods and services. While it is quite painful to talk about what the current rate is (very low) and its meaning for the "recovery", a glance at the monthly and yearly averages over the past decade (range 1.59-3.85 percent) prompted me to estimate the average rate as 3% per year in the U.S., where I live.
I got interested in the effect of inflation because, I thought, the extra dollars paid early in the life of the load should be worth more than the savings obtained by ending the payments early, and so the advantage of pre-payment would be reduced. And this is true. But what I also discovered is that there is a huge effect of inflation on the total cost of the loan and that makes the loan a remarkable bargain.
Calculation of the interest and loan balance is in constant dollars, but we keep track of the "total cost" separately. There the cost of each payment is adjusted for inflation.
Here is the output (code listing at the end):
with inflation 360 payments of 1229.84 final payoff: 7.06 total cost: 291712.05 no inflation 360 payments of 1229.84 final payoff: 7.06 total cost: 442749.46 with inflation 310 payments of 1329.84 final payoff: 22.28 total cost: 265102.21 no inflation 310 payments of 1329.84 final payoff: 22.28 total cost: 381272.68 |
We calculate the number of payments and total cost for the standard monthly payment, or with an additional $100, either with or without inflation. If you do the math, you'll find that the extra payment regime saves about 61K without inflation and only 26K with inflation. But the big effect is on the total cost of the loan. With the standard payment inflation saves 150K. Wow.
I knew that inflation is bad for investors (one example), but never realized just how good it is for creditors. Two sides of the same coin, I suppose.
[UPDATE: I forgot to mention that I am suspicious about the low total cost with inflation. The method without inflation is easily checked and is correct within roundoff error. ]
initial_balance = 250000
interest = 4.25 # percent
monthly_rate = interest/1200.0
term = 360 # months
inflation = 3.0 # percent
monthly_inflation_rate = inflation/1200.0
def show(month,base,extra,current,total):
print (str(month) + ' payments of').ljust(15),
print ('%3.2f' % (base + extra)).rjust(10)
print ('final payoff:').ljust(15),
print ('%3.2f' % current).rjust(10)
print ('total cost:').ljust(15),
print('%3.2f' % total).rjust(10)
print
def test(base,extra=0,with_inflation=True):
month = 0
current = initial_balance
total = 0
inflation_multiplier = 1.0
while current - base - extra > 0:
month += 1
inflation_multiplier *= (1.0 / (1.0 + monthly_inflation_rate))
interest = monthly_rate * current
current -= base - interest + extra
cost = base
if with_inflation:
cost *= inflation_multiplier
total += cost
total += current
show(month,base,extra,current,total)
base = 1229.84
for extra in [0,100]:
print 'with inflation'
test(base,extra)
print 'no inflation'
test(base,extra,with_inflation=False)
|
