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= 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 [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')   
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
Out[2]:
Text(0.5, 0, 'Month')

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)
Old bond:
       Initial Balance      Payment   Interest  Final Balance
Month                                                        
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
Month                                                        
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 [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)))
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 [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
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 [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)) + "%")
The break-even prepayment penalty rate is: 2.354%

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")
    
The bond's half-life is 34 periods