You will build a reusable sheet that prices a bond from its terms and shows how the price reacts to yield changes.
Every time Hui Min reads about a bond now, she wants to know what it would be worth if rates moved. Working it out with a calculator each time is slow. So she builds one sheet that does it for any bond she types in. It takes about 25 minutes, and you will use the same sheet again in modules 4, 7 and 8.
Open a blank spreadsheet in Excel or Google Sheets. The figures below are made up for practice.
Put labels in column A and values in column B, one per row:
B1, face value: 100 B2, coupon rate: 3% B3, payments per year: 1 B4, years to maturity: 5 B5, market yield: 4%
Keep every other formula pointing at these five cells. That way you can price a different bond by changing the inputs, without touching anything else.
Using 100 as face value keeps the answer in the form bonds are quoted in, per 100. If you want a dollar figure for your own holding, multiply at the end.
In B7, type:
=PV(B5/B3, B4*B3, -B1*B2/B3, -B1)
Read it from left to right. The rate per period is the yearly yield divided by the number of payments a year. The number of periods is years times payments a year. The payment is the coupon in money per period, entered as negative so the answer comes out positive. The last argument is face value, also negative, for the same reason. This is the PV function you used in How money works, lesson 6.3, Present value: what a future promise is worth today, with a stream of coupons and a lump sum together.
With the inputs above, B7 should show 95.55. That matches lesson 2.1, Price and yield move in opposite directions.
A formula you have not checked is a formula you are trusting blindly. Build the same price one payment at a time.
In D1 to G1 put four headings: period, cash flow, discount factor and present value. In D2 to D6 put the periods 1 to 5. In E2 to E5 put the coupon, 3. In E6 put 103, the last coupon plus face value. In F2 type =1/(1+$B$5)^D2 and fill it down to F6. In G2 type =E2*F2 and fill down. Sum G2 to G6.
You should see roughly 2.885, 2.774, 2.667, 2.564 and 84.658 in the present value column, and a total of 95.55. If the hand total and B7 disagree, the usual culprits are a missing minus sign in PV, or a discount factor that uses the coupon rate instead of the market yield.
The hand version is written for yearly coupons. For a half-yearly bond, set B3 to 2. The PV formula adjusts on its own. As a check, the same five-year, 3% bond paying half-yearly comes out at about 95.51 at a 4% yield, a touch lower than the yearly version because the yield also compounds half-yearly.
Now make the sheet show the price at several yields at once. In A10 to A14, type five yields: two points below today, one below, today, one above and two above. With today at 3%, that is 1%, 2%, 3%, 4% and 5%.
In B10, copy the PV formula but point the rate at A10 instead of B5:
=PV(A10/$B$3, $B$4*$B$3, -$B$1*$B$2/$B$3, -$B$1)
Fill it down to B14. In C10 add the percentage change from today's price, =B10/$B$12-1, and fill down. You now have the price and the gain or loss at each yield.
Duration from lesson 2.3, Duration: how far a price moves when rates move, is easiest to believe when you see it. Set up a second copy of the inputs and the yield table a few columns to the right, and point its formulas at its own input cells, because the dollar signs in the first table will keep pointing at column B. Give both bonds a 3% coupon and take today's yield as 3%. Set one to 2 years and the other to 10.
At today's yield both are priced at exactly 100, because the coupon equals the yield. Look at the row for one point higher. The two-year bond drops to about 98.11, a fall of 1.89%. The ten-year bond drops to about 91.89, a fall of 8.11%. Same coupon, same issuer quality, same rate move, and the long bond loses more than four times as much.
That gap is duration showing up in your own numbers. If you plot price against yield for both bonds, the ten-year line is much steeper, and both lines curve slightly, which is why the duration estimate in lesson 2.3 was a little too gloomy for large moves.
A finished sheet has five input cells, a PV price that matches the hand calculation to two decimal places, a five-row yield table with percentage changes, and two bonds side by side. Change any input and every number updates. Run it on the 2.1 example to confirm it gives 95.55, then read off what happens to the short and the long bond when yields go up by two points.
Build the bond pricing sheet, test it on the 5-year, 3% example from lesson 2.1, and note the price change for both bonds at plus two points.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).