In this and a couple of the following posts we will be demonstrating how a Heath-Jarrow-Merton (HJM) three factor no-arbitrage model may be constructed in EXCEL. In this post we will be defining the input cells for the model. This includes among other cells, the initial yield (zero) rates and the volatility functions (Factors 1, 2 and 3) derived using a Principal Component Analysis (PCA).

This construction is based on the worksheet built by Mark Broadie and Paul Glasserman.

Define Input Cells

Assign Input cells on the excel sheet. These are:

Term = Tenor being considered, example 3 years, 5 years, 10 years etc. This usually corresponds with the tenor of the instrument being valued.

No. of intervals = Number of compounding periods in the tenor. For annual compounding this would be Term ×1. This usually corresponds with the number of future coupon payments of the instrument being valued.

Length of intervals (dt) = Duration of the compounding period in years. In our illustration and in the rest of the document we assume an annual compounding period. It is calculated as Term/ No. of Intervals. This usually corresponds with the coupon paying frequency of the instrument being valued.

Sqrt (dt) = The square root of the length of intervals. This is the standard deviation of the normally distributed variable dzi.

Time Index (t) = The sequence of numbers representing the interval of time step being consider. The index represents a single time step. For example if the total term (T) in the analysis is 10 and the time step is dt=1 then the time index would be 0, 1, 2, …, 11. The reason for the additional time index entry (e.g. 11 in our illustration) at the end is given below.

Initial Yield Curve = This is the initial zero rate curve derived. Note that the rates are need for an additional time step because this rate will be used to calculate the forward rate for the final interval in our model

Scale = These are the calibrated weights or scaling factors applied to the volatility functions derived and selected using principal component analysis on the forward rates term structure.

Factor 1, Factor 2 and Factor 3 = These factors are functions of the selected volatility functions, the calibrated weights and time.

In this post we have seen how input cells to the HJM interest rate model are assigned on the EXCEL worksheet. The next post will consider how output cells will be defined.

If you would like to buy this course as a PDF file or the sample EXCEL sheets, please see the Interest Rate Modelling section at our online finance course store.

About the authorJawwad Farid

Jawwad Farid has been building and implementing risk models and back office systems since August 1998. Working with clients on four continents he helps bankers, board members and regulators take a market relevant approach to risk management. He is the author of Models at Work and Option Greeks Primer, both published by Palgrave Macmillan.Jawwad is a Fellow Society of Actuaries, (FSA, Schaumburg, IL), he holds an MBA from Columbia Business School and is a computer science graduate from (NUCES FAST). He is an adjunct faculty member at the SP Jain Global School of Management in Dubai and Singapore where he teaches Risk Management, Derivative Pricing and Entrepreneurship.