Excuse my naivete, but I have a simple bond math question. I was asked to calculate the duration for a 10 year bond at 12%, with a refinance at year 5 at par, and 20% amortization.
I started by discounting the coupon (12) for years 1-4 and coupon+principal (112) for year 5. I used the excel pmt function (PMT (12, 5, -100, 0, 0)) to find the annual payments and discounted accordingly. So essentially I viewed this as 2 5-year bonds. Does this make sense to you? This yields a Mac duration of ~5.1, compared to a duration of ~6.1 for a 10 year bond at 12% with no refi.
Any insight would be greatly appreciated.
Following is my view.
if you are using PMT function then installments include both coupon and principal. So your cash flow setup of discounting only coupons for first four years and coupon plus principal in the fifth year does not make sense. since its an 20% p.a. amortizing bond then in the fifth year you should net off last installment of existing bond with the principal of refinanced bond them calculate duration.
Nevertheless there is no need to use PMT function in your case because PMT makes installments amounts same with varying principal repayments starting from low to high. since yoir bond is amortized 20percent every year it means principal payments are same in every yeat but not the installment. so at the end of every year you should discount 12+20% of principal.