I'm watching some FRM videos teaching how to find YTM via excel's =rate() function and tried getting the YTM using Paul Wilmott's spreadsheet that uses goal seek. I'm getting different results.
First the rate() formula:
[
Now the goal seek method:
Quantitative Finance Stack Exchange is a question and answer site for finance professionals and academics. It only takes a minute to sign up.
Sign up to join this communityI'm watching some FRM videos teaching how to find YTM via excel's =rate() function and tried getting the YTM using Paul Wilmott's spreadsheet that uses goal seek. I'm getting different results.
First the rate() formula:
[
Now the goal seek method:
I did some tinkering with the numbers...
It seems to me that Wilmott is expressing his answer as a continuous time interest rate.
Notice that $e^{0.0658}=1.03347\times2$. That is how your answer 3.347 per period and his answer 6.58 for 1 year can be reconciled. He is working with $e^{rt}$ and you are working with $(1+r)^n$. Your answer is the industry standard method for finding YTM for US Treasuries, his answer is for quants who like to express all interest rates in continuous time.
So this is why they differ. I don't have the code for Wilmott's sheet but if you check I believe you will be able to confirm my hunch.