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)