I would like some guidance with the following please.
Suppose I have two yields:
2020 bond --> yield = 2.5
2027 bond --> yield = X (to be interpolated)
2030 bond --> yield = 2.8
How could I interpolate the 2027 bond yield, considering that it should be closer to the yield from 2030? I am doing this with excel
Thank you.
If these are risky (e.g., corporate) bullet bonds, then I would not interpolate the yield directly, because their yield has two distinct components: a risk-free rate and an additional spread (to compensate the bond holder for credit, liquidity, etc risks). You should look for observable risk-free benchmark (probably swap curve, but maybe treasury, depending on the context) for each of the 3 maturities. Then you have no need to interpolate those.
You then have spreads $s_1,s_3$ at maturities $m_1,m_3$. (I like Z-spread, but you may prefer asset-swap spread or some other spread, depending on the context.) Normally, the spreads should be close and so your choice of interpolation is not very material. You can interpolate the spread linearly with respect to the time if you like $w_1 = \frac{m_3-m_2}{m_3-m_1}$, $w_3=1-w_1=\frac{m_2-m_1}{m_3-m_1}$, and $s_2=w_1s1+w_3s_3$.
However if $s_1,s_3$ are very different, then you should dig in deeper (do your due diligence) and undersand why they are. E.g., if $s_3$ is much wider than $s_1$ because a lot of debt (including perhaps the second bond) needs to be repaid between $m_1$ and $m_3$, then you should consider where $m_2$ fits into this and perhaps manually give more weight to the wider spread.
Then you back out the second bond from the second risk-free benchmark and the spread $s_2$.
If any bonds amortize or pay very high coupon, then you may want to consider risk-free benchmarks at additional times for more cash flows than just the maturities. For example, you could project all the cash flows of the 3 bonds; assume some recovery in case of default (e.g. 40%, does not affect the result much); solve for a survival curve using first and third bonds and all of the swap curve (i.e. solve for constant hazard rate $h_1$ from now to second bond's maturity, and for constant hazard rate $h_2$ from second bond's maturity to third bond's maturity); price the second bond using this survival curve.
Linear interpolation. See, for e.g., https://en.wikipedia.org/wiki/Linear_interpolation. Easily implemented in Excel.
If you has a lot of points with wide matuirities you can use the specific Nelson–Siegel–Svensson (NSS) model. It closely follow academic researches for short-, mid- and long-term structures.
Real implementation fot python - https://github.com/luphord/nelson_siegel_svensson
