*Relevance alert: the following post will be of only academic interest.*
dbr wrote: ↑Sun Sep 01, 2024 3:57 pmThe formula is really the formula for bond price from which you solve for YTM by some method of successive approximations as YTM can't be extracted algebraically as a solution.

Correct, we can't solve directly for yield-to-maturity (YTM); only by successive approximations. In case anyone is interested, here is one way to do it using the

Newton–Raphson method.

- Pick a small initial value for YTM, say 1%.
- Calculate the value of the function f(YTM) - P where P is the price. We're looking for the YTM where the result would be zero; i.e., where f(YTM) equals the price.
- Calculate the value of the derivative function, f'(YTM). The calculus to do this exactly is beyond me, so I approximate it by recalculating with a small delta or dx, f(YTM+dx).
- Determine a closer value of YTM where YTM[n+1] = YTM[n] - f(YTM[n]) / f'(YTM[n])
- Repeat until result converges. Often takes only two repeats.

Here's an example using column B of the table in

my previous post of a 5-year bond with a 4% coupon, a 3% YTM, and a price of 104.580. We calculate the price on rows 8 and 9 with the

PV function. It takes only two iterations to arrive at the 3% result in cell C11.

Code: Select all

```
Row Col A Col B Col C Col D Formula in Column B Copied Right
2 Periods 5
3 Coupon 4.000%
4 Price 104.580
5 YTM dx 0.001%
6 Iteration 1 2 3
7 YTM 1.000% 2.889% 3.000%
8 f(YTM) 9.980 0.523 0.002 =-100*PV(B7,$B2,$B3,1,0)-$B4
9 f(YTM+dx) 9.975 0.519 -0.003 =-100*PV(B7+$B5,$B2,$B3,1,0)-$B4
10 f'(YTM) -528.289 -474.008 -471.044 =(B9-B8)/$B5
11 YTM n+1 2.889% 3.000% 3.000% =B7-B8/B10
```

The above example is of a bond with annual coupon payments and only whole interest periods. Following is an example of the 9-year 10-month 4.375% Treasury note

auctioned 7/10/2024 with a YTM of 4.276% and a price of 100.777903. The formula for f(YTM) is more complicated as it takes into account semi-annual compounding and a fractional initial period with accrued interest.

Code: Select all

```
Row Col A Col B Col C Col D Formula in Column B Copied Right
2 Settlement 7/15/2024
3 Matures 5/15/2034
4 Coupon 4.375%
5 Price 100.777903
6 Periods / year 2
7 First period begin 5/15/2024 =COUPPCD(B2,B3,B6,1)
8 First period end 11/15/2024 =COUPNCD(B2,B3,B6,1)
9 Days in period 184 =B8-B7
10 Days before settle 61 =B2-B7
11 Days after settle 123 =B8-B2
12 Nbr full periods 19 =COUPNUM(B2,B3,B6,1)-1
13 Accrued interest 0.7252 =100*(B4/B6)*(B10/B9)
```

Code: Select all

```
14 YTM dx 0.001%
15 Iteration 1 2 3
16 YTM n 1.00000% 3.81097% 4.26594%
17 f(YTM) 30.7563 3.8034 0.0805 =100*(-PV(B16/$B6,$B12,$B4/$B6,1,0)+$B4/$B6)/(1+(B16/$B6)*($B11/$B9))-$B13-$B5
18 f(YTM+dx) 30.7454 3.7950 0.0725 =100*(-PV((B16+$B14)/$B6,$B12,$B4/$B6,1,0)+$B4/$B6)/(1+((B16+$B14)/$B6)*($B11/$B9))-$B13-$B5
19 f'(YTM) -1094.1535 -835.9573 -800.7644 =(B18-B17)/$B14
20 YTM n+1 3.81097% 4.26594% 4.27600% =B16-B17/B19
```

We get the 4.276% YTM after just three iterations in cell D20. Note that 4.276% is the

*exact* value since YTM is what is bid at a treasury note auction (in 0.001% increments). 4.276% was the accepted bid and the 100.777903 price buyers paid was calculated based on that.

Edited 9/8/2024 9:00 AM to add the following:
We can use the same approximation method with the

PRICE function as shown below. It takes four iterations to get the YTM to five percentage places, 4.27663%. Astute readers will notice this differs from the 4.27600% calculated above. This is because Excel handles the initial fractional period, 123/184, differently than does the Treasury formula. This is confirmed by PRICE's complementary function,

YIELD:

**4.27663% = YIELD(B2, B3, B4, B5, 100, B6, 1)**
Code: Select all

```
Row Col A Col B Col C Col D Col E Formula in Column B Copied Right
2 Settlement 7/15/2024
3 Matures 5/15/2034
4 Coupon 4.375%
5 Price 100.777903
6 Periods / year 2
14 YTM dx 0.001%
15 Iteration 1 2 3 4
22 YTM n 1.00000% 3.81118% 4.26655% 4.27662%
23 f(YTM) 30.7567 3.8058 0.0807 0.0000 =PRICE($B2,$B3,$B4,B22,100,$B6,1)-$B5
24 f(YTM+dx) 30.7457 3.7974 0.0727 -0.0080 =PRICE($B2,$B3,$B4,B22+$B14,100,$B6,1)-$B5
25 f'(YTM) -1094.0839 -835.7579 -800.5263 -799.7651 =(B24-B23)/$B14
26 YTM n+1 3.81118% 4.26655% 4.27662% 4.27663% =B22-B23/B25
```