2
$\begingroup$

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

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

1 Answer 1

3
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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