Reprice a bond in a spreadsheet

Build a reusable sheet that prices any bond from five inputs and shows its price at yields around today's.

[[chat Friend: Yields went from 3% to 5%. My 10-year 3% bond is down 15.44%. | You: My 2-year 3% bond only fell 3.72%. | Friend: Same coupon. Why so different?]]

A sheet with five inputs works that out for any bond.

---

[[guess A 5-year bond pays a 3% coupon once a year on S$100 of face value. Market yields are 4%. What's it worth today? | S$95.55 | Each payment is discounted at the 4% market yield, not the 3% coupon. A coupon below the yield means a price below 100. | S$100]]

---

Five inputs, one formula

| Cell | Input | Example | |---|---|---| | B1 | Face value | 100 | | B2 | Coupon rate | 3% | | B3 | Payments a year | 1 | | B4 | Years left | 5 | | B5 | Market yield | 4% |

``` =PV(B5/B3, B4*B3, -B1*B2/B3, -B1) ```

Put it in B7: 95.55 per 100 of face value. The minus signs keep it positive.

---

Check it **one payment at a time**

| Year | Cash flow | x 1/(1+B5)^year | Worth today | |---|---|---|---| | 1 | 3 | 0.9615 | 2.885 | | 2 | 3 | 0.9246 | 2.774 | | 3 | 3 | 0.8890 | 2.667 | | 4 | 3 | 0.8548 | 2.564 | | 5 | 103 | 0.8219 | 84.658 | | Total | | | 95.548 |

Year 5 adds the face value. The total should match B7.

---

?? Your hand total says 100.00. PV says 95.55. What went wrong? The PV formula lost one of its minus signs + The discount factors use the 3% coupon Payments a year is set to 2, not 1 => Discount at the coupon rate and every bond comes out at exactly 100. The factors take the market yield in B5.

---

?> How does your bond pay its coupon? Once a year => Leave B3 at 1. The PV price and the hand table should agree. Every six months, like SGS bonds => Set B3 to 2. PV halves the coupon and the yield and doubles the periods, so the example becomes 95.51. The hand table won't follow, so set B3 back to 1 before you build the yield table.

---

Price it at five yields

| Yield | Price | Change | |---|---|---| | 2% | 104.71 | +9.59% | | 3% | 100.00 | +4.66% | | 4% | 95.55 | 0.00% | | 5% | 91.34 | -4.40% | | 6% | 87.36 | -8.57% |

In A10 to A14 type =$B$5-2%, =$B$5-1%, =$B$5, =$B$5+1% and =$B$5+2%, so the yields follow B5. Fill this down from B10:

``` =PV(A10/$B$3, $B$4*$B$3, -$B$1*$B$2/$B$3, -$B$1) ```

C10 is =B10/$B$12-1.

---

Same coupon, **four times the fall**

[[grow A 3% bond's price | yield | 2-year: 3 S$100, 4 S$98.11, 5 S$96.28 | 10-year: 3 S$100, 4 S$91.89, 5 S$84.56]]

Copy the block and point it at its own inputs. One point above 3%, the 2-year falls 1.89%, the 10-year 8.11%.

---

[[sum The 10-year is now 91.89. You hold S$20,000 of face value. What's it worth? | S$18,378 | 19,622 => That's the 2-year's price. Use the 10-year's 91.89. | 20,000 => That's the face value, which you only get back if you hold to maturity. | => Prices are per 100 of face value: 91.89 / 100 x S$20,000 = S$18,378.]]

---

Keep the sheet. Change three cells.

When you read about a bond, type its coupon, years and yield into B2, B4 and B5. For the yield, start with MAS's benchmark SGS yield for the same term.

In Google Sheets or Excel, set up the five inputs and the PV line, then check that a 5-year 3% bond at a 4% yield prices at 95.55.

Calculators for this lesson

Course

Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).