Refinancing calculations

This notebook replicates the refinancing calculations we performed in class with Excel. 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.

In [2]:
# inputs

rnow = 0.10 # current interest rate, in yearly terms
term= 60 # remaining time to maturity in months
bnow= 100000000 # outstanding principal
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 [3]:
import pandas as pd # pandas is excellent for creating and manipulating dataframes, R-style
import numpy as np # great for simulations, we may not use for running regressions here
import numpy_financial as npf #numpy_financial contains the functions a standard financial calculator would have and much more

def put_comma(number): 
    return ("{:,}".format(number)) # this function puts comma separations in numbers
# 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: " + str(put_comma(round(oldpayment, 2))))
print("The new payment would be: " + str(put_comma(round(newpayment, 2))))
print("Monthly savings upon refinancing are: " + str(put_comma(round(oldpayment-newpayment, 2))))

# let us now use loops to produce the old and the new payment tables

oldib= np.zeros(term) #  this will store the start-of-the-month balance path under current loan
oldint=np.zeros(term) # interest under current loan
oldfb=np.zeros(term) # end-of-the-month balance under current loan

month=np.zeros(term) # an array that will count months
month=month.astype(int) # astype is not inplace, hence the reassignment, always something to pay attention to in Python

month[0]=1 ### Warning!!!! Python starts all arrays at zero so element zero is month 1

newib= np.zeros(term) # post-refi starting balance path
newint=np.zeros(term) # post-refi interest path
newfb=np.zeros(term) # post-refi end balance path

for t in range(term):
    if t==0: 
       newib[t]=bnow # initialize balances, like we would on excel
       oldib[t]=oldfb[t-1] # start of this month = end of last month
    oldfb[t]= oldib[t]*(1+rnow/12)-oldpayment
    newfb[t]= newib[t]*(1+rnew/12)-newpayment # standard loan algebra

# 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].set_title("End balance (\$M)")

axs[1].plot(month,oldint, label='old loan')
axs[1].plot(month,newint,label='new loan')
The current payment is: 2,124,704.47
The new payment would be: 2,075,835.52
Monthly savings upon refinancing are: 48,868.95
Text(0.5, 0, 'Month')

For practice's sake, let's make debt payment tables as panda data frames.

In [4]:
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


# 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(' ')
print("New bond:")
Old bond:
       Initial Balance      Payment   Interest  Final Balance
1       100,000,000.00 2,124,704.47 833,333.33  98,708,628.86
2        98,708,628.86 2,124,704.47 822,571.91  97,406,496.30
3        97,406,496.30 2,124,704.47 811,720.80  96,093,512.63
4        96,093,512.63 2,124,704.47 800,779.27  94,769,587.43
5        94,769,587.43 2,124,704.47 789,746.56  93,434,629.52
56       10,363,013.71 2,124,704.47  86,358.45   8,324,667.69
57        8,324,667.69 2,124,704.47  69,372.23   6,269,335.45
58        6,269,335.45 2,124,704.47  52,244.46   4,196,875.44
59        4,196,875.44 2,124,704.47  34,973.96   2,107,144.93
60        2,107,144.93 2,124,704.47  17,559.54          -0.00
New bond:
       Initial Balance      Payment   Interest  Final Balance
1       100,000,000.00 2,075,835.52 750,000.00  98,674,164.48
2        98,674,164.48 2,075,835.52 740,056.23  97,338,385.19
3        97,338,385.19 2,075,835.52 730,037.89  95,992,587.55
4        95,992,587.55 2,075,835.52 719,944.41  94,636,696.44
5        94,636,696.44 2,075,835.52 709,775.22  93,270,636.14
56       10,149,672.43 2,075,835.52  76,122.54   8,149,959.45
57        8,149,959.45 2,075,835.52  61,124.70   6,135,248.63
58        6,135,248.63 2,075,835.52  46,014.36   4,105,427.47
59        4,105,427.47 2,075,835.52  30,790.71   2,060,382.65
60        2,060,382.65 2,075,835.52  15,452.87           0.00

Now we calculate the NPV of refi. We make it a function so that we can ask questions about parameters later.

In [5]:
def npv_refi(rnew, term, bnow, rnow, penalty): # rnew, term, bnow, rnow, penalty, here, are dummy names
    return npf.pv(rnew/12,term,-(oldpayment-newpayment))-penalty*bnow

print("The NPV of refi is: " 
      +str(put_comma(round(npv_refi(rnew, term, bnow, rnow, penalty),2))))
The NPV of refi is: 354,182.11

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 [6]:
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
The break-even refi rate is: 9.148%

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 [7]:

# 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)) + "%")
The break-even prepayment penalty rate is: 2.354%