Gyroscopic Investing

akratic wrote: Yeah MediumTex and dualstow, that's exactly how I valued my physical gold until I figured out a formula to get the spot price directly from a gold retailer: =Index(ImportHTML("http://apmex.com/","table",7),2,2)

I'm hoping to do better than using TLT (and SHY/SHV for the cash ladder) as a proxy though.

akratic wrote: I want to sell TLT and buy individual bonds.  After Gumby's great tutorial, the only thing holding me back is figuring out how to automatically value the individual bonds in my PP Google Docs Spreadsheet.

Assuming that goes well, I'm also planning to build a 0-3 year treasury ladder for my cash.  Hopefully whatever method for valuing the 30Y bonds also works for shorter duration bonds.

I've been searching around for how to do this, and the only post I can find on the topic is from fnord123 here, but I don't entirely understand it.  fnord123 gets the 30Y price from yahoo, and then plugs it into this =PRICE black box along with information about his bond.  Is this is the best way to value treasuries in Google Docs?  Does anyone else have a different way?  Can anyone explain how fnord's way works?

For my 0-3 year treasury ladder, what bond value should I be importing from Yahoo to plug in the =PRICE formula?

Thanks!

Kriegsspiel wrote: I have mine importing like this:

=Index(ImportHTML("http://fixedincome.fidelity.com/fi/FIBo ... ,"table",0),5,2)*10

It comes back with a lot of errors though, I'll try out some of the ways in this thread.

Tortoise wrote:

akratic wrote: I want to sell TLT and buy individual bonds.  After Gumby's great tutorial, the only thing holding me back is figuring out how to automatically value the individual bonds in my PP Google Docs Spreadsheet.

Assuming that goes well, I'm also planning to build a 0-3 year treasury ladder for my cash.  Hopefully whatever method for valuing the 30Y bonds also works for shorter duration bonds.

I've been searching around for how to do this, and the only post I can find on the topic is from fnord123 here, but I don't entirely understand it.  fnord123 gets the 30Y price from yahoo, and then plugs it into this =PRICE black box along with information about his bond.  Is this is the best way to value treasuries in Google Docs?  Does anyone else have a different way?  Can anyone explain how fnord's way works?

For my 0-3 year treasury ladder, what bond value should I be importing from Yahoo to plug in the =PRICE formula?

Thanks!

fnord123's method of calculating individual bond prices seems to be correct. If you're interested in the math, the =PRICE() financial formula in Google Finance and Microsoft Excel implements the following equation for bond valuation:

P = C*[1-1/(1+i)^N]/i + M/(1+i)^N

where

C = coupon payment (periodic interest payment)
N = number of coupon payments remaining
i = market interest rate, i.e. required yield (per coupon payment period, so divide by 2 in the case of semi-annual coupon payments)
M = value at maturity (face value)
P = market price of bond

I spot-checked a few different 30-year Treasury bonds on Schwab (including one I own). Looking up the 30-year yield on Yahoo Finance, I obtained exactly the same result both by using the automatic =PRICE() formula and by manually entering the mathematical equation above into Excel. For all of those bonds, my computed price was within 0.03% of the market price listed on Schwab. Not bad!

Since the face value, interest rate, coupon payment frequency, and maturity date of the bond all remain fixed, the only two arguments to the =PRICE() formula you need to automatically update are (1) the current date ("settlement date") and (2) the market yield. As fnord123 pointed out, you can pull the yield from Yahoo Finance using the formula he specified.

However, if your bond has fewer than 30 years to maturity, make sure you adjust the yield accordingly. For example, using the 30-year yield for a bond with 25 years to maturity might produce a somewhat inaccurate price. To keep it accurate, have your spreadsheet pull in both the 20- and 30-year yields (perhaps by doing an HTML or XML import from the Treasury web site) and take an appropriate weighted average of the two yields.

For example, assume i20 is the 20-year yield and i30 is the 30-year yield. If your bond has 23 years to maturity, the yield is roughly (30-23)/10*i20 + (23-20)/10*i30 =  0.7*i20 + 0.3*i30. And for a bond with 25 years to maturity, the weighted average is (as you might guess) simply 0.5*i20 + 0.5*i30.

This method may not be as simple as the TLT-times-fudge-factor approach, but it's probably more accurate and should please the purists and the mathematically-inclined.

jswinner wrote: Dualstow:  Is this still working for you, my sheet is not returning the price.

PRICE(settlement, maturity, rate, yield, redemption, frequency, basis)

Calculates the market value of a fixed interest security with a par value of 100 currency units as a function of the forecast yield. Settlement is the date of purchase of the security. Maturity is the date on which the security matures (expires). Rate is the annual nominal rate of interest (coupon interest rate). Yield is the annual yield of the security. Redemption is the redemption value per 100 currency units of par value. Frequency is the number of interest payments per year (1, 2 or 4). Basis is chosen from a list of options and indicates how the year is to be calculated.

dualstow wrote: @Khisanth,
I followed your advice and changed only the dates and yield, leaving the Index portion unchanged, and it worked. No CUSIP necessary I guess, since the dates and % are supplied by me.

Can I ask a question that is probably obvious to most? Where did you get the

Code: Select all

Index(ImportHTML("http://money.cnn.com/data/bonds/","table",3),6,2)

part? Is there someplace on CNN where you can see the table with your own eyes?

flyingpylon wrote: The page can be viewed at http://money.cnn.com/data/bonds/.  However it's important to note that the function relies on the HTML source code, not necessarily what you can see when you visit the page normally with a browser.