## Derivation of YTM formula

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Derivation of YTM formula

I am having a hard time understanding where the YTM formula comes from, see https://learn.saylor.org/mod/book/view. ... erid=37829 or the example in https://www.investopedia.com/terms/y/yi ... m-formulas

Legend
F = face value of the bond (principal)
I = bond annual interest
N = years needed to reach maturity
C = F*I

This is how I derive it.
The total value (V) generated by F over N years, if the interest is compounded, is
F*(1+I)^N = F + Sum[C*(1+I)^(k-1), k = 1:N] = V
(= F + Sum[C*(1+I)^(N-k), k = 1:N] = V, for later)

Then, let's say the FED increases interest rates and the bond price falls from F to P. Still P generates, at maturity, V and so one could measure the increase in yield of the discounted bond with the modified yield YTM such that

P*(1+YTM)^N = V (eq 1)

where "YTM is such that P generates the total value of the principal at maturity" (this is what I would expect YTM does)

Instead, everywhere online the formula to solve for YTM modifies also V, with YTM in place of I as

P*(1+YTM)^N = F + Sum[C*(1+YTM)^(k-1), k = 1:N] = F + Sum[C*(1+YTM)^(N-k), k = 1:N]

and thus

P = F/(1+YTM)^N + Sum[C*(1+YTM)^(-k), k = 1:N] (eq 2)

where the last equation is the explicitly given in the first link above.
Here is where I am confused. If the bond I buy at price P will still generate V why the received total value V with YTM?
I solved YTM numerically in two formulas (eq 1) and (eq 2), and for similar prices of P and F they are indeed close (of course exactly the same when P = F, with YTM = I), but when P is much different from F the difference in the YTMs increases.
Is it just a matter of the definition of YTM, or am I missing something?
dbr
Posts: 47336
Joined: Sun Mar 04, 2007 8:50 am

### Re: Derivation of YTM formula

So YTM is the yield that used as a discount rate in computing the net present value of the cash flows offered from today gives you the bond price today. While the cash flows from coupon interest are fixed as to dollar amount and date and the return of face value at maturity is fixed as to dollar amount and date, the price today and the date today change every day. The result is that YTM changes every day. The 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.

I can't quite follow the details of your comparisons, but the above definition should at least make clear what is being calculated.

One issue might be that bond interest is not compounded. The periodic coupon payment made is taken away and you do something with it, but what you do with it has no effect on the YTM math. You do not reinvest a bond coupon payment when talking about YTM. For this reason YTM is not the same as return. It would be the same as return if you had a zero coupon bond or if you somehow kept reinvesting in something at the same terms as the bond, and then reinvesting the interest on the reinvestments at the same term and so on. Put differently a bond making periodic payments does not have a return because the definition of return does not allow cash flows in and out. When there are cash flows you have to instead compute an internal rate of return, a formula that looks suspiciously like an NPV calculation.

Another way to think of this is that you can't sensibly calculate how much money you end up with from holding a bond because you get the money back at different times and there is no defined fate for what you do with that money.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

So YTM is the yield that used as a discount rate in computing the net present value of the cash flows offered from today gives you the bond price today.
By used as a discount rate, do you mean YTM is used in combination with discounted price P?
What do you mean by the net present value of the cash flows offered from today?
I find your words similar to those given in investopedia (https://www.investopedia.com/terms/d/duration.asp)
the current price of a bond is the present value of all its cash flows
Is it C/(1+YTM) + C/(1+YTM)^2 + ... + C/(1+YTM)^N?
I can't make sense of this!
Last edited by erma on Sun Sep 01, 2024 4:31 pm, edited 2 times in total.
dbr
Posts: 47336
Joined: Sun Mar 04, 2007 8:50 am

### Re: Derivation of YTM formula

erma wrote: Sun Sep 01, 2024 4:10 pm
So YTM is the yield that used as a discount rate in computing the net present value of the cash flows offered from today gives you the bond price today.
By used as a discount rate, do you mean YTM is used in combination with discounted price P?
What do you mean by the net present value of the cash flows offered from today?
I find your words similar to those given in investopedia (https://www.investopedia.com/terms/d/duration.asp)
the current price of a bond is the present value of all its cash flows
Is it C/(1+YTM) + C/(1+YTM)^2 + ... + C/(1+YTM)^N?
I can't make sense of this!
Yes, I am referring exactly to that formula for bond price. The term "discount" means the interest rate to calculate the net present value of a future cash payment. Discounted (or premium) price is a different thing. In the second use discounted means the price to buy the bond is less than the face value that will be returned at redemption. The whole conversation hinges on the investment concept of time value of money, meaning when you get the money matters and money gotten sooner is more valuable than money gotten later.

NPV: https://en.wikipedia.org/wiki/Net_present_value

I think this gets confusing in many investment conversations because yield on bonds (YTM version) is not like an interest payment on a savings account. There are other yields you can talk about. The coupon yield is the interest rate that applied to the face value gives you the periodic coupon payment (usually in two halves each year). You can also take that coupon payment in dollars and divide it by the current price to get a current coupon interest rate on price. The final way of making (losing) money on a bond is getting back at maturity more (less) than you paid. Some bonds have no coupon payments and everything comes from the price being less than the eventual face value, such as a T bill. The price can also be more than the eventual face value and result in a negative yield to maturity, depending on how much that loss exceeds the value of the coupon payments.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

Yes, I am referring exactly to that formula for bond price. The term "discount" means the interest rate to calculate the net present value of a future cash payment. Discounted (or premium) price is a different thing. In the second use discounted means the price to buy the bond is less than the face value that will be returned at redemption. The whole conversation hinges on the investment concept of time value of money, meaning when you get the money matters and money gotten sooner is more valuable than money gotten later.
I see the confusion about discount and discounted! This time-value-of-money concept looks very interesting, I will dig more soon, thank you sir (for now )!!
#Cruncher
Posts: 4067
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

### Re: Derivation of YTM formula

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.
erma wrote: Sun Sep 01, 2024 4:10 pm
the current price of a bond is the present value of all its cash flows
Is it C/(1+YTM) + C/(1+YTM)^2 + ... + C/(1+YTM)^N?
Yes, except the final cash flow needs to include the face value as well as the last coupon payment. This is shown on row 12 of the table below with the discounted cash flows when a bond is priced above (column B), at (column C), or below (column D) par. The sum of the discounted cash flows on row 13 agrees with the present value on row 6 as calculated with the PV spreadsheet function.

Code: Select all

``````Row                     Col A     Col B    Col C   Col D   Formula in Column B Copied Right
2                Face value       100
3             Term in years         5
4      Coupon paid annually      4.0%
5   Yield to maturity (YTM)      3.0%     4.0%    5.0%
6             Present value   104.580  100.000  95.671  =-\$B2*PV(B5,\$B3,\$B4,1,0)
7                      Year   - CF Discounted by YTM -
8                         1     3.883    3.846   3.810  =\$B\$2*\$B\$4  /(1+B\$5)^\$A8
9                         2     3.770    3.698   3.628   | | |
10                         3     3.661    3.556   3.455   v v v
11                         4     3.554    3.419   3.291  =\$B\$2*\$B\$4  /(1+B\$5)^\$A11
12                         5    89.711   85.480  81.487  =\$B2*(\$B4+1)/(1+B\$5)^\$A12
13  Sum discounted cash flow   104.580  100.000  95.671  =SUM(B8:B12)``````
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

#Cruncher wrote: Sun Sep 01, 2024 9:59 pm Yes, except the final cash flow needs to include the face value as well as the last coupon payment.
Perfectly clear, thanks.
dbr wrote: Sun Sep 01, 2024 3:57 pm Another way to think of this is that you can't sensibly calculate how much money you end up with from holding a bond because you get the money back at different times and there is no defined fate for what you do with that money.
Are you speaking about the possibility of the coupon being not enough to be reinvested immediately?
So that the compounded formula F(1+I)^M is an overestimate?

F = face value
I = coupon interest rate
M = maturity years
dbr
Posts: 47336
Joined: Sun Mar 04, 2007 8:50 am

### Re: Derivation of YTM formula

erma wrote: Wed Sep 04, 2024 10:28 am
#Cruncher wrote: Sun Sep 01, 2024 9:59 pm Yes, except the final cash flow needs to include the face value as well as the last coupon payment.
Perfectly clear, thanks.
dbr wrote: Sun Sep 01, 2024 3:57 pm Another way to think of this is that you can't sensibly calculate how much money you end up with from holding a bond because you get the money back at different times and there is no defined fate for what you do with that money.
Are you speaking about the possibility of the coupon being not enough to be reinvested immediately?
So that the compounded formula F(1+I)^M is an overestimate?

F = face value
I = coupon interest rate
M = maturity years
You can't reinvest in a bond at the same terms as the original investment. The face value remains the same and the coupon interest rate on face value remains the same, but the price to buy a bond of that same issue will be different in general. You would have to go on the bond market and see if someone will even sell you that same issue at any price, less spreads, etc. Your compounding formula does not apply.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

dbr wrote: Wed Sep 04, 2024 10:57 am
erma wrote: Wed Sep 04, 2024 10:28 am
#Cruncher wrote: Sun Sep 01, 2024 9:59 pm Yes, except the final cash flow needs to include the face value as well as the last coupon payment.
Perfectly clear, thanks.
dbr wrote: Sun Sep 01, 2024 3:57 pm Another way to think of this is that you can't sensibly calculate how much money you end up with from holding a bond because you get the money back at different times and there is no defined fate for what you do with that money.
Are you speaking about the possibility of the coupon being not enough to be reinvested immediately?
So that the compounded formula F(1+I)^M is an overestimate?

F = face value
I = coupon interest rate
M = maturity years
You can't reinvest in a bond at the same terms as the original investment. The face value remains the same and the coupon interest rate on face value remains the same, but the price to buy a bond of that same issue will be different in general. You would have to go on the bond market and see if someone will even sell you that same issue at any price, less spreads, etc. Your compounding formula does not apply.
Your point is that if I get a coupon C, it is highly unlikely that I can reinvest it ALL or AT-ALL immediately in the same bond, correct?
dbr
Posts: 47336
Joined: Sun Mar 04, 2007 8:50 am

### Re: Derivation of YTM formula

erma wrote: Wed Sep 04, 2024 11:18 am
dbr wrote: Wed Sep 04, 2024 10:57 am
erma wrote: Wed Sep 04, 2024 10:28 am
#Cruncher wrote: Sun Sep 01, 2024 9:59 pm Yes, except the final cash flow needs to include the face value as well as the last coupon payment.
Perfectly clear, thanks.
dbr wrote: Sun Sep 01, 2024 3:57 pm Another way to think of this is that you can't sensibly calculate how much money you end up with from holding a bond because you get the money back at different times and there is no defined fate for what you do with that money.
Are you speaking about the possibility of the coupon being not enough to be reinvested immediately?
So that the compounded formula F(1+I)^M is an overestimate?

F = face value
I = coupon interest rate
M = maturity years
You can't reinvest in a bond at the same terms as the original investment. The face value remains the same and the coupon interest rate on face value remains the same, but the price to buy a bond of that same issue will be different in general. You would have to go on the bond market and see if someone will even sell you that same issue at any price, less spreads, etc. Your compounding formula does not apply.
Your point is that if I get a coupon C, it is highly unlikely that I can reinvest it ALL or AT-ALL immediately in the same bond, correct?
It really doesn't make sense to talk about compounding the interest on a bond in the same sense that you compound the interest on a CD.

You might find a bond of the same CUSIP but even if you do you may not be investing at the same price as the price you paid when you bought originally, nor would you exactly reinvest the coupon payment, if you can even invest it at all. As a practical matter it doesn't happen. Remember what you pay for a bond is in general different from the face value and different in value every day. Even if you bought the bond at par, meaning paying the face value for the bond, the reinvestment per dollar of face value will not be at par in general. You won't know what the reinvestment price is going to be to calculate a future value of the compounded coupon payments.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

dbr wrote: Wed Sep 04, 2024 11:40 am It really doesn't make sense to talk about compounding the interest on a bond in the same sense that you compound the interest on a CD.

You might find a bond of the same CUSIP but even if you do you may not be investing at the same price as the price you paid when you bought originally, nor would you exactly reinvest the coupon payment, if you can even invest it at all. As a practical matter it doesn't happen. Remember what you pay for a bond is in general different from the face value and different in value every day. Even if you bought the bond at par, meaning paying the face value for the bond, the reinvestment per dollar of face value will not be at par in general. You won't know what the reinvestment price is going to be to calculate a future value of the compounded coupon payments.
I see, thanks
dbr
Posts: 47336
Joined: Sun Mar 04, 2007 8:50 am

### Re: Derivation of YTM formula

erma wrote: Wed Sep 04, 2024 11:47 am
dbr wrote: Wed Sep 04, 2024 11:40 am It really doesn't make sense to talk about compounding the interest on a bond in the same sense that you compound the interest on a CD.

You might find a bond of the same CUSIP but even if you do you may not be investing at the same price as the price you paid when you bought originally, nor would you exactly reinvest the coupon payment, if you can even invest it at all. As a practical matter it doesn't happen. Remember what you pay for a bond is in general different from the face value and different in value every day. Even if you bought the bond at par, meaning paying the face value for the bond, the reinvestment per dollar of face value will not be at par in general. You won't know what the reinvestment price is going to be to calculate a future value of the compounded coupon payments.
I see, thanks
I should add that if you do want compounded return from a bond holding you do get that from a bond fund, automatically neat and clean. But the fixed rate of return compound interest formula will not apply because the return is constantly changing and is not a fixed interest rate.
alex_686
Posts: 13842
Joined: Mon Feb 09, 2015 1:39 pm

### Re: Derivation of YTM formula

erma wrote: Wed Sep 04, 2024 11:18 am
dbr wrote: Wed Sep 04, 2024 10:57 am
erma wrote: Wed Sep 04, 2024 10:28 am
#Cruncher wrote: Sun Sep 01, 2024 9:59 pm Yes, except the final cash flow needs to include the face value as well as the last coupon payment.
Perfectly clear, thanks.
dbr wrote: Sun Sep 01, 2024 3:57 pm Another way to think of this is that you can't sensibly calculate how much money you end up with from holding a bond because you get the money back at different times and there is no defined fate for what you do with that money.
Are you speaking about the possibility of the coupon being not enough to be reinvested immediately?
So that the compounded formula F(1+I)^M is an overestimate?

F = face value
I = coupon interest rate
M = maturity years
You can't reinvest in a bond at the same terms as the original investment. The face value remains the same and the coupon interest rate on face value remains the same, but the price to buy a bond of that same issue will be different in general. You would have to go on the bond market and see if someone will even sell you that same issue at any price, less spreads, etc. Your compounding formula does not apply.
Your point is that if I get a coupon C, it is highly unlikely that I can reinvest it ALL or AT-ALL immediately in the same bond, correct?
To shift this conversation a bit, you may not like the assumption but it is a necessary assumption. With this assumption you can solve the problem with a financial pocket calculator.

One could deconstruct a 10 year bond that paid bi-annual coupons into 20 separate zero coupon bonds, each coupon having its own interest rate. As such you kind of sidestep the issue of reinvestment. However it is a hairy exercise involving construction a Treasure Yield Curve which involves a host of technical and practical issues.
Former brokerage operations & mutual fund accountant. I hate risk, which is why I study and embrace it.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

alex_686 wrote: Wed Sep 04, 2024 12:17 pm One could deconstruct a 10 year bond that paid bi-annual coupons into 20 separate zero coupon bonds, each coupon having its own interest rate. As such you kind of sidestep the issue of reinvestment.
Why would you do this decomposition?
alex_686
Posts: 13842
Joined: Mon Feb 09, 2015 1:39 pm

### Re: Derivation of YTM formula

erma wrote: Wed Sep 04, 2024 12:36 pm
alex_686 wrote: Wed Sep 04, 2024 12:17 pm One could deconstruct a 10 year bond that paid bi-annual coupons into 20 separate zero coupon bonds, each coupon having its own interest rate. As such you kind of sidestep the issue of reinvestment.
Why would you do this decomposition?
Because it is super fun!

Also because I work in finance. As such I need to price bonds. The formula you use above is trivial. If you know the price you know the yield, if you know the yield you know the price.

I often have to price things. i.e., I am not given either the price or the yield. Cash flows may be unequal or may not even be known with certainty.
Former brokerage operations & mutual fund accountant. I hate risk, which is why I study and embrace it.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

alex_686 wrote: Wed Sep 04, 2024 1:21 pm
erma wrote: Wed Sep 04, 2024 12:36 pm
alex_686 wrote: Wed Sep 04, 2024 12:17 pm One could deconstruct a 10 year bond that paid bi-annual coupons into 20 separate zero coupon bonds, each coupon having its own interest rate. As such you kind of sidestep the issue of reinvestment.
Why would you do this decomposition?
Because it is super fun!

Also because I work in finance. As such I need to price bonds. The formula you use above is trivial. If you know the price you know the yield, if you know the yield you know the price.

I often have to price things. i.e., I am not given either the price or the yield. Cash flows may be unequal or may not even be known with certainty.
Nice to meet someone which actually price things!! I always wondered how to price bonds when interest rates change. I have my own interpretation.

A new bond with annual interest rate I (bought at par) gives a non-compounded return at maturity (in M years) as M*I (coupon not reinvested).
Then interest rates rise 1% and the bond price falls, but of how much?
If I was to price it, I would reduce it such that the non compounded return at maturity would be now M*(I+1%), to make it competitive with the new bonds. How far am I from reality?
dbr
Posts: 47336
Joined: Sun Mar 04, 2007 8:50 am

### Re: Derivation of YTM formula

erma wrote: Wed Sep 04, 2024 1:46 pm
alex_686 wrote: Wed Sep 04, 2024 1:21 pm
erma wrote: Wed Sep 04, 2024 12:36 pm
alex_686 wrote: Wed Sep 04, 2024 12:17 pm One could deconstruct a 10 year bond that paid bi-annual coupons into 20 separate zero coupon bonds, each coupon having its own interest rate. As such you kind of sidestep the issue of reinvestment.
Why would you do this decomposition?
Because it is super fun!

Also because I work in finance. As such I need to price bonds. The formula you use above is trivial. If you know the price you know the yield, if you know the yield you know the price.

I often have to price things. i.e., I am not given either the price or the yield. Cash flows may be unequal or may not even be known with certainty.
Nice to meet someone which actually price things!! I always wondered how to price bonds when interest rates change. I have my own interpretation.

A new bond with annual interest rate I (bought at par) gives a non-compounded return at maturity (in M years) as M*I (coupon not reinvested).
Then interest rates rise 1% and the bond price falls, but of how much?
If I was to price it, I would reduce it such that the non compounded return at maturity would be now M*(I+1%), to make it competitive with the new bonds. How far am I from reality?
Your concept of return is too simple. There is no return in the sense you understand it above. When there are cash flows spread out over time. The workable mathematical calculation is the internal rate of return: https://en.wikipedia.org/wiki/Internal_rate_of_return You will notice that it is back to that problem of when you get the money and the net present value depending on when you get the money. M*I just says you get all the coupon payments at maturity, but you don't unless you have a zero coupon bond. This zero coupon thing is what Alex had to fall back on to reproduce this time sequence of investing.

The math that relates to how much bond price changes relative to a change in yield comes from the concept of Modified Duration. Read down in this article to that topic: https://en.wikipedia.org/wiki/Duration_(finance) That result essentially says that the change in price divided by the price (in %) will be approximately the modified duration times the change in yield (in %) Note the yield in that formulation is not "the" interest but rather the yield to maturity. In fact it is just the same old price formula and yield to maturity we have been talking about all along.

A problem is that a differential statement about price and yield at a point only approximates the results of a discrete change. As the yield change gets larger a first order approximation is a bit off and one wants second order results involving what they call the duration. The general mathematical idea is that of a Taylor Series approximation: https://en.wikipedia.org/wiki/Taylor_series
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

dbr wrote: Wed Sep 04, 2024 2:17 pm M*I just says you get all the coupon payments at maturity, but you don't unless you have a zero coupon bond.
Starting simple If I hold 100\$ of a 5% bond my return will be 5\$ every year, without doing anything else (reinvesting). At maturity I have in total M times this return. I do not get the utility of introducing zero coupons, YTM, and duration and modified duration at this point.
dbr wrote: Wed Sep 04, 2024 2:17 pm The math that relates to how much bond price changes relative to a change in yield comes from the concept of Modified Duration.
I am familiar with the equations, not so much yet with the concepts. But as far as I understood, the chain of events is
1) interest rates issued by FED/BCE will determine a change of price from face F to price P
2) the YTM is function of F, P, M and cash flows C (with C = F*I)
3) the (Macaulay) duration is function of the same variables above plus the coupon timings

Right now I am interested in 1) and not so much in how duration changes with YTM.
As a newcomer to the world of finance I see YTM just as a tool to get the duration and the duration as a tool to assess risk.
dbr
Posts: 47336
Joined: Sun Mar 04, 2007 8:50 am

### Re: Derivation of YTM formula

erma wrote: Wed Sep 04, 2024 2:57 pm
dbr wrote: Wed Sep 04, 2024 2:17 pm M*I just says you get all the coupon payments at maturity, but you don't unless you have a zero coupon bond.
Starting simple If I hold 100\$ of a 5% bond my return will be 5\$ every year, without doing anything else (reinvesting). At maturity I have in total M times this return. I do not get the utility of introducing zero coupons, YTM, and duration and modified duration at this point.

Since there are cash flows out of the investment return in a simple sense is not defined. You can calculate your internal rate of return from a formula such as XIRR in Excel. The inputs to XIRR are the dates and amounts of the cash flows starting with the purchase amount and finishing with the redemption amount or the bond value on whatever date you end on if the bond is not yet redeemed.

https://www.financialwisdomforum.org/gu ... f/xirr.htm

Gummy stuff is a great investment blog from the old days by a guy named Peter Ponzi.

In this case for the IRR up to today the first entry is the negative of whatever the price of that \$100 face bond was (it could be \$100 or it may be different) and the date you bought it. The other entries are the series of coupon payments, which will be \$2.50 each, and the dates, which will be twice a year six months apart. The last entry is the price of the bond on the market today and today's date. XIRR as calculated in Excel is annualized.
Topic Author
erma
Posts: 40
Joined: Sun Aug 18, 2024 2:54 pm
Location: Italy

### Re: Derivation of YTM formula

dbr wrote: Wed Sep 04, 2024 4:16 pm Since there are cash flows out of the investment return in a simple sense is not defined.
I see what you mean, thanks for the clarification and the Gummy stuff source!
#Cruncher
Posts: 4067
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

### Re: Derivation of YTM formula

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``````