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:
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]
oldint[t]=oldib[t]*rnow/12
newint[t]=newib[t]*rnew/12
oldfb[t]= oldib[t]*(1+rnow/12)-oldpayment
newfb[t]= newib[t]*(1+rnew/12)-newpayment # standard loan algebra
month[t]=t+1
# 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[3]:

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

In [4]:

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

```
def npv_refi(rnew, term, bnow, rnow, penalty): # rnew, term, bnow, rnow, penalty, here, are dummy names
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: "
+str(put_comma(round(npv_refi(rnew, term, bnow, rnow, penalty),2))))
```

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
```

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

```
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)) + "%")
```