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:
$\begingroup$
$\endgroup$
2
-
$\begingroup$ How did you get 3.347% using Rate () function in excel? I got 3% in excel using the same data. $\endgroup$– Win_odd DhamnekarJul 14, 2018 at 4:49
-
$\begingroup$ Maybe your value is rounded off $\endgroup$– ensabahnurJul 16, 2018 at 14:06
Add a comment
|
1 Answer
$\begingroup$
$\endgroup$
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.