Finding Interest Rate Tree Nodes by Counting Up Moves

Summary

The document addresses how to map a sequence of up and down moves in a short rate tree to the interest rate at a later node. It recommends simplifying the spreadsheet logic by counting the number of up moves rather than evaluating every possible path separately.

At a given step, paths with the same number of up moves reach the same node, so the count identifies the rate regardless of the order of the moves. The answer illustrates this with possible move counts at node three and sample rates for two cases. This relies on a recombining tree in which up and down paths converge to shared nodes. The post is a concise modeling suggestion, not a full description of the tree construction, simulation process, or validation of the cited rates.

Key ideas

• In a recombining interest rate tree, paths with the same number of up moves reach the same node.
• Count up moves to identify the node's rate instead of encoding each path as nested conditions.
• The method assumes the tree recombines so that move order does not change the resulting node.
• The example addresses spreadsheet mapping rather than the broader design or validation of a Monte Carlo model.

Tags

# Issue with Monte Carlo Simulation on an interest rate tree in Excel


# Issue with Monte Carlo Simulation on an interest rate tree in Excel












I need to build a Monte Carlo simulation model that does 10 iterations on the interest rate tree. I need to identify the interest rate given a sequence of moving up and down the interest rate tree. I managed to create an IF statement until i=2, however it gets tricky on i=3 onwards.

Any suggestions? I was told to use the INDEX() function, but I fail to see how I can use it as a condition.

Thanks!

EDIT: This is what I have on node 2 -> =IF(U4="up",IF(X4="up",D10,D11),IF(X4="up",D11,D12))

## Answer by xing gao (score 1)

https://quant.stackexchange.com/a/61440

Well, you make it too complicated. An easy way is to count how many "up"s or "down"s before that node. For example, for the node 3, there can be 3 ups 0 downs, 2 ups 1 downs, 1 ups 2 downs, 0 ups 3 downs. 3 ups 0 downs will point to 0.0495. 2 ups 1 downs is 0.0251.....

So no matter the ups/downs sequence, only the numbers of ups/downs can tell you the answer.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.