Since you have my book I'll give you a step by step.
You have choices to make here. How you you want to parametrise your Curve, what interpolation do you use, which node points for the discount factors, etc. Since you are doing this in Excel you have constraints. One is that you need this to be bootstrappable.
If you do this in a library you can easily use a numerical solver, such as the following:
from rateslib import * curve = Curve( nodes={ dt(2023, 1, 3): 1.0, dt(2023, 7, 3): 1.0, dt(2023, 8, 3): 1.0, dt(2023, 9, 3): 1.0, dt(2023, 10, 3): 1.0, dt(2023, 11, 3): 1.0 }, calendar="ldn", convention="act365f", interpolation="log_linear", )
And then solve and update the Curve values based on market prices. I am using GBP instruments to ignore payment delays and T+2 spot.
solver = Solver( curves=[curve], instruments=[ IRS(dt(2023, 1, 3), "6m", spec="gbp_irs", curves=curve), IRS(dt(2023, 2, 3), "6m", spec="gbp_irs", curves=curve), IRS(dt(2023, 3, 3), "6m", spec="gbp_irs", curves=curve), IRS(dt(2023, 4, 3), "6m", spec="gbp_irs", curves=curve), IRS(dt(2023, 5, 3), "6m", spec="gbp_irs", curves=curve), ], s=[2.379, 3.007, 3.229, 3.995, 3.495] )
This is what this produces:
curve.nodes # {datetime.datetime(2023, 1, 3, 0, 0): <Dual: 1.000000, ('0d6f0_0',), [1.]>, # datetime.datetime(2023, 7, 3, 0, 0): <Dual: 0.988340, ('0d6f0_1',), [1.]>, # datetime.datetime(2023, 8, 3, 0, 0): <Dual: 0.983330, ('0d6f0_2',), [1.]>, # datetime.datetime(2023, 9, 3, 0, 0): <Dual: 0.980333, ('0d6f0_3',), [1.]>, # datetime.datetime(2023, 10, 3, 0, 0): <Dual: 0.974663, ('0d6f0_4',), [1.]>, # datetime.datetime(2023, 11, 3, 0, 0): <Dual: 0.975075, ('0d6f0_5',), [1.]>}
Now you want to do this in Excel. Setup the same scheme. You will have the first discount factor (blue) set to 1.0 as identity. Your yellow cells are the parameters calibrated by the rates. Your Grey cells are the intermediate calculations required under interpolation to get the right values.
The formulae for log_linear interpolation look like this:
