Continuous and Periodic Compounding Explain Different Yield to Maturity Results

Summary

The discussion explains a discrepancy between a yield to maturity calculated with Excel’s RATE function and one found using a spreadsheet goal-seek method. The proposed explanation is that the two outputs use different compounding conventions: the spreadsheet result is expressed as a continuously compounded annual rate, while the RATE result uses a periodic rate. These rates cannot be compared directly without converting one convention into the other.

The reply illustrates the relationship by comparing exponential accumulation with periodic compounding and says the periodic approach is standard for US Treasury YTM, while continuous rates are often used in quantitative models. This is offered as a likely explanation based on tinkering, not a verified inspection of the spreadsheet, whose underlying formula is unavailable in the discussion. The document does not provide the original bond cash flows or a general conversion walkthrough, so users should confirm the compounding convention and payment frequency in each calculation before drawing conclusions.

Key ideas

• Yield figures can differ when one calculation uses continuous compounding and another uses periodic compounding.
• Continuous compounding expresses accumulation exponentially, while periodic compounding applies a rate over payment periods.
• Convert yields to a common compounding convention before comparing them.
• The suggested explanation is a hypothesis because the spreadsheet’s formulas are not shown.

Tags

# Difference between Excel's Rate Function and Paul Wilmott's Goal Seek Method for finding YTM


# Difference between Excel's Rate Function and Paul Wilmott's Goal Seek Method for finding YTM












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:

## Answer by Alex C (score 3)

https://quant.stackexchange.com/a/40675

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.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.