- Joined
- 7/24/09
- Messages
- 2
- Points
- 11
Hi,
Sorry if this a stupid question, but it has being troubling me for some days.
If I have the current MV of a bond and the current coupon rate and maturity how can I forecast an exponential Pull to Par.
I have sort of found a method for basic periodic coupons, using the IRR. (Please see the first sheet of the attached file and below) where the previous value is multiplied by 1+IRR less the coupon - very rudimentary but I am just after any kind of function that will give a exponential increase from T0 MV to Tn PAR.
As I want to use standardised buckets for my Pull to Par (IE ON, 1W, 2W, 1M, 2M, 3M....... 20Y 30Y 40Y) I have non periodic cashflows and so using the excell IRR function is a non starter, so I have tried using XIRR, unfortunately however I manipulate the formula I cannot arrive back at PAR (please see second sheet of attached workbook)
So my question has two parts
1) how do I get the the XIRR excell function to return PAR at maturity.
2) Is this the best method given that I just want something simple on excel (Could use VBA, as I have a small amount programming ability (really small)) and it is to be part of a workbook that analyses bonds in a number of ways.
Thanks for reading and sorry if it is a dumb question
Dan
Notional1210to-500Coupon5.00%t160.5MV500t260.5t360.5t41270.5IRR33.85159%to500t1608.75794t2754.33217t3949.1856t41210
Sorry if this a stupid question, but it has being troubling me for some days.
If I have the current MV of a bond and the current coupon rate and maturity how can I forecast an exponential Pull to Par.
I have sort of found a method for basic periodic coupons, using the IRR. (Please see the first sheet of the attached file and below) where the previous value is multiplied by 1+IRR less the coupon - very rudimentary but I am just after any kind of function that will give a exponential increase from T0 MV to Tn PAR.
As I want to use standardised buckets for my Pull to Par (IE ON, 1W, 2W, 1M, 2M, 3M....... 20Y 30Y 40Y) I have non periodic cashflows and so using the excell IRR function is a non starter, so I have tried using XIRR, unfortunately however I manipulate the formula I cannot arrive back at PAR (please see second sheet of attached workbook)
So my question has two parts
1) how do I get the the XIRR excell function to return PAR at maturity.
2) Is this the best method given that I just want something simple on excel (Could use VBA, as I have a small amount programming ability (really small)) and it is to be part of a workbook that analyses bonds in a number of ways.
Thanks for reading and sorry if it is a dumb question
Dan
Notional1210to-500Coupon5.00%t160.5MV500t260.5t360.5t41270.5IRR33.85159%to500t1608.75794t2754.33217t3949.1856t41210