AmortFloatingRateBond Example VBA

VBA Example - AmortFloatingRateBond

' ##################################################################################

' The first function here AmortFloatingRateBond(), contains a series of

' function calls leading upto the main function call, the second function

' within this file ( AmortFloatingRateBondPart() ).

' which contains the answer that we are looking for.

' The first function here is simply an example of how to construct the parameters

' in order acquire either a string Key (that is to be passed to other functions)

' or a computed result.

' If you are viewing this source code from the chm or web help file you can use the

' outlining features to collapse certain sections of the code for better readability.

' ##################################################################################

' Add a reference to the CTQL_VBA_API.xla file via the Tools->Reference menu (within the VBA editor).

' This holds VBA class objects for communicating with the CTQuantToolsXL20.dll Excel Addin

' This XLA does not use COM and has access to all the financial objects created via the spreadsheet functions.

' Some global parameter in order to append to user defined keys.

' We use it here to ensure that we have unique Keys (in the case several of our examples

' use the same key-name)

' In normal use, a user defined string will be used and so this variable will be pointless.

Global nCTBondsGlobal As Long

Public Function VB_EX_AmortFloatingRateBond() As String

nCTBondsGlobal = nCTBondsGlobal + 1

On Error GoTo err_Generic

' Calendar for reproducing theoretical calculations.

Dim MyNullCalendar As String

MyNullCalendar = _

EmptyCalendarPart()

' UK date calendar used within the UK stock exchange.

Dim MyCALUKExchange As String

MyCALUKExchange = _

CALUKExchangePart()

' EURO calendar used for holiday adjustments.

Dim MyEuroCal As String

MyEuroCal = _

CALEUROPart()

' Creates a centralized valuation date object.

Dim MyValuationDate As String

MyValuationDate = _

ValueDateObjPart()

' Creates a Deposit template which is almost identical to a Libor

' Index, but without the YieldCurve information.

Dim MyDepoTPL As String

MyDepoTPL = _

CreateDepoTemplatePart( _

MyCALUKExchange, _

MyEuroCal)

' Creates a Swap template which is almost identical to the definition

' of the parameters of a swap contract, but without the swap duration,

' buysell, and YieldCurve information.

Dim MySwapTPL As String

MySwapTPL = _

CreateSwapTemplatePart( _

MyEuroCal, _

MyDepoTPL)

' Creates a yield curve using market rates (No cross-currency

' Swaps).

Dim MyYCInterpOnDCF As String

MyYCInterpOnDCF = _

MKTYC_DPart( _

MyValuationDate, _

MyDepoTPL, _

MySwapTPL)

' Creates a new Index code.

Dim MyNewIndex As String

MyNewIndex = _

CreateIndexPart( _

MyCALUKExchange, _

MyEuroCal, _

MyYCInterpOnDCF)

' Creates an amortised floating rate bond object.

Dim MyAmortFLTBond As String

MyAmortFLTBond = _

AmortFloatingRateBondPart( _

MyNewIndex, _

MyNullCalendar, _

MyYCInterpOnDCF)

' This is the result we are looking for.

VB_EX_AmortFloatingRateBond = MyAmortFLTBond

Exit Function

err_Generic:

MsgBox "Error: " & Err.Number & vbCrLf & Err.Description

End Function

' ///////////////////////////////////////////////////////////////////

Private Function AmortFloatingRateBondPart( _

MyNewIndex As String, _

MyNullCalendar As String, _

MyYCInterpOnDCF As String) As String

' Create example range for parameter AmortFloatingRateBond_BondDetails

Dim AmortFloatingRateBond_BondDetails As Variant

AmortFloatingRateBond_BondDetails = vRange.RangeFromStr _

( _

CStr("{") + _

CStr("100 | 0 | 0 ;") + CStr("100 | 0 | 0 ;") + CStr("100 | 0 | 0 ;") + _

CStr("100 | 100 | 0") + _

CStr("}") _

)

' Key Handle to be used for the new Bond object.

Dim MyAmortFLTBond As String

MyAmortFLTBond = "MyAmortFLTBond" & "_" & CStr(nCTBondsGlobal)

' When creating this object for the first time, set this parameter

' to a positive value.

Dim Reload As Long

Reload = 1

' The currency code that you want to associate with the Bond.

Dim Ccy As String

Ccy = "EUR"

' Number of days for the settlement of bonds.

Dim SettleDays As Long

SettleDays = 2

' Dated date of the bond.

Dim datedDate As Date

datedDate = DateValue("21/Apr/2005")

' First Coupon Date: Date that the first coupon is paid (if bond

' does not have an odd first period, leave blank or enter 0).

Dim firstCouponDate As Long

firstCouponDate = 0

' Penultimate Coupon Date: Date that the penultimate coupon is

' paid (if bond does not have an odd last period, leave blank

' or enter 0).

Dim penultCouponDate As Long

penultCouponDate = 0

' Maturity date of the bond.

Dim matDate As Date

matDate = DateValue("21/Apr/2015")

' Coupon DayCounter to use for the bond coupon payments.

Dim cpnDayCount As String

cpnDayCount = "30360"

' Business Day Convention to use.

Dim BusDayConv As String

BusDayConv = "ModifiedFollowing"

' Excel function call is : "CT.BOND.AmortFloatingRateBond()"

' Creates an amortised floating rate bond object.

Dim rAmortFloatingRateBond As String

' We call the CreateCTBonds() function via the CTQL module exposed from the CTQL_VBA_API.xla addin.

Dim oCTBonds As CTBonds

Set oCTBonds = CTQL.CreateCTBonds()

rAmortFloatingRateBond = oCTBonds.AmortFloatingRateBond( _

MyAmortFLTBond, _

Reload, _

Ccy, _

SettleDays, _

datedDate, _

firstCouponDate, _

penultCouponDate, _

matDate, _

MyNewIndex, _

AmortFloatingRateBond_BondDetails, _

cpnDayCount, _

MyNullCalendar, _

BusDayConv, _

MyYCInterpOnDCF)

AmortFloatingRateBondPart = rAmortFloatingRateBond

End Function