calculate implied ytm

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i'm trying to calculate the implied ytm for a bond 4,5year six month payment
given nper=9
price of a Fv1000=1277,61
coupon rate p.a 11,5%
coupon frequency semiannual
using rate function the xl worksheet come up with an error which i'm trying
to figuredit out but couldn't. +rate function ask me for a guess, but i was
wxpecting a switch.. does anyone know how to do this?.
 
write more clearly and don't abreviate. YOU may know what you're talking
about but NOBODY else does
 
Azzurra said:
i'm trying to calculate the implied ytm for a bond 4,5year six month payment
given nper=9
price of a Fv1000=1277,61
coupon rate p.a 11,5%
coupon frequency semiannual
Click to expand...

I am not very knowledgable about bonds, but my understanding is that
yield to maturity is the IRR of the following cash flow in your case:
-1277.61 initially, followed by 8 periods of 57.50 (1000*11.5%/2), and
1057.50 in the 9th period (1000 plus 57.50). Putting those values into
B1:B10, the annualized IRR (4.65%) can be computed by either of the
following:

=fv(irr(B1:B10, 2, 0, -1) - 1

=fv(rate(9, 1000*11.5%/2, -1277.61, 1000), 2, 0, -1) - 1

By the way, fv(r,2,0,-1) is the same as (1+r)^2, if you prefer.

Assigning arbitrary dates to the cash flows (A1=1/1/2006, A2=6/1/2006,
etc), XIRR(B1:B10,A1:A10) returns 4.74%.

I am surprised that the result of YIELDMAT() is not close to the XIRR()
result when I use a "basis" argument of 3 (actual/365). Using an
arbitrary issue date of 12/31/2005, YIELDMAT() returns 4.08%.
using rate function the xl worksheet come up with an error which i'm trying
to figuredit out but couldn't. +rate function ask me for a guess, but i was
wxpecting a switch.. does anyone know how to do this?.
Click to expand...

I was able to use RATE() without a "guess" argument. If you post your
RATE() formula, perhaps we can help you out. But I can understand your
difficulty. Choosing a "guess" is sometimes non-trivial.
 
Back
Top