Bond YTM- Excel (Goal Seek) vs BA II

[Sixcarbs]

Hello All,

Reviewing one of David's video and there is a very basic example of a bond.

2 years, 6% coupon, Semi-Annual Payments, $100 Face Value.

Spot rates are:
5%, 5.8%, 6.4%, and 6.8% for .5, 1, 1.5, and 2 years.

Bond values at $98.39. Fine.

Calculate YTM using Goal Seek and come up with 6.76%, just like David in his video.

But when I run this on the calculator I keep getting a different figure. I get 2x3.44%=6.88%.

N=4
PMT=3
PV=-98.39
FV=100

What am I doing wrong? Going mad here.


Thank you,

Sixcarbs

 

[Detective]

Can you show screenshot of what exactly you Goal Seek in Excel?

The calculator and IRR in Excel assume periodic CFs with a generic period with length between periods being 1.

Goal Seek:



RATE:
=RATE(4,3,-98.39,100,0,0.03)

IRR:
=IRR({-98.39,3,3,3,103},0.03)

All give ~3.44% and * 2 = 6.88%.

 

[Sixcarbs]

Can you show screenshot of what exactly you Goal Seek in Excel?

The calculator and IRR in Excel assume periodic CFs with a generic period with length between periods being 1.

Goal Seek:
View attachment 2594


RATE:
=RATE(4,3,-98.39,100,0,0.03)

IRR:
=IRR({-98.39,3,3,3,103},0.03)

All give ~3.44% and * 2 = 6.88%.

Here is what I get in Excel:



The above matches what David gets in his video. I am searching for an single interest rate in column H that makes the sum of the discounted Cash Flows the same as on the left, $98.39.

My problem is when I put the following into the calculator:

N=4
PV= -$98.39
PMT=$3
FV=$100

I get 3.44 when I compute I/Y. Doubling that figure gets me 6.88%. As David points out in his video the YTM should be just below the final spot because that's where the bulk of the cash flow is.

In David's video there is another example immediately after this one, and Goal Seek and the calculator solution line up.

What am I missing here?

 

[Detective]

@Sixcarbs

The exact conversion to make it an annualized IRR is:

(1+IRR)^2-1 in this case it actually gives 6.99% or basically 7%.

https://www.wolframalpha.com/input/?i=(1+0.034377)^2-1

I don’t have Excel on my phone but using Google Sheets I get answer that agrees with calculator. Can you upload your spreadsheet?

I see your point that YTM should be lower than last rate so I also backed into the PV using your spot rates in column E, and I actually get 98.798, how do you get 98.39? 6.76% YTM does give you PV = 98.8.

 

[Sixcarbs]

@Sixcarbs

The exact conversion to make it an annualized IRR is:

(1+IRR)^2-1 in this case it actually gives 6.99% or basically 7%.

https://www.wolframalpha.com/input/?i=(1+0.034377)^2-1

I don’t have Excel on my phone but using Google Sheets I get answer that agrees with calculator. Can you upload your spreadsheet?

I see your point that YTM should be lower than last rate so I also backed into the PV using your spot rates in column E, and I actually get 98.798, how do you get 98.39? 6.76% YTM does give you PV = 98.8.

View attachment 2598

Attached is my spreadsheet.

 

[Detective]

Attached is my spreadsheet.

Corrupted spreadsheet, can't open it.



The PV = 98.39 is wrong.

Your t=2 cashflow seems too low. I am doing 103/(1+0.068)^2, what are you doing?

 

[Sixcarbs]

Corrupted spreadsheet, can't open it.

View attachment 2600

The PV = 98.39 is wrong.

Your t=2 cashflow seems too low. I am doing 103/(1+0.068)^2, what are you doing?

I am using continuous.

=103*EXP(-2*6.80%)

My numbers are the same as in the BT video.

 

[Matthew Graves]

This is all about compounding assumptions. By using continuous compounding you are making the assumption that the spot rates are continuously compounded rates, obtaining a valuation of 98.39. If you then calculate a YTM in the same fashion with continuous compounding you will obtain a YTM of 6.76%.

If you assume the spot rates are semi-annually compounded rates and do the valuation accordingly you will obtain 98.60. If you calculate a semi-annually compounded YTM from this price you will obtain the same value as before, 6.76%.

However, if you take the valuation from using continuously compounded spot rates (98.39) and back out a semi-annually compounded YTM you will obtain your other value of 6.88%. This is higher than the final spot rate as you are assuming the spot is continuously compounded but the YTM is only semi-annually compounded.

The calculator is assuming semi-annual compounding and you have input the price obtained from using continuous compounding, hence the different YTM. I haven't seen David's video but since he is obtaining 6.76% I assume he is using the same compounding for the YTM as he is for the valuation. If you are consistent in your compounding assumptions you should always obtain 6.76%.

 

[Sixcarbs]

This is all about compounding assumptions. By using continuous compounding you are making the assumption that the spot rates are continuously compounded rates, obtaining a valuation of 98.39. If you then calculate a YTM in the same fashion with continuous compounding you will obtain a YTM of 6.76%.

If you assume the spot rates are semi-annually compounded rates and do the valuation accordingly you will obtain 98.60. If you calculate a semi-annually compounded YTM from this price you will obtain the same value as before, 6.76%.

However, if you take the valuation from using continuously compounded spot rates (98.39) and back out a semi-annually compounded YTM you will obtain your other value of 6.88%. This is higher than the final spot rate as you are assuming the spot is continuously compounded but the YTM is only semi-annually compounded.

The calculator is assuming semi-annual compounding and you have input the price obtained from using continuous compounding, hence the different YTM. I haven't seen David's video but since he is obtaining 6.76% I assume he is using the same compounding for the YTM as he is for the valuation. If you are consistent in your compounding assumptions you should always obtain 6.76%.

That makes sense. I am going to rewatch the video. David does 2 examples. In the first one he uses Continuous Compounding and Goal Seek to find the YTM. He doesn't use teh calculator until the second example. I need to see if he changed to semi-annual compounding on the Excel in that one.

So note to all I guess. The calculator will always assume periodic compounding, not continuous.

Thank you