# YIELD function giving wrong results

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

any help plz?

#### skywriter

##### Well-known Member
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

##### New Member

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

#### joeu2004

##### Banned user
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

##### New Member
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

##### Banned user
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 . 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.

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

Last edited:

#### joeu2004

##### Banned user
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

##### Banned user
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 . 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:
Code:
``````                                  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.

Last edited:

#### Nirav D

##### New Member
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!

Replies
11
Views
588
Replies
6
Views
110
Replies
8
Views
285
Replies
6
Views
866
Replies
6
Views
676

### Forum statistics

1,191,671
Messages
5,987,956
Members
440,121
Latest member
eravella ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back