Earlier we had defined the input cells for the Heath-Jarrow-Merton (HJM) multifactor no-arbitrage model in EXCEL. In this post we will see how the calculation cells are built up. These include cells for calculating the Brownian shocks, Drifts and Spot and Forward rates.

## Brownian Shocks

Brownian shocks to be applied to the volatility factors in the HJM model, dz_{i, }are obtained as follows. Using Excel’s random function, RAND () we scale the resulting number to the normal distribution by applying the inverse of the standard normal distribution function NORMSINV(). Combined this can be determined in excel using the following formula: NORMSINV(RAND()). These values are derived for each Factor across all time intervals. Whenever the user hits the F9 function button on their key board new results for this formula will be simulated:

## Drifts

**Driftk _{t}**=0.5*dt*( ?

_{x =1 to t }Factork

_{x})

^{2}– (?

_{i=0 to t-1}Driftk

_{i}), for k = 1, 2, 3 and where Driftk

_{0}=0 and.

**Total Drift _{t}**= Drift1

_{t}+Drift2

_{t}+Drift3

_{t}

## Spot and Forward Rates Matrix

The one-period spot / short rate (=F_{0,1}) and forward rates (F_{t,t+1}) are derived using the initial yield curve, Brownian shocks, drifts and scaled volatility factors (Factors 1, 2 and 3) and the following formula:

At time index 0 (as given by the first column in the diagram above) the spot and forward rates are derived using the following formula:

Spot Rate_{0}= Initial Yield_{1} = 0.30%

Forward Rate_{0} (t,t+1)=Time Index_{t+1}*Initial Yield_{t+1}– Time Index_{t}*Initial Yield_{t} , for t = 1 to 10

Where t is the Time Index (as given by the first row in the diagram above)

For example, Forward Rate_{0} (1,2)=Time Index_{2}*Initial Yield_{2}– Time Index_{1}*Initial Yield_{1}=2*0.65%- 1*0.30% = 1%.

(*Note this is equivalent to the continuous formula for forward rates e ^{f(T2-T1)}=e^{r2T2}/ e^{r1T1}*).

At time index 1 and later (as given by the first column in the diagram above) the one-period spot and forward rates are derived using the following formula:

Spot Rate_{1 }= Forward Rate_{0} (1,2)+Total Drift_{1}*dt+?dt*(Factor1_{1}*dz1_{1}+ Factor2_{1}* dz2_{1}+ Factor3_{1}* dz3_{1})

Spot Rate_{2 }= Forward Rate_{1} (1,2)+Total Drift_{1}*dt+?dt*(Factor1_{1}*dz1_{2}+ Factor2_{1}* dz2_{2}+ Factor3_{1}* dz3_{2})

Spot Rate_{3 }= Forward Rate_{2} (1,2)+Total Drift_{1}*dt+?dt*(Factor1_{1}*dz1_{3}+ Factor2_{1}* dz2_{3}+ Factor3_{1}* dz3_{3})

…and so on

Forward Rate_{1}(t,t+1)= Forward Rate_{0} (t+1,t+2)+Total Drift_{t+1}*dt+?dt*(Factor1_{t+1}*dz1_{1}+ Factor2_{t+1}* dz2_{1}+ Factor3_{t+1}* dz3_{1})

Forward Rate_{2}(t,t+1)= Forward Rate_{1} (t+1,t+2)+Total Drift_{t+1}*dt+?dt*(Factor1_{t+1}*dz1_{2}+ Factor2_{t+1}* dz2_{2}+ Factor3_{t+1}* dz3_{2})

…and so on

Where t is the Time Index (as given by the first row in the diagram above)

For time index 2 to 10 (as given by the first column in the diagram above) the same procedure will be used.

In this post we constructed the calculation cells for the HJM model, in particular cells for Brownian Shock, Drifts including Total Drift and the spot and forward rate matrix. In the next post we will define the output cells for the model and see what the results to the calculation are.