Solved: Yield to Maturity

[anandbohra]

Hi All,

I want a simple calculation in Excel.
My data is
Bond A
Face Value-1000
Maturity=5 Years
Coupon Rate = 9.51%
Compounding = Quarterly
Current Trading Price = 1143.90

Now If I use Yield function of excel it gives me -1.74% HOW COME??:banghead::banghead::banghead:

if I do the same in my financial calculator
first I will convert to effective rate
=(+1(0.951/4))64-1 which comes to 9.85 (effect function in excle)

Now as per Time value of Money the formula should be

1143.90=98.5 Pvifa(ytm, 5yrs) + 1000 * pvif(ytm,5 yrs)

which as per Goal Seek comes to 6.40%

also If I use IRR function as
-1143.90
98.5
98.5
98.5
98.5
1098.5
=IRR(A1:A6) it gives me 6.40%

THEN WHY YIELD IS NOT DOING THE SAME :dunno:dunno:dunno:dunno:dunno

:helpany urgent help will be highly appreciated.:help

[mdmackillop]

I'm not familiar with Yield, so what is entered into your Yield function?

[Paul_Hossler]

YIELD Help:



Pr is the security's price per $100 face value.
Redemption is the security's redemption value per $100 face value.



=YIELD(
DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())),
DATE(YEAR(NOW())+5,MONTH(NOW()),DAY(NOW())),
0.0951,
1143.9/10,
1000/10,
4,
0)


gives 6.15% which is closer, but since I have hard time balencing my checkbook, my financial background is non-existant

Paul

[anandbohra]

Thanks Paul_Hossler

Actually I was not dividing the same with 10 :bug:
Thank I got exactly the same answer What I wan't :clap:
Hi, mdmackillop
The Yield to Maturity is the total amount one receive from a bond holding it till maturity.
Its the present Value of future coupon payment Plus present value of redemption value discounted at the value (YTM) where total present value comes to current market price

Small example
say bond face value if $100
coupon rate is 5%
bond life is 10 years
frequency of compounding is annual
Current Price is say $95

Now if you hold this bond for 10 years you will receive following amount
$5 every year for 10 years plus $100 at Maturity
now your will convert your future inflow into Present value
so it will be
95 = 5/(1+X)^1 + 5/(1+X)^2+5/(1+X)^3........5/(1+X)^10 + 100/ (1+X)^10
so the value of X in the above equation will be YTM (Yield to Maturity)

Hope above small example will clear you with what is Yield
(To use Yield function you have to add Analysis Toolpak addin)

[mdmackillop]

Pleased to see it solved.
For the future, if you are posing such a question, I see Yield requires
YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)
If you use terms like Face Value, Coupon Rate etc., it confuses my simple mind.

[anandbohra]

Noted Sir,

Next time will use the same terminology as required in Function.:doh:


:thumb :beerchug: