Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculate implied ytm
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?. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculate implied ytm
write more clearly and don't abreviate. YOU may know what you're talking
about but NOBODY else does -- HTH, Gary Brown "Azzurra" wrote: 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?. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculate implied ytm
Azzurra wrote:
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 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?. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't get excel to calculate | Excel Worksheet Functions | |||
Turn off calculate event | Excel Discussion (Misc queries) | |||
any formula to auto calculate 1st-12th is 12 days pls? | Excel Discussion (Misc queries) | |||
Calculate appears in status bar | Excel Discussion (Misc queries) | |||
Spreadsheet Won't Calculate | Excel Discussion (Misc queries) |