Yield vs XIRR formula

Hi all,

I am currently working on creating a bond valuation spreadsheet using the yield or XIRR function. To my understanding both of these functions should provide the same answer, however, I am not obtaining the same answer we I perform my calculations. Can you guys look at my attached spreadsheet and let me know if I am doing anything wrong.

Thanks

Bond Valuation.xls

YIELD function has given you an annual YTM where as XIRR has given you an annualized YTM

Annual YTM
YTM * 2

Annualized YTM
(1+YTM)^2 - 1

Using RATE function you will get periodic (semi-annual YTM) of 4.75%

From there you can get the annual and annualized YTM values as shown in the attached Worksheet

And you never accepted my answer to your Payback Period Problem a few months ago

Thanks MoneyMaker again for answering my question and more importantly explaining the answer. I accepted your answer to my other thread, my apologizes as I was not set up t receive email alerts when a thread was responded to.

I tried to add to your reputation again but it will not let me until I rep someone else. You are a true asset to this forum and I really appreciate the help!

MoneyMaker,

I was looking deeper into YTM vs IRR and I am having a hard time wrapping my head around the whole difference between the annual and annualized return and I was hoping you could provide some insight.

To my understanding an annualized return is taking a return that is less than or greater then a one year period and converting it to equal that of a one year period so it can be used for comparison purposes. I also understand that an annualized return takes in to consideration the effects of compounding interest.

To my knowledge the YTM of a bond also takes into consideration compounding interest (that all interest payments will be reinvested at the YTM until the bond matures). So wouldn't the annual return of the YTM also include compounding interest and thus have the same rate as the IRR?

Can you please explain this in a simple way that I can understand, it would be greatly appreciated!

I am not able to down the attachment, it says you donot have necessary permissions.