anandbohra
12-02-2009, 12:58 AM
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
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