How to value direct bond holdings on Google Docs?

[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!

[fnord123]

I find the PRICE() function often doesn't match the actual price of the bonds as reported by Fidelity or the WSJ, so I do the following:

1. Import the table of prices from the WSJ:  =importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
2. Use the VLOOKUP funciton to get the exact current price of the bond by maturity date, e.g.: =vlookup(date(2042,11,15),A165:C440,3,FALSE)

[dualstow]

Thank you, fnord! Good to finally see you around again.

[ZedThou]

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.

I had been pulling URLs like https://fixedincome.fidelity.com//ftgw/ ... =912810QT8 and using the 3rd party price, but as of yesterday it has been requiring a Fidelity login to display 3rd party. So from now on I'll just observe for a few days how the 3rd party prices compare to bid/ask and in the future use those relationships to get an approximate price.

[dualstow]

Zed, did you see fnord's idea in Reply#25? Works great.
For me, the result was within $2 of what Vanguard showed in Cost Basis.

[ZedThou]

I should also have mentioned that I'm not using a spreadsheet, but rather Perl, parsing the HTML. In principle I could grab the WSJ table and emulate the vlookup function, and I will do just that if Fidelity Fixed ever disappears completely. But for now, using what Fidelity will still give is easiest.

[dualstow]

Ah, I see.
I've got a Perl book, but I'm mainly using it to elevate my satellite radio antenna on the window sill.
If I had put in the time to learn the language, I would probably want to reap the benefits and do it your way!

[Pointedstick]

I should also have mentioned that I'm not using a spreadsheet, but rather Perl, parsing the HTML.

Were you a build engineer in a previous life?!

[ZedThou]

Wish I could say this was rocket surgery but it's not actually that complicated. Just a Finance::Quote submodule that scrapes a web page to get bond values by CUSIP. I do it this way because I use Gnucash to keep track of all financial transactions, and an eye on current allocations.

[Pointedstick]

Wish I could say this was rocket surgery but it's not actually that complicated. Just a Finance::Quote submodule that scrapes a web page to get bond values by CUSIP. I do it this way because I use Gnucash to keep track of all financial transactions, and an eye on current allocations.

Is this built into GNUcash, or your custom addition? If the latter, have you committed it to the project's source tree yet? Sounds useful.

[ZedThou]

Gnucash gets asset values from the Perl Finance::Quote module. Previously the module had no mechanism for retrieving bond quotes given CUSIP, so my modification is a Finance::Quote submodule which does this by scraping the Fidelity Fixed web page. I've submitted the code to the Finance::Quote developers for inclusion in their next release, but they haven't been communicative at all. It's a bit puzzling - perhaps not many users hold individual bonds.

Just as well since every few months Fidelity Fixed moves the goalposts and I have to make modifications to keep things working.

[Pointedstick]

Gnucash gets asset values from the Perl Finance::Quote module. Previously the module had no mechanism for retrieving bond quotes given CUSIP, so my modification is a Finance::Quote submodule which does this by scraping the Fidelity Fixed web page. I've submitted the code to the Finance::Quote developers for inclusion in their next release, but they haven't been communicative at all. It's a bit puzzling - perhaps not many users hold individual bonds.

Bah! Typical open-source project. 

[dualstow]

I find the PRICE() function often doesn't match the actual price of the bonds as reported by Fidelity or the WSJ, so I do the following:

1. Import the table of prices from the WSJ:  =importhtml("http://wsj.com/mdc/public/page/2_3020-treasury.html", "table",0)
2. Use the VLOOKUP funciton to get the exact current price of the bond by maturity date, e.g.: =vlookup(date(2042,11,15),A165:C440,3,FALSE)

I've been using the above, but there seems to be a discrepancy between the WSJ table and other sources.
For example, CUSIP 912828VQ0 with maturity date 7/31/18 (this is just a 5-year note in my vp) -->
Using the WSJ table, it's got a price of $104 and change.
But, according to both Vanguard and something I found online called treasurywiki.appspot.com, it's closer to $100 and change. Hmm.
http://treasurywiki.appspot.com/treasur ... n-xTZR_WKE