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:

[Rate Formula

Now the goal seek method:

Goal Seek Method

  • $\begingroup$ How did you get 3.347% using Rate () function in excel? I got 3% in excel using the same data. $\endgroup$ – Dhamnekar Winod Jul 14 '18 at 4:49
  • $\begingroup$ Maybe your value is rounded off $\endgroup$ – gaston Jul 16 '18 at 14:06

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.