About this lesson
Bond pricing in Google Sheets: semi-annual coupons by hand, with PV, and with PRICE and YIELD
In Google Sheets, a bond's price is the present value of its coupons and face value at the market yield. For half-yearly coupons, halve the rates and double the periods: =PV(B3/2,B4*2,-B1*B2/2,-B1). With dates, =PRICE(B6,B7,B2,B3,100,2) gives the price per $100 and =YIELD(B6,B7,B2,B8,100,2) the yield behind a quoted price.
Using Excel? Do this lesson in Excel.
=B1/(1+B3/B5)^(B4*B5)What you'll practise
Price a six-year corporate bond with half-yearly coupons: the face value and the coupons discounted by hand, the same price with PV, then PRICE on real dates, and the yield behind a broker's quote.
5 steps · 50 points · the cells outlined in gold below
- bond pricing
- PV
- PRICE
- YIELD
- semi-annual coupons
The data
| A | B | |
|---|---|---|
| 1 | Face value ($) | $100.00 |
| 2 | Coupon rate (a year) | 5.50% |
| 3 | Market yield (a year) | 6.20% |
| 4 | Years to maturity | 6 |
| 5 | Coupons a year | 2 |
| 6 | Settlement date | 16/11/2026 |
| 7 | Maturity date | 16/11/2032 |
| 8 | Broker's quote ($) | $97.25 |
| 9 | ||
| 10 | PV of face value | |
| 11 | PV of coupons | |
| 12 | Price with PV | |
| 13 | Price with PRICE | |
| 14 | Yield at the quote |