Lets say you have data of monthly change in interest rates Which formula would tell you the % gain or loss of such bond as interest rates change? Assuming the maturity stays constant
If you want a very rough calculation, you multiply the change in yield by DV01 to get the mark-to-mkt PNL. Against that, you need to add/subtract the carry and roll PNL, if you want to get a "total return". For a 10k notional position in these 10y bonds (assuming they're trading at par, so mkt value of arnd $10000), DV01 will be the same number as modified duration. Modified duration for 10y US bonds will live somewhere between 7 (when rates are high) and 9 (when they're low). Et voila...
Can't you use PV() or PRICE() in excel to get the bond values?.... although that's the theoretical values not actually traded...
I dont think I can use this at all. The yields change dramatically in my data set. They start at 5% ish but go as high at mid teens and as low as 1.5%. Both the DV01 and the Modified Duration are constantly changing. Damodaran uses a formula in his Excel sheet =((B8*(1-(1+B9)^(-10))/B9+1/(1+B9)^10)-1)+B8 B8 is the prior yield, B9 is the next yield I asked him how to adapt this to monthly data and he said to just put /12 after the +B8 at the end but that did not work and the outputs made no sense ("+44%")
Careful when using these calculations. Works on US government bonds because there weren't any defaults (yet). Do not use any of this arithmetic on Argentina/Greece/Russia or corporate bonds since it doesn't account for defaults.
Damodaran's formula is correct including /12 part, +44% is feasible if yields drop around 5%. For more precision you can replace 10 with 9.9167 to account for a new security having 1 month shorter maturity and interpolate between your new yield of the 10y with some shorter term bond (2y, 5y?) to account for the fact a bond with 9.9167 maturity will likely have bit smaller yield than the 10y. Even then you'll be approximating because that formula works for a flat yield curve. If curve is very steep, it will underestimate the gains (kinda like cheapest CTD's in futures are ones with the highest coupon in such environments).
i solved the issue. the cells targeted in the formula were in decimals instead of percent, that was causing the problem
Does this Damodaran formula stops working when there is huge volatility? I'm referring to using it in the way he intended (yearly data). I'm finding a significant discrepancy between his formula and a simple bond price delta change + coupon interest = total annual bond return