Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
I’m trying to calculate the yield of notes and bonds in an excel spreadsheet. I can’t seem to get the calculated yield to match quotes I find online.
For example, I calculate a yield for the 10 year treasury at 2.32%, whilst Bloomberg quotes it at 2.61%
The formula that I use is one I find all over the web:
It is more complicated than what you are trying to do.
You can not just divide the coupon by the price, that is a rough estimation.
Here are some of your mistakes :
1. the coupon is payable bi-annual, not annual
2. you forget the accrued interest
3. for the calculation of the accrued interest, you need to take into account the settlement date for accrual calculation
so the formulae is different for Monday versus a Friday
4. you can not calculate it linear, you need to calculate the polynomial, which turns it into a non linear problem.
Personally I calculate it in iterations until the error is sufficiently low (I get to the exact YTM (i have a Reuters, not a
Bloomberg, but the value is the same, as what you mentioned)
Took me few days to figure it out, posted the same question a while ago, nobody answered.
In order to talk the same vernacular, the yield on the Reuters/Bloomberg screens, is the YTM or Yield to Maturity.
I’ve got it working now. I ended up using QuantLib to do it. ( Actually I used QLNet ). There are a lot of details as you mentioned and QuantLib has addressed all of these. Thank you for your help.
I couldn't make it work with QLnet, there was always a difference
At the end i wrote my own iterative process, to approximate the yield towards sufficient precision
Are you spot/on with Bloomberg/Reuters ?
or just happy with approximation ?
"Nearly" spot on in my testing so far. Problem is that different quote providers aren't "spot on" with each other... at least not on websites which may have some latency ... therefore I can't tell from comparing to websites. But I'm within a hundredth of a percentage point.
I tested intra-day and also after close with market prices. I'm using websites to compare: MarketWatch and Bloomberg ( can't find the quote on Reuters )
I struggled with quantlib a little. It was giving me "close" results but not perfect. Until I found an example that employs a solver. The solver uses a bisection algo to find the internal rate of return. Here is the complete listing of the program.
To get it to work for the 10 year treasury I changed the period type passed to the Schedule constructor to be Frequency.Semiannual.
If you can give me a quote of price and yield for a 10 year on a Monday or Friday then I can run a test now using the same date as the settlement date. I can't find historical prices for the cash note online... only historical yields. If not then I'll run a test on Friday and get back to you then.
