Three modules of this course have leaned on claims you took on trust: prices fall when rise, measures how hard, the long end is the dangerous end. This module does the arithmetic behind those claims, and the arithmetic is divide, multiply, add. By the end of these three lessons you can price a by hand and in Python.
A bond is a list of dated cashflows
Here is the this whole module is built on. It runs for three years, pays a 4% annual on £100 nominal, and the market on like it is 5%.
The is the face amount repaid when the matures. The is the interest paid on that face amount, once a year on this : 4% of £100 is £4. So owning this means owning exactly three future cashflows:
- £4 in one year
- £4 in two years
- £104 in three years: the final coupon plus the £100 back
That is the entire instrument. Every conventional is a list like this one, usually longer.
Discounting is the only move
What is the list worth today? Adding the cashflows gives £112, but that ignores the waiting. A pound due later is worth less than a pound in hand, because a pound in hand could be out earning the market . At 5%, £1 due in a year is worth 1 ÷ 1.05 = 95.2p today; £1 due in two years gets divided by 1.05 twice; three years, three times. That single move, dividing by 1.05 once per year of waiting, is the whole of pricing.
Apply it to each cashflow in turn:
| Year | Cashflow | Discounting at 5% | Worth today |
|---|---|---|---|
| 1 | £4 | 4 ÷ 1.05 | £3.81 |
| 2 | £4 | 4 ÷ 1.05² | £3.63 |
| 3 | £104 | 104 ÷ 1.05³ | £89.84 |
| Price | £97.28 |
Add the right-hand column: 3.81 + 3.63 + 89.84 = £97.28. That is the price: the of the list. The turns out to be the connecting the cashflows to the price. Quote either one and you can work out the other, which is why Module 2 treated price and as two readings of the same object.
Why it sits below £100
The pays 4% interest in a market offering 5%. Nobody hands over face value for below-market interest, so the price settles below par (the £100 face value), and the £2.72 discount is precisely what tops the buyer's return up to 5%. Pay £97.28 for the £4, £4, £104 list and you earn the market after all; the cheap price is the compensation.
Run the seesaw from there. If the market rises, every division in the table gets heavier and the price falls further below par. If the falls to 4%, equal to the , the discount disappears entirely and the prices at exactly £100. If the market falls to 4%, equal to the , the is worth exactly its £100 face value. This is called trading at par.
Run it, then change the inputs
The table is a loop. Here it is as one: three working lines and some printing.
Now change the inputs:
- Set y to 0.06. The price drops to £94.65: up, price down.
- Set y to 0.04. The price lands on £100.00 exactly. equals , at par.
- Set the to 6.0 with y back at 0.05. The price rises to £102.72, above par, because now the pays above-market interest and buyers bid the premium away.
The direction is the part to hold on to: prices fall when rise, and everything else you say about stands on it. You have now watched the arithmetic produce the right answer three times.
Now build it in the grid
The loop hides the workings inside itself, which is fine for a demonstration and wrong for a model. On a desk this is built as a sheet: one row per dated cashflow, the visible in its own column, every number traceable to a parameter above it.
Spreadsheet: build it, don’t type it
Price the bond, one row per cashflow
Three blocks: the bond's parameters at the top, one row per dated cashflow in the middle, the price at the bottom. No cell below row 9 may hard-code a bond parameter, because every cell reads the inputs above it, so changing the yield in B5 reprices every cell that reads it. WORKINGS. B10 to B12: each year's cashflow, built from the coupon rate in B2 and the nominal in B3. Only the last one gets the nominal back. C10 to C12: the discount factor at the market yield, which is 1 divided by (1 plus the yield) raised to the power of the year in column A. Build the factor explicitly rather than reaching for a function, so you can watch it shrink. D10 to D12: cashflow times discount factor, which is the worth-today column from the table above. E10 to E12: the same three cashflows, discounted instead at the stressed yield in B6. RESULTS. B15: the price at 5%, the total of column D. B16: the price at 6%. B17: the fall from one to the other, as a percentage of B15. CHECKS. E15 and E16, each returning OK or CHECK: E15: your price agrees with the NPV function run over the cashflows at the same rate. NPV discounts its first cashflow by one year, which is exactly what row 10 does, so the two routes have to land on the same number. E16: the discount factors are below 1, falling, and still above zero. Marked cells: B12, C10, C12, B15, B16, B17, E15, E16. B10, B11, C11 and the D and E workings rows are not marked, and the results are wrong without them.
Cells to fill: C10, B12, C12, B15, E15, B16, E16, B17
- Given data, locked
- Yours to fill
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | INPUTS: the bond and the market | ||||||
| 2 | Coupon rate | 0.04 | |||||
| 3 | Nominal (£) | 100 | |||||
| 4 | Term (years) | 3 | |||||
| 5 | Market yield | 0.05 | |||||
| 6 | Stressed yield | 0.06 | |||||
| 7 | |||||||
| 8 | WORKINGS: one row per dated cashflow | ||||||
| 9 | Year | Cashflow (£) | Discount factor at 5% | Value today at 5% (£) | Value today at 6% (£) | ||
| 10 | 1 | ||||||
| 11 | 2 | ||||||
| 12 | 3 | ||||||
| 13 | |||||||
| 14 | RESULTS | CHECKS | Status | ||||
| 15 | Price at 5% (£) | Price agrees with NPV | |||||
| 16 | Price at 6% (£) | Discount factors below 1 and falling | |||||
| 17 | Price fall (%) | ||||||
| 18 | |||||||
| 19 | |||||||
| 20 |
How this grid works: typing, filling, references, layout
Moving and selecting
- Move a cell at a time
- : arrow keys
- Run to the end of a block
- : Ctrl+arrow
- Back to A1, or out to the last cell used
- : Ctrl+Home / Ctrl+End
- Select a range
- : Shift+arrow, or shift-click the far corner
- Select to the end of a block
- : Ctrl+Shift+arrow
- Take a whole row, or a whole column
- : Shift+Space / Ctrl+Space, or click its header
- Take several rows or columns
- : drag along the headers, or shift-click
- Take the lot
- : Ctrl+A
Entering and editing
- Start an entry
- : just type, or use the formula bar
- Commit it and move down, or up
- : Enter / Shift+Enter
- Commit it and move right
- : Tab
- Change your mind mid-entry
- : Escape
- Open what is already in the cell
- : F2, or double-click it
- Empty the selected cells
- : Delete
- Find a function, then its arguments
- : start typing the name; the open bracket lists the arguments in order, with the one you are writing picked out
Filling and copying
- Fill a formula down the column
- : Ctrl+D, or drag the small square at the corner of the selection
- Fill it right along the row
- : Ctrl+R
- Copy, or cut
- : Ctrl+C / Ctrl+X
- Paste it, references moving as they go
- : Ctrl+V
- Paste the numbers instead of the formulas
- : Ctrl+Shift+V
- Fill a whole block from one cell
- : copy it, select the block, paste
- The same four with a mouse
- : right-click a cell: the shortcuts are printed beside them
References
- Put a cell into a formula without typing its address
- : click it, or press an arrow after =, a bracket or an operator
- Grow that reference into a range
- : Shift+arrow, or drag across the cells
- Stop a reference shifting when the formula copies
- : F4, which adds the dollar signs
- Give the arrow keys back to the text
- : F2 swaps them between picking cells and moving the cursor
- See the range you picked before you commit it
- : each reference takes a colour and outlines the cells it points at; the same one twice keeps its colour
- See what a finished formula reads
- : select its cell: the cells it reads are outlined
Rows and columns
- Make a column wider, or a row taller
- : drag the line between two headers, or Alt+Shift+arrow
- Fit it back to what is in it
- : double-click that line, or Alt+Shift+0
- More room to work in
- : the blank rows and columns past the data, and the Add rows and Add columns buttons
Columns start as wide as what is in them, and nothing you do out in the blank space is marked.
The view
- Zoom in or out
- : Ctrl++ / Ctrl+-, the buttons above the grid, or Ctrl with the wheel
- Back to how it was drawn
- : Ctrl+0, or Reset view
- Find out what a shade means
- : the key above the grid, which lists only the shades this exercise uses
Zoom, widths and heights are how you are looking at the grid, not what is in it. None of it is marked.
Have a go and press Check. The worked solution opens up after your first real attempt.
The next lesson takes the same and asks how hard the price moves when the moves. First, prove the price.
Work it out
A 2-year bond pays a 3% annual coupon on £100 nominal, and the market yield is 5%. Price it, in pounds, to two decimal places, by hand with the table's method or by editing the loop above.