Site icon R-bloggers

RDCOMClient : A Simple Libor IRS Pricing with OIS Discounting

sang-heon lee

[This article was first published on K & L Fintech Modeling, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
This post shows a simple example which uses the RDCOMClient R package. As an example, A Libor IRS pricing with OIS discounting is presented with the help of a VBA macro code from Mikael Katajamäki’s source with proper citation (this should be always).


< !--콘텐츠 내 자동 삽입 광고 배치하기-->< !--
–>

Introduction


In the previous post, we have used the RDCOMClient R package for communications between Excel and R.



In this post, we provides a simple example for pricing a Libor IRS with OIS discounting. Unlike Libor IRS pricing with only one Libor curve, pricing of Libor IRS using OIS discounting uses two curves.

1) Projecting floating cash flows using an adjsuted Libor forward rate curve.
2) Discounting all cash flows from two fixed and floating legs using OIS discount factors.

Among them, the first part is a little complicated so that this issue (construction of adjusted forward libor curve) will be covered in some later post. Instead, for this time, we borrow Mikael Katajamäki’s VBA function, which is found in the following his blog. Some useful background information for OIS discounting is provided for your understanding in his blog.



Like this, if useful functions for some specific purpose have already been made by another researchers, we can use these functions without transforming from VBA code to R code (with proper citation as follows).


Excel Example with VBA macro


The following figure shows the operation of macro1() function.



Clicking [run macro1] rectangular button calls macro1() which also calls a internal function for OIS discount factors and adjusted forward rates.


Mikael Katajamäki’s VBA code of bootstrapping OIS discounting is as follows, to which I add some modifications (shaded area) for OIS discounting factor to be also returned (original version returns only forward rate). When Rectangular button is clicked, macro1() VBA function is called, which calls this VBA OIS bootstrapping function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
Option Explicit
 
Public Function OIS_bootstrapping(ByRef curves As Range) As Variant
    ‘
    ‘ import source data from Excel range into matrix
    Dim source As Variant: source = curves.Value2
    ‘
    ‘ create all the needed matrices and define dimensions
    Dim nSwaps As Integer: nSwaps = UBound(source, 1)
    Dim fixed As Variant: ReDim fixed(1 To nSwaps, 1 To 1)
    Dim float As Variant: ReDim float(1 To nSwaps, 1 To nSwaps)
    Dim forward As Variant: ReDim forward(1 To nSwaps, 1 To 1)
    ‘
    ‘ counters and other temp variables
    Dim i As Integer, j As Integer, k As Integer, nCashFlows As Integer
    Dim OIS_DF As Double, OIS_Rate As Double, t As Double
    
    
    ””” added by S.H. Lee
    Dim v_OIS_DF As Variant: ReDim v_OIS_DF(1 To nSwaps, 1 To 1)
    
    ‘
    ‘ loop for cash flows processing
    nCashFlows = nSwaps: k = 0
    For i = 1 To nSwaps
        ‘
        ‘ create OIS discount factor
        OIS_Rate = source(i, 2): t = source(i, 1)
        If (t <= 1) Then OIS_DF = 1 / (1 + (OIS_Rate * t))
        If (t > 1) Then OIS_DF = 1 / (1 + OIS_Rate) ^ t
        
        ””” added by S.H. Lee
        v_OIS_DF(i, 1) = OIS_DF
        
        ‘
        ‘ create sum of fixed leg pv’s for each individual swap and create all
        ‘ cash flows (excluding coupon rate) for floating legs for each individual swap
        For j = 1 To nSwaps
            If (j <= nCashFlows) Then
                fixed(j + k, 1) = fixed(j + k, 1) + 100 * source(j + k, 3) * OIS_DF
                float(i, j + k) = 100 * OIS_DF
            Else
                ‘ replace empty array value with zero value
                float(i, nSwaps – j + 1) = 0#
            End If
        Next j
        ‘
        k = k + 1: nCashFlows = nCashFlows – 1
    Next i
    ‘
    ‘ solve for implied forward rates, which are going to be used to generate coupons
    ‘ for floating legs. matrix operation: [A * x = b] —> [x = Inverse(A) * b]
    ‘ where A = float (N x N), x = forward rates (N x 1), b = sum of swap fixed leg pv’s (N x 1)
    forward = WorksheetFunction.MMult(WorksheetFunction.MInverse(WorksheetFunction.Transpose(float)), fixed)
 
    ””” commented by S.H. Lee
    ‘OIS_bootstrapping = forward
    
    ””” added by S.H. Lee
    ‘ two output : OIS DF and Adjusted Libor Curve
    Dim m_out As Variant: ReDim m_out(1 To nSwaps, 1 To 2)
 
    For i = 1 To nSwaps
        m_out(i, 1) = v_OIS_DF(i, 1)
        m_out(i, 2) = forward(i, 1)
    Next i
 
    OIS_bootstrapping = m_out
 
End Function
 
Sub macro1()
 
    Worksheets(“Sheet1”).Range(“H4:I11”).Value = _
      OIS_bootstrapping(Worksheets(“Sheet1”).Range(“C4:E11”))
    
End Sub
 
cs


R code


The following R code implements three operations:

  1. Write input array to Excel
  2. Run macro1()
  3. Read output array from Excel
  4. Using OIS DFs and Adjusted Forward Rates,
    Calculate Swap Price at Inception

R code is similar to the that of previous post because R does not perform the core part of calculation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
#=========================================================================#
# Financial Econometrics & Derivatives, ML/DL using R, Python, Tensorflow  
# by Sang-Heon Lee 
#
# https://kiandlee.blogspot.com
#————————————————————————-#
# OIS swap pricing by using a VBA macro in R through RDCOMClient
#=========================================================================#
 
library(RDCOMClient)
 
graphics.off()  # clear all graphs
rm(list = ls()) # remove all files from your workspace
 
#===============================================================================
# functions using RDCOMClient
#===============================================================================
 
f_read_vector <– function(xlWbk1, sheet1, range1){
    
    sheet <– xlWbk1$Worksheets(sheet1)
    range <– sheet$Range(range1)
    data  <– do.call(“cbind”,range[[“Value”]])
    data  <– matrix(unlist(data), dim(data)[1], dim(data)[2])
    return(data)
}
 
f_write_vector <– function(xlWbk1, sheet1, range1, data1) {
    
    sheet <– xlWbk1$Worksheets(sheet1)
    range <– sheet$Range(range1)
    range[[“Value”]] <– asCOMArray(data1)
}
 
#===========================================================
# MAIN
#===========================================================
    
    # set working directory
    setwd(“D:/SHLEE/blog/excel_com”)
    
    # Create Excel Application
    xlApp <– COMCreate(“Excel.Application”)
    
    # Open the Macro Excel book
    fn <– “sample_ois.xlsm”
    xlWbk <– xlApp$Workbooks()$Open(paste0(getwd(),“/”,fn))
    
    # use TRUE for Excel Spreadsheet to be visible
    xlApp[[‘Visible’]] <– TRUE # FALSE 
 
#===========================================================
# Communicate between R and Excel
#===========================================================
 
    # Arguments for Excel Spreadsheet and VBA macro
    sheet      <– “Sheet1”
    range_in   <– “D4:E11”
    range_out  <– “H4:I11”
    macro_name <– “macro1”
    
    #————————————————–
    # Pass Input Market Swap Rates to Excel
    # and Get OIS DFs and adjusted FWD Rates from Excel
    #————————————————–
    
    # 1) write input values from R to Excel
    #    1st column : OIS swap rates
    #    2nd column : Libor Swap Rates
    m.input <– rbind(c(0.00100, 0.00500), 
                     c(0.00620, 0.01040), 
                     c(0.01100, 0.01580), 
                     c(0.01640, 0.02120), 
                     c(0.02004, 0.02440), 
                     c(0.02354, 0.02760), 
                     c(0.02676, 0.03080), 
                     c(0.02958, 0.03400))
    
    f_write_vector(xlWbk, sheet, range_in, m.input) 
    
    # 2) run Excel macro
    xlApp$Run(macro_name)
    
    # 3) read output values from R to Excel
    m.output <– f_read_vector(xlWbk, sheet, range_out)
 
    print(cbind(m.input, m.output))
    
    #————————————————–
    # Libor IRS pricing with OIS discounting
    # Check if a swap price is at par for each maturity
    #————————————————–
    
    v.Lib_SR <– m.input [,2] # Libor Swap Rates (input)
    v.OIS_DF <– m.output[,1] # OIS discount factor (output)
    v.adj_FD <– m.output[,2] # Adjusted Forward Rate (output)
    
    for(i in 1:nrow(m.output)) {
        v.fixed_leg <– sum(v.Lib_SR[i]*(3/12)*v.OIS_DF[1:i])
        v.float_leg <– sum(v.adj_FD[1:i]*(3/12)*v.OIS_DF[1:i]) 
        swap_pr <– v.fixed_leg – v.float_leg
        print(paste0(i,“-quarter swap price = “, swap_pr))
    }
 
#===========================================================
# save and quit
#===========================================================
xlWbk$close(TRUE); xlApp$Quit() 
    
cs


Results


The following console shows two outputs: 1) OIS discount factors and adjusted forward rates, 2) swap pricing at inception for each maturity. As you can see, we can get swap prices at inception which are correctly at par (zero).

Conclusion


The core part of this above swap pricing is based on the VBA code from other sources. Therefore, when we have some good VBA resources other than R, we can use them in R for efficient process. I think that this is a kind of “standing on the shoulders of giants”.

Of course, this Libor IRS pricing with OIS discounting is so simple that we can deal with this issue more concretely in the next time \(\blacksquare\)

To leave a comment for the author, please follow the link and comment on their blog: K & L Fintech Modeling.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.