2

If you go to savingsbond.gov, choose the date 2023-07-17 and look at the CUSIP 91282CBM2, you'll see that it has a rate of 0.125%, a maturity of 2024-02-14, and a sell price of 97.03125. It's a 3 year note that pays a coupon semi-annually.

That price matches what I see in in my brokerage's interface and I'd like to compute (or estimate) that price myself in a Google sheet. I think I need to use some combination of the PRICE() and/or YIELD() functions and knowledge of the yield curve as of 2023-07-17, but I'm not quite sure how to do it.

To continue the example, I have:

  • Settlement: 2023-07-17
  • Maturity: 2024-02-14
  • Rate: 0.00125
  • Price: 97.03125
  • Redemption: 100
  • Frequency: 2 (semi-annual coupon)
  • Day count convention: 1 (Actual/Actual)

Plugging those values into the YIELD function produces 0.054. Taking those same values and the 0.054 yield just calculated and plugging them into PRICE() gets me back to 97.03125.

My question is: can I use knowledge of the current yield curve to estimate that 0.054 number? I imagine that bond traders have a way to calculate the fair price of a bond given the current interest rate environment.

Improve this question

2 Answers 2

Reset to default
5

The price is the present value of the coupon payments and principal payment at the end, so it depends on the discount rate(s) you choose.

In practice, the price is due to supply and demand, and current yields. In other words, the market sets the (in this case) 3Y yield and you can solve for the corresponding bond price.

Also, bonds are quoted in clean price, i.e. excluding accrued coupon payments.

For actual examples using Excel/Sheets formulas, Google is your friend.

Improve this answer
3
  • I added a more detailed example. One assumes that given knowledge of the current interest environment, a bond trader can estimate a reasonable price for a bond with a given coupon, maturity and settlement terms. What do they do?
    – John Rauser
    Jul 18 at 18:07
  • 1
    They do exactly what the answer says - they discount the future coupons and the final payoff based on the yield curve. They don't do it manually, though, they have software that knows the yield curve and does all of those calculations for them.
    – D Stanley
    Jul 20 at 13:28
  • @DStanley Ok, so my question then is how does that software work, and how close can I come to approximating it with publicly available information?
    – John Rauser
    Jul 21 at 16:43
0

Answering my own question: Notice that maturity is roughly 6 months away, and if I look up yield curve as of 2023-07-17, the 6 month yield is 5.5%, close to the 5.4% calculated with YIELD().

In code:

=price(date(2023,7,17), date(2024,2,14), 0.00125, 0.054, 100, 2, 1)

... which gives 97.04, which is the estimate I was looking for.

My original confusion stemmed from failing to divide the coupon percentage by 100 when calling PRICE(). Curing that, combined with the ideas in this link set me straight.

Improve this answer
6
  • 1
    How is this different from the already provided answer?
    – AKdemy
    Jul 18 at 22:12
  • @AKdemy I needed to know 1. What part of the yield curve to look at, and 2. the details of calling PRICE() to get a correct result. The other answer suggested I look at at the 3Y yield, which is incorrect for my case, and the link did not provide any useful information for my case.
    – John Rauser
    Jul 18 at 22:35
  • @JohnRauser apologies, you highlighted that it's a "3 year note" - I glossed over the details. In any case, being 10 bp off on a 6m maturity is not a small difference... Are you looking at mid or bid/offer prices?
    – 0xFEE1DEAD
    Jul 19 at 12:37
  • @0xFEE1DEAD No worries. Your answer wasn't wrong, but didn't give the details I needed to solve my problem. Re which price, I wanted to arrive at an approximation of the sell price of 97.03125, as quoted in the question. Agreed that 10bps is not a small error. I could improve by interpolating between the 6m and 1y points on the yield curve. I know that the only true price is the one set by the market, but I did say that what I wanted was an estimate.
    – John Rauser
    Jul 19 at 15:54
  • Part of the issue is that you're comparing an off-the-run note with ~7M to maturity to newly issued 6M bills, which are more liquid and trade with tighter bid-offer spreads. Further, they use different day-count conventions: notes use ACT/ACT and bills use ACT/360. Also, interpolating between the 6M and 1Y would be too crude to improve the results, considering the bills curve. In any case, I'm glad you found what you were looking for.
    – 0xFEE1DEAD
    Jul 19 at 17:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .