YIELD function giving wrong results

[Nirav D]

I want to calculate yield to maturity of 3 bonds.

I have uploaded the calculations of those 3 bonds in an excel file below. I am not sure why bond A and bond C is giving wrong result. Bond B is ok.

I have checked all formats and everything but can't figure out whats the problem.

Here's the link:

https://onedrive.live.com/redir?resid=2482B878A351846C!202&authkey=!AI7bWfliWCCP5UE&ithint=file,xlsx

 

[Nirav D]

any help plz?

 

[skywriter]

I can't find a problem. I looked up yield in the help for Excel. They give example data and the expected result.
I entered their data in where you have your first formula and left your formula alone. The result was what the help example said it should be. I'm in no way an expert, but the formula is calculating correctly according to what you put in. I would double check the numbers you put in if you are sure the results should be different.

 

[Nirav D]

Thankyou for your confirmation skywriter.

I am too wondering why we are getting wrong results when the formula entered is correct?!

 

[joeu2004]

I am too wondering why we are getting wrong results when the formula entered is correct?!

The correct explanation was provided in answers.microsoft.com. As the YIELD help page states (in my English version of XL2010, at least):

Pr.... Required. The security's price per $100 face value.
Redemption... Required. The security's redemption value per $100 face value.

(And of course, that should say "per 100" since the function does not know the unit of currency.)

Stating bond values per $100 is a common convention for US bonds, at least.

 

[Nirav D]

Pr.... Required. The security's price per $100 face value.
Redemption... Required. The security's redemption value per $100 face value.

Thanks joeu2004 for your explanation. Yes I agree that on changing the face value of bond from 1000 to 100 gives correct answer.

What I am wondering is why doesn't the formula work if the bond values are in 1000s? In India the bonds are face valued in Rs 1000. Will converting them into 100s give correct results?

And if excel needs 100 as input then why we need to enter it in first place?! Why doesnt excel take it as default?

 

[joeu2004]

What I am wondering is why doesn't the formula work if the bond values are in 1000s?

No reason that I can think of. See the IRR usage below.

In India the bonds are face valued in Rs 1000. Will converting them into 100s give correct results?

Yes, I believe so.

And if excel needs 100 as input then why we need to enter it in first place?!

Because the redemption value is not always par value (100).

Consider an 8-year bond priced at 8252.56 that is redeemed at maturity at 10,000 and that pays 1200 (12%) in annual coupons.

The correct yield of 16% can be calculated with:
=YIELD("10/1/2014","10/1/2022",12%,8262.56/100,10000/100,1).

To confirm, note that the following returns 16%:
=IRR({-8262.56,1200,1200,1200,1200,1200,1200,1200,11200})

(The last cash flow is the 1200 coupon plus the 10,000 redemption value.)

But now assume the bond is callable after 4 years.

To return the same 16% yield, the call price should be 8880.72 [1]. To confirm, note that the following returns 16%:
=IRR({-8262.56,1200,1200,1200,10080.72})

(The last cash flow is the 1200 coupon plus the 8880.72 call price.)

The following YIELD function usage returns 16%:
=YIELD("10/1/2014","10/1/2018",12%,8262.56/100,8880.72/100,1)

Note that the "redemption" parameter (#5) is 88.8072, not 100.


-----
[1] Call price: =FV(16%,4,1200,-8252.56)

 

[joeu2004]

Heads up....

Yes I agree that on changing the face value of bond from 1000 to 100 gives correct answer.. What I am wondering is why doesn't the formula work if the bond values are in 1000s?

No reason that I can think of. See the IRR usage below.

I'm in the process of writing a clarification. But it is taking some time, and I'm concerned that with the time difference, you might not see the complete answer. So if you do not see yet-another "clarification" response from me, please look back again some time later.

 

[joeu2004]

Errata....

I agree that on changing the face value of bond from 1000 to 100 gives correct answer.. What I am wondering is why doesn't the formula work if the bond values are in 1000s?

No reason that I can think of. See the IRR usage below.

Actually, the IRR examples demonstrate the need to assume that price and redemption are per 100.

Consider an 8-year bond priced at 8252.56 that is redeemed at maturity at 10,000 and that pays 1200 (12%) in annual coupons.
[....]
To confirm, note that the following returns 16%:
=IRR({-8262.56,1200,1200,1200,1200,1200,1200,1200,11200})
[....]
But now assume the bond is callable after 4 years.

To return the same 16% yield, the call price should be 8880.72 [1]. To confirm, note that the following returns 16%:
=IRR({-8262.56,1200,1200,1200,10080.72})

Those IRR formulas are correct. And they are useful for validating the results from YIELD insofar as the IRR formulas use the actual cash flows, not adjusted per 100, which is the source of confusion.

But in the second example (called bond; "early redemption"), how can YIELD know the coupon cash flows are 1200?

The YIELD parameters only provide price (8262.56) and "early" redemption amounts (8880.72) in some form. 1200 is based on the bond value at maturity (12% of 10,000).

Answer.... By stating price and redemption per 100, YIELD can know the coupon cash flows are 12 (12% of 100). That is, for YIELD, the effective IRR formula is:

=IRR({-82.6256,12,12,12,100.8072})

where the last cash flow is 12+88.8072.

-----

Another place where YIELD relies on the "per 100" assumption is in accounting for a first "short period".

The examples I offered all involve complete coupon periods. But if the settlement date is not the beginning of a coupon period (working backwards from the maturity date), we must account for the first "short period".

For example, the YIELD help page shows the formula to calculate yield when there is one coupon period or less between the settlement and redemption ("maturity") dates.

Rearranging the formula algebraically (and correcting a critical typo: "par" should be "price"), we can see the "per 100" assumption when prorating the coupon amount. The formula is:

                                  rate 
yield =     ( redemption + (100 * ----) )
                                  freq                E
        ( --------------------------------- - 1 ) * (---) * freqs
                             A   rate                DSR
            ( price + (100 * - * ----) )
                             E   freq

DSR <= E

A + DSR = E

Ostensibly, yield is the %gain ( redemption/price - 1 ) prorated to a full coupon period ( times E/DSR ) and annualized ( times freq ).

But we must add the full coupon amount ( 100*rate/freq ) to redemption, and we must add the accrued interest ( 100*(A/E)*rate/freq ) to price.

Again, given only price and redemption, we can only know the coupon amount if we assume that all amounts are stated per 100.

 

[Nirav D]

Thanks joeu2004! I will take some time to study all what you have mentioned. It is too much for me to digest at a time.

If I find any more confusion than I'll let you know through this post.

Thanks again!