YTM calculation

Question

Hello,

I am trying to use excel to calculate the YTM and I have different formulas...

=RATE(period, InterestFV,PV,FV)
=RATE(10,7%-1000,985,-1000)
or
=SUM(Interest +((FV-PV)/n)/[(FV+PV/2)])
=SUM(7%+((1000-985)/10)/(1000+985)/2)

but unfortunately I do not get the same result...
with the value:
Period (n) 10
Value (FV)1000
Coupon (Interest) 7%
Interest $70
Price Value(PV) 985

I am getting 7.21% with the RATE
and 7.04% with the SUM

Any idea why this difference?

Thanks,
Dom

Answer 1

I am assuming that you are attempting to calculate Yield to Maturity. If so. please refer to the following YIELD function at the following Microsoft site and see if that helps.

https://support.microsoft.com/en-us/office/yield-function-f5f5ca43-c4bd-434f-8bd2-ed3c9727a4fe

Answer 2

Hello,

Thanks for this,
unfortunately this formula is giving a third value!!! different from the two previous one...
trying to compare the various methods and nothing matched...

Thanks,
Dom

Answer 3

I will be most surprised if the Microsoft Excel Function is incorrect but I guess stranger things have occurred.

Unfortunately, I cannot assist any further.

Hopefully someone with a high level of Math skill will pick up on this and establish the accuracy one way or the other.

Answer 4

Hello,

=RATE(period, InterestFV,PV,FV)
=RATE(10,7%-1000,985,-1000)
or
=SUM(Interest +((FV-PV)/n)/[(FV+PV/2)])
=SUM(7%+((1000-985)/10)/(1000+985)/2)
or
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
=YIELD(10,5,7,1000,1000,2,)

but unfortunately I do not get the same result...
with the value:
Period (n) 10
Value (FV)1000
Coupon (Interest) 7%
Interest $70
Price Value(PV) 985

I am getting 7.21% with the RATE
and 7.04% with the SUM
and -19.15% with the YIELD

Thanks,
Dom