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.


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



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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.