Message Font: Serif | Sans-Serif

No. of Recommendations: 2
YTM,

Excel offers decent tools for calculating various types of yields. Poke around in the Formulas section to find them. Let's work an example. Assume you have downloaded a couple hundred bonds into a spreadsheet and you’re checking the broker-calculated yields. Assume your data is in columns A through D, one row per bond.
`  A  	         B	    C	           D	1 Alcoa		5.870	02/23/22	60.6472 3ShopKo	9.250	03/15/22	60.5483 Burlington	3.200	01/01/45	60.200`

=YIELD(today(), C1, B1/100, D1, 100, 2, 1)

Obviously, any fact about any bond could be put into any column of the spreadsheet, and any fact could be entered in many different formats. Also, “settlement” is T-3, not “trade date”. But that formula will get you going. E.g., if today is Thursday, 03/17/2011, and if the ASK is 60.500, and if you're buying a single bond at Fido and paying \$8 in commish, and if you want to get fussy about settlement, then the formula would look like this:

=YIELD("03/22/2011", "07/24/2045", 0.032, 61.300, 100, 2, 1)

where the syntax is YIELD(Settlement, Due, Coupon_Rate, Price, Par, Frequency_of_Coupon, Year_Type), and the YTM is 6.1% (if I haven't screwed up on my math).

Caution: In buying any bond, not just long-dated ones, you need to estimate the impact of inflation on yield. (But that's a post for another time.)