Estimating the zero coupon rate or zero rates using the bootstrap approach and with excel linest
https://sites.google.com/view/vinegar...
Deducing the term structure of Interest rates using bootstrapping
The zero curve or zero coupon yield curve maps interest rates on zero-coupon bonds to different maturities straddling time. Typically bonds do pay coupons but this complicates applying the appropriate discount rate for a given time period. Analysts typically resolve this by estimating the Zero-coupon bond equivalents and reduce bonds to having a single payment at a given maturity. A common technique for manual estimation involves bootstrapping i.e. stripping coupons sequentially. The zero curves enable you to price arbitrary cash flows, fixed-income instruments, and derivatives. Zero curves are separately constructed for government securities and for inter-bank markets.
Zero-coupon bonds are available for a limited number of maturities, so you typically construct zero curves with a combination of bootstrapping and interpolation techniques in order to build a continuous curve. Once you construct these curves, you can then use them to derive other curves such as the forward curve and to price financial instruments. Above, we follow John C Hull : Options, Futures and Other Derivatives. We then adapt John's estimation apply linest from Excel to automate.