RDCOMClient : A Simple Libor IRS Pricing with OIS Discounting
[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). Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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:
- Write input array to Excel
- Run macro1()
- Read output array from Excel
- 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.