Refinancing calculations¶
This notebook replicates the refinancing calculations we performed in class with Excel. (See section 1.1 of my notes.) If you are reading this and you are not in my FIN 325, we want to calculate the NPV of refinancing from a bond with 60 months to maturity, a fixed rate of 10% annualized, fixed monthly payments, to a bond with the same term but a rate of 9% annualized. The outstanding principal is \$100M and the prepayment penalty is 2% of principal. We will ignore the option value of delaying refinancing, for now.
This is a good way to get started with Python since these calculations are easy. First we need to enter inputs, much like we would in Excel.
The following assumes fixed-payment debt. My bond calculator notebook considers all possible payment shapes.
In [1]:
# inputs
rnow = 0.10 # current interest rate, in yearly terms
term= 60 # remaining time to maturity in months
bnow= 100_000_000 # outstanding principal (the neat power of _ in Python)
rnew = 0.09 # new rate
penalty = 0.02 # prepayment penalty
Let's now compute and plot the shape of the old and the new loan. Like excel Python has functions for that.
In [2]:
import pandas as pd # pandas is excellent for creating and manipulating dataframes, R-style
import numpy as np # array manipulations and simulations
import numpy_financial as npf #numpy_financial contains the functions a standard financial calculator would have
def put_comma(number):
return ("{:,.2f}".format(number)) # this function puts comma separations in numbers, with two decimals
# when we switch to dataframes below we can set that convention once and for all
oldpayment=npf.pmt(rnow/12,term,-bnow) # emulates Excel's pmt function, this is the current payment
# the - comes from the fact that pmt functions, by convention, take the borrower's perspective
# for borrowers, the loan is a positive cash flows whereas payments are negative cashflows
# we want to work with positive payments
newpayment=npf.pmt(rnew/12,term,-bnow) # this is the payment after refinancing
print("The current payment is: " + put_comma(oldpayment))
print("The new payment would be: " + put_comma(newpayment))
print("Monthly savings upon refinancing are: " + put_comma(oldpayment-newpayment))
# let us produce the old and the new bonds' amortization tables
month=np.array([t+1 for t in range(term)]).astype(int) # an array that will count months
# this is called list comprehension, it helps avoid costly loops
# We will rely on list comprehension much more in the Monte Carlo notebook
# See http://erwan.marginalq.com/index_files/tea_files/Montecarlo.html
# now we compute interest and principal payment schedules
# Warning: time starts at period 1 for all financial functions from numpy_financial
# All pure Python things like range start at zero
oldint=np.array([npf.ipmt(rnow/12,t+1,term,-bnow) for t in range(term)]) # interest payment under the old bond
newint=np.array([npf.ipmt(rnew/12,t+1,term,-bnow) for t in range(term)]) # interest payment under the new bond
oldprinc=np.array([npf.ppmt(rnow/12,t+1,term,-bnow) for t in range(term)]) # principal payment under the old bond
newprinc=np.array([npf.ppmt(rnew/12,t+1,term,-bnow) for t in range(term)]) # principal payment under the new bond
# for balances, recursive objects, I think a loop is safer
# but good Python coders find list comprehension solutions for just about every loop
oldib= np.zeros(term) # post-refi starting balance path
oldfb=np.zeros(term) # post-refi end balance path
newib= np.zeros(term) # post-refi starting balance path
newfb=np.zeros(term) # post-refi end balance path
for t in range(term):
if t==0:
oldib[t]=bnow
newib[t]=bnow # initialize balances, like we would on excel
else:
oldib[t]=oldfb[t-1] # start of this month = end of last month
newib[t]=newfb[t-1]
oldfb[t]= oldib[t]-oldprinc[t]
newfb[t]= newib[t]-newprinc[t] # standard loan algebra
# it is said that for loops are never necessary in Python
# that list comprehension can and should be use instead
# not sure how to do it in the above case because of the circularities
# but good Python programmers would find a way
# now let us plot a few things to make sure it all looks ok
# both balances must start at the same place and both must end at zero
# so really the balance paths can't be that different from one another
import matplotlib.pyplot as plt #graphing module with matlab-like properties
%matplotlib inline
fig, axs = plt.subplots(1,2,figsize=(12, 8))
axs[0].plot(month,oldfb/1000000, label='old loan')
axs[0].plot(month,newfb/1000000,label='new loan')
axs[0].legend()
axs[0].set_title("End balance (\$M)")
axs[0].set_xlabel('Month')
axs[1].plot(month,oldint, label='old loan')
axs[1].plot(month,newint,label='new loan')
axs[1].legend()
axs[1].set_title("Interest")
axs[1].set_xlabel('Month')
Out[2]:
For practice's sake, let's make debt payment tables as panda data frames.
In [3]:
oldpay=oldpayment*np.ones(term)
newpay=newpayment*np.ones(term) # stores payment schedule under both notes
# here they are constant
# but we will learn later to work with any payment shape we can dream up
pd.options.display.float_format = '{:,.2f}'.format # this is the financial format we like
oldbond=pd.DataFrame({'Month':month,'Initial Balance': oldib,'Payment':oldpay,
'Interest':oldint,'Final Balance':oldfb}) # current bond schedule
oldbond=oldbond.set_index('Month') # yet another Python method that is not inplace
newbond=pd.DataFrame({'Month':month,'Initial Balance': newib,'Payment':newpay,
'Interest':newint,'Final Balance':newfb}) # post-refi schedule
newbond=newbond.set_index('Month')
# let's show the first and last 5 lines of each bond table
sliced_old = oldbond.head(5)
sliced_old = sliced_old.append(oldbond.tail(5)) # append method is used all over the place in python to play with arrays
sliced_new = newbond.head(5)
sliced_new = sliced_new.append(newbond.tail(5)) # append method is used all over the place in python to play with arrays
print("Old bond:")
print(sliced_old)
print(' ')
print("New bond:")
print(sliced_new)
Now we calculate the NPV of refi. We make it a function so that we can ask questions about parameters later.
In [4]:
def npv_refi(rnew, term, bnow, rnow, penalty): # rnew, term, bnow, rnow, penalty, here, are dummy names
'''This function returns the npv of refi given
rnew: new rate
term: remaining term on the bond
bnow: remaining principal
rnow: current rate
penalty: prepayment fee as a fraction of principal'''
oldpayment=npf.pmt(rnow/12,term,-bnow)
newpayment=npf.pmt(rnew/12,term,-bnow)
return npf.pv(rnew/12,term,-(oldpayment-newpayment))-penalty*bnow
print("The NPV of refi is: "
+put_comma(npv_refi(rnew, term, bnow, rnow, penalty)))
Now we can ask all kinds of intersting questions. For instance, how low must the new rate be to justify refinancing? We need Python's version of goal seek for that, which is best provided by the scipy.optimize module.
In [5]:
import scipy.optimize as opt
r0=rnow # first guess for break-even rate is old rate
print("The break-even refi rate is: "
+str(round(100*opt.fsolve(npv_refi, r0, args=(term, bnow, rnow, penalty))[0],3)) + "%") #lots ot unpack here
# but mostly formatting stuff
Now what if we want to find the break-even prepayment penalty level? (One issue here is that penalty is not the first argument making distinguishing between the variable to optimize over and variables that are mere parameters somewhat ambiguous.) Python has super easy and efficient methods to do this. See below.
In [6]:
pen0=0.02
# I like the following 'lambda' way to neatly and easily pick one argument to optimize over
# what-you-see-is-what-you-get-syntax helps minimize operator error
print("The break-even prepayment penalty rate is: "
+str(round(100*opt.fsolve(lambda p: npv_refi(rnew,term,bnow,rnow,p), pen0)[0],3)) + "%")
How about, for kicks, a function that computes the half-life of a bond?
In [7]:
def halflife(term, bnow, rnow): # term, bnow, rnow, penalty are the bond's contract terms, still assuming fixed payments
pprinc=np.array([npf.ppmt(rnow/12,t+1,term,-bnow) for t in range(term)]) # principal payment under the bond
# commented out below is some compact list comprehension syntax that works but is inefficient in this case
# It repeats additions needlessly (additions are cheap vs other operations so no biggie here)
# count = len([np.sum(pprinc[:i+1]) for i in range(term) if np.sum(pprinc[:i])<bnow/2])
# simple loop below cuts on the number of operations in this case
count=1;
remainingprincipal=bnow-pprinc[0];
while remainingprincipal>bnow/2:
count+=1
remainingprincipal-=pprinc[count]
return count
print("The bond's half-life is " +str(halflife(term, bnow, rnow)) + " periods")