How to value direct bond holdings on Google Docs?

[akratic]

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!

[MediumTex]

You could use the actual volatilty of TLT to simulate the volatility of your individual bond holdings.

It won't be exact, but it will be pretty close.

Just take the starting value of your bond holdings and divide by the share price of TLT and that will get you a simulated number of TLT shares, which you can then track.

You can do the same thing with your physical gold holdings and GLD.

[dualstow]

Good question. I do something vaguely similar to what Med-Tex does, even though we're supposed to just look at monthly or yearly statements. ;-)

For TLT, I have a cell for chg and one for chg %, and I multiply the former by # of shares to see the day's change in dollars.
I take that figure and multiply it by 0.8 to estimate the $ change for one of my long term treasurys. Why 0.8? It just seems to be close. I checked the real change in Fidelity and it was always very close when I used 0.8, which I found by trial and error.

Again, though, we shouldn't be looking so closely.

[akratic]

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.

I get a lot of utility from my little PP spreadsheet, so it's worth it for me to try to fully understand bond valuation to the point where I can put it in a spreadsheet.  Perhaps what I really need is to find a textbook or wiki on bond valuation.  I tried to start with wikipedia, but I got kind of overwhelmed, not knowing which valuation method to use.

=== Begin digression ===

Speaking of comparing TLT to 30Y bonds, or SHY to 2Y or 3Y bonds, I'm even confused about that:

Dividend yields from yahoo finance:
SHY: 0.91% as of Sep 30, 2011
TLT: 3.40% as of Sep 30, 2011

Weighted Average Maturity from iShares:
SHY: 1.85Y
TLT: 28.05Y

Current treasuries yields for today from treasury.gov:
2Y: 0.23%
3Y: 0.37%
30Y: 3.05%

Treasury yields as of Sep 30, 2011 from treasury.gov:
2Y: 0.25%
3Y: 0.42%
30Y: 2.90%

How can TLT be yielding 3.40% when it's not even a 30Y bond (it's 28Y) and the 30Y bond has been yielding ~3% for months?

How can SHY be yielding 0.91% when it's not even a 2Y bond (it's 1.85Y) and the 2Y bond has been yielding ~0.25% for months?

Is that just because the dividend yields look backwards at past dividends, which were better than today's dividends would be?  I see how that can explain some of this, but not all of it, in particular not the 0.91% yield on SHY.

The last time even the 3Y yielded 0.91% was 05/24/2011!

[stone]

akratic, is it something to do with how the bonds mature? Imagine you have some of the 1year bonds actually being 29 year old what were once 30 year bonds that have a coupon of 7% or something. They will pay out a big coupon and then mature with a "capital" loss. I guess the way the interest is paid out by the etfs gets skewed by that.

[dualstow]

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.

I use the same apmex link for my google sheet. Probably learned about it at this forum.
You can get data on specific 30-YR's from the wall street journal, but I don't know if it's importable.

[escafandro]

I use the MT suggestion in Smartmoney, whit 5 individual 30yrs bonds that I have the result of TLT is almost always accurate.

[melveyr]

Wow. Thanks for that gold formula! I really appreciate it!

[Tortoise]

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.

[akratic]

Thanks Tortoise, that's exactly what I was looking for!

[Khisanth]

Just recently liquidated TLT and bought actual honest to god United States Treasuries. Here's how I have google docs set up:

The possible market price of the bond:

Code: Select all

=PRICE(DATE(2013,05,15),DATE(2043,05,15),2.875%,H5,100,2,0)

The current yield in % as my H5 cell (included in the =PRICE above)

Code: Select all

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

Did I do that right?

[Kriegsspiel]

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.

[dualstow]

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.

Mine is almost the same. I think I learned it from Melvey.
=index(ImportHtml("http://fixedincome.fidelity.com/fi/FIBo ... ics?cusip="&$A26,1,10),5,2)
(A26 is a cell with a cusip)

I'm trying to learn to just look at the monthly statements, though.

[jswinner]

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

[Ad Orientem]

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.

You just reminded me why I prefer ETFs.

[dualstow]

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

No, it's not. Definitely broken in google docs.  I've seen two or three threads asking about it, but so far no answers.
Melveyr used to fix it.

[Khisanth]

Once you figure out that =PRICE formula it's pretty easy to plug in the values you need:

Code: Select all

=PRICE(DATE(2013,05,15),DATE(2043,05,15),2.875%,Index(ImportHTML("http://money.cnn.com/data/bonds/","table",3),6,2),100,2,0)

Here's how I've set mine up. Basically, the only thing you need to adjust for your own situation is the 2013,05,15 portion, the 2043,05,15 portion, and the 2.875% portion.

=Index(ImportHTML("http://money.cnn.com/data/bonds/","table",3),6,2) will get the current 30-year interest rate.

Edit: corrected the code tags.

[dualstow]

I learned about the PRICE function here.

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.

I guess I just don't know where to look for a new source (from which to import data) when an old source fails, or why it fails.
For example, WildAboutHarry mentioned in another thread that the Fidelity data still worked for him, coding in Python. Does that mean that the PRICE function changed in Google Docs?

Is there someplace I can look at the cnn table? I don't see much at http://money.cnn.com/data/bonds/ so I'm definitely looking in the wrong place.

[dualstow]

@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),100,2,0)

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

[flyingpylon]

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.

The formula uses ImportHTML to get the third table in the page (as indicated by "table" and 3).  Then the Index function locates the value in the 6th row, 2nd column of that table.

The 100,2,0 values are part of the Price function.  PRICE(settlement, maturity, rate, yield, redemption, frequency, basis).  The Google Docs documentation says this for PRICE:

"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."

So in other words, I think it is calculating the value as opposed to looking it up somewhere.

[Khisanth]

@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?

The Index(ImportHTML) section is simply getting the current Interest rate for the 30-year bond, so basically that is plugging in 3.87% into the =PRICE field based on the interest rate today as of this post.

=PRICE then derives the bond price based on $100 original value, 2.875% Interest rate of the bonds I purchased, and 3.87% interest rate today and the DATE values that you plugged in.

[dualstow]

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.

Aha! That's what I was missing. { I thought that whatever PRICE was calculating, it still was drawing & using data via the Index part, and admittedly I have not looked up Index, but I see Khisanth's explanation above. }
Thank you both! Makes perfect sense.

[Kriegsspiel]

Nice, thanks. Where can you import from to get t-bills?

[flyingpylon]

Bloomberg seems to have a little more data available at http://www.bloomberg.com/markets/rates- ... -bonds/us/

The 30-year yield is in table 4, row 8, column 4:

=Index(ImportHTML("www.bloomberg.com/markets/rates-bonds/g ... ,"table",4),8,4)

You can adjust the row number as needed to get other yields.

For example, the 12-month yield is in row 4:

=Index(ImportHTML("www.bloomberg.com/markets/rates-bonds/g ... ,"table",4),4,4)

However, I have to say that I'm really not sure about the values I'm getting using the PRICE function.  They're pretty close to what Fidelity reports, but not exact.  Seems like the PRICE function is undervaluing things a tiny bit.  I'm a lot better at figuring out the mechanics of the spreadsheet formula than I am at understanding the bond market, so hopefully someone with more expertise can give us some guidance.  It's really too bad the old method of importing the numbers from Fidelity no longer works.

[dualstow]

I think I'm going to stop loading t-bill data since I never sell them anyway. I always wait & redeem at maturity.
Thanks for the bloomberg tip, pylon!