M
M Welinder
I don't quite get how the number of coupons is calculated per ODDFPRICE.
In fact, I think XL does it wrong. Consider...
A1=1-Mar-2001 [settlement]
A2=30-Apr-2016 [maturity]
A3=17-Mar-2000 [issue]
A4=30-Apr-2001 [first coupon]
A5=0% [rate]
A6=1% [yield]
A7=$100 [redemption]
A8=2 [frequency]
A9=1 [basis]
A11=ODDFPRICE(A1,A2,...,A9)
A12=LOG(A7/A11)/LOG(1+A6/A8)
A13=COUPNUM(A4-1,A2,A8,A9)
I chose rate=0% to simplify matters greatly. That way, A12 now holds N-1+DSC/E
using terms from the help text. I get 31.33, so N=32. I don't see how there
can be more than 31 half-years between first coupon and maturity.
With 29-Apr, 1-May, or 31-May in A2 and A4, I get N=31 as expected. This is
the same number that A13 gives me in all four cases.
Am I wrong, or is Excel wrong?
Any ideas what algorithm Excel is using for this?
In fact, I think XL does it wrong. Consider...
A1=1-Mar-2001 [settlement]
A2=30-Apr-2016 [maturity]
A3=17-Mar-2000 [issue]
A4=30-Apr-2001 [first coupon]
A5=0% [rate]
A6=1% [yield]
A7=$100 [redemption]
A8=2 [frequency]
A9=1 [basis]
A11=ODDFPRICE(A1,A2,...,A9)
A12=LOG(A7/A11)/LOG(1+A6/A8)
A13=COUPNUM(A4-1,A2,A8,A9)
I chose rate=0% to simplify matters greatly. That way, A12 now holds N-1+DSC/E
using terms from the help text. I get 31.33, so N=32. I don't see how there
can be more than 31 half-years between first coupon and maturity.
With 29-Apr, 1-May, or 31-May in A2 and A4, I get N=31 as expected. This is
the same number that A13 gives me in all four cases.
Am I wrong, or is Excel wrong?
Any ideas what algorithm Excel is using for this?