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.

 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778 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 Colored by Color Scripter 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.

 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109 #=========================================================================## 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 graphsrm(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), dim(data))    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()     Colored by Color Scripter 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$$