In [8]:
#import libraries
import pandas as pd
import numpy as np   
import math
from scipy.optimize import fsolve
In [11]:
#import yield_curve_rates
yield_curve = pd.read_excel('yield_curve_rates.xlsx')
yield_curve
Out[11]:
6 Mo 1 Yr 1.5 Yr 2 Yr 2.5 Yr 3 Yr 3.5 Yr 4 Yr 4.5 Yr 5 Yr
0 0.06 0.15 0.325 0.50 0.645 0.79 0.8925 0.995 1.0975 1.20
1 0.07 0.15 0.305 0.46 0.595 0.73 0.8350 0.940 1.0450 1.15
2 0.07 0.17 0.320 0.47 0.620 0.77 0.8750 0.980 1.0850 1.19
3 0.07 0.14 0.275 0.41 0.550 0.69 0.7925 0.895 0.9975 1.10
4 0.07 0.14 0.265 0.39 0.525 0.66 0.7550 0.850 0.9450 1.04
5 0.07 0.16 0.305 0.45 0.600 0.75 0.8450 0.940 1.0350 1.13
6 0.06 0.14 0.275 0.41 0.560 0.71 0.8025 0.895 0.9875 1.08
7 0.07 0.17 0.340 0.51 0.670 0.83 0.9300 1.030 1.1300 1.23
8 0.07 0.17 0.350 0.53 0.690 0.85 0.9475 1.045 1.1425 1.24
9 0.06 0.18 0.355 0.53 0.700 0.87 0.9675 1.065 1.1625 1.26
10 0.07 0.17 0.355 0.54 0.705 0.87 0.9700 1.070 1.1700 1.27
11 0.06 0.18 0.350 0.52 0.685 0.85 0.9475 1.045 1.1425 1.24
12 0.06 0.18 0.350 0.52 0.680 0.84 0.9350 1.030 1.1250 1.22
13 0.06 0.18 0.350 0.52 0.690 0.86 0.9500 1.040 1.1300 1.22
In [12]:
#define durations
coupon_duration = [0.5, 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5]
#hold zero coupon rates for each day
all_zero_rates = []

#for each day find zero coupon rates
for index in range (0,len(yield_curve)):
    
    coupon_rate = np.array(yield_curve.iloc[index,:])
    coupon_rate = coupon_rate / 100     #convert yield_curve rates to numpy and divide by 100
    
    
    ##############################################################################################################
    #cash flow calculation for yield curve rates
    cash_flow = []
    for i in range (1,11):
        cash = []
        
        for j in coupon_duration:
            #every 6 months coupon payment is relized until maturity date
            if i/2 > j:
                cash.append(coupon_rate[i-1]/2*100)
            #at maturity coupon payment with face value is realized    
            if i/2 == j:
                cash.append(100*(coupon_rate[i-1]/2+1))
        #for each coupon duration append cash flow to main cash_flow array        
        cash_flow.append(cash)
    
    ##############################################################################################################
    #define zero_coupon_rate 
    zero_coupon_rate=[coupon_rate[0]]   #for 0.5 year maturity zero_coupon and yield curve rates are same
    
    
    #calculate zero_rates for other maturities
    for i in range (1,10):
        #create array for present_values
        pv = []
        
        for j in range (0,10):
            
            #until maturity calculate present values by using previous zero_coupon_rates
            if i > j:
                pv.append(cash_flow[i][j] / ((1 + zero_coupon_rate[j]/2)**(coupon_duration[j]*2)))
            
            #at maturity find new zero_coupon rates by equating sum of present valus to 100
            if i == j:
                #define problem
                def rate (r):
                    remaining = 100 - sum(pv)
                    p_val = cash_flow[i][j] / ((1 + r/2)**(coupon_duration[j]*2))
                    return remaining - p_val
                #solve problem and find zero_coupon_rates
                r = fsolve(rate, [0])[0]
                
                #append new coupon rates
                zero_coupon_rate.append(r)
    #append daily zero_coupon_rates to all_zero_rates array.           
    all_zero_rates.append(zero_coupon_rate)
    
    ##############################################################################################################
    
#create dataframe with all_zero_rates
rates_df = pd.DataFrame(all_zero_rates)
In [13]:
rates_df
Out[13]:
0 1 2 3 4 5 6 7 8 9
0 0.0006 0.001500 0.003253 0.005010 0.006470 0.007935 0.008973 0.010015 0.011062 0.012114
1 0.0007 0.001500 0.003053 0.004609 0.005967 0.007330 0.008393 0.009460 0.010531 0.011609
2 0.0007 0.001700 0.003203 0.004709 0.006218 0.007734 0.008797 0.009864 0.010937 0.012015
3 0.0007 0.001400 0.002752 0.004107 0.005515 0.006928 0.007965 0.009006 0.010051 0.011102
4 0.0007 0.001400 0.002652 0.003906 0.005263 0.006625 0.007586 0.008550 0.009517 0.010490
5 0.0007 0.001600 0.003053 0.004508 0.006017 0.007532 0.008494 0.009458 0.010427 0.011401
6 0.0006 0.001400 0.002752 0.004107 0.005616 0.007130 0.008065 0.009004 0.009947 0.010894
7 0.0007 0.001700 0.003403 0.005110 0.006722 0.008339 0.009352 0.010369 0.011390 0.012417
8 0.0007 0.001700 0.003504 0.005311 0.006923 0.008541 0.009528 0.010520 0.011516 0.012517
9 0.0006 0.001801 0.003554 0.005311 0.007024 0.008744 0.009731 0.010722 0.011719 0.012720
10 0.0007 0.001700 0.003554 0.005412 0.007074 0.008743 0.009756 0.010773 0.011795 0.012823
11 0.0006 0.001801 0.003504 0.005211 0.006873 0.008541 0.009529 0.010520 0.011516 0.012517
12 0.0006 0.001801 0.003504 0.005211 0.006822 0.008440 0.009402 0.010367 0.011337 0.012312
13 0.0006 0.001801 0.003504 0.005211 0.006923 0.008643 0.009554 0.010468 0.011387 0.012310