Reading 36: Section 3.4: Example 4
“By using numeric methods (in this case, Excel’s Solver), we find that the actual number for F1,1d = 3.442% instead of 3.477%, making F1,1u = 4.646% instead of 4.694%.”
The expression above is from the solution of the example. I know what is Excel’s Solver and how it works, but I don’t know how it is done in the text. I tried to replicate the valuation model in a spreadsheet in order to absorb the steps. URL is this: https://docs.google.com/spreadsheets/d/1-GJU_xhWXElkRw6-ASrWhQbWaA0c3BW0i4ESdiC6SlU/edit?usp=sharing
However, I couldn’t figure out the steps related to numeric methods. Can anyone dig into this and help me out to understand this step?
All they’re saying in the text is that they first calculated 1f1 and used that to approximate the up and down rates at time 1: multiplying by e0.15 and dividing by e0.15, respectively. That didn’t quite get the correct price on the 2-year bond.
Then they stuck it into Excel and told Solver to determine the down rate at time 1 so that they would get exactly the correct price on the 2-year bond; Solver changed the down rate, and the up rate was calculated as the down rate multiplied by e0.30.
As it turns out, you can determine the correct down rate analytically; when you write the formula for the price of the 2-year bond and multiply everything out, you get a quadratic equation in the down rate, which any kid in first-year algebra can solve using the quadratic formula.
