XLeratorDB/financial 1.05 is now available
Apr
19
Written by:
Charles Flock
4/19/2010 5:49 PM
We’ve added monthly coupons to the bond functions, a new bond interest function, and some tweaks to XIRR
We are happy to announce that there is a new release of XLeratorDB/financial available as of April 18, 2010. The biggest change that we have made is that the bond figuration functions now accept 12 as a coupon frequency, which means that the bond pays interest 12 times per year, or monthly. The functions affected by this change are:
|
· ACCRINT
|
|
· COUPDAYBS
|
|
· COUPDAYS
|
|
· COUPDAYSNC
|
|
· COUPNCD
|
|
· COUPNUM
|
|
· COUPPCD
|
|
· DURATION
|
|
· MDURATION
|
|
· ODDFPRICE
|
|
· ODDFYIELD
|
|
· ODDLPRICE
|
|
· ODDLYIELD
|
|
· PRICE
|
|
· YIELD
|
While this is a big enhancement in terms of functionality, there is no change in the way that the functions are called, so if you do not have bonds with monthly coupons, you shouldn’t see any change in these functions.
We also added a new function, BONDINT, which calculates the accrued interest on a bond with regular period coupon payments. BONDINT also supports monthly coupons. The ACCRINT function does pretty much the same thing and produces the same results as EXCEL. The reason we developed a new function is that we don’t really like the EXCEL implementation. The BONDINT function is more like the PRICE function, where you only need the maturity date, settlement date, frequency, interest basis, and coupon rate to calculate the accrued interest. We also included a par value amount. These values are generally readily available.
The ACCRINT function requires additional data, notably the issue and first interest date. The ACCRINT function also has something called a ‘calc_method’ which tells the function to accrue from the issue date or from the last coupon date. I find this confusing and, frankly, it doesn’t work in EXCEL. Since this is supposed to be a function that calculates the accrued interest for a security that pays periodic interest it doesn’t actually make sense to have a first coupon date, and if it is an attempt to accommodate bonds that have an odd first period, the EXCEL calculation of those odd first periods is actually unreliable.
Since it is in EXCEL, it is in XLeratorDB. We just wanted an easier way to get the interest for these types of bonds and created the BONDINT function. We will be doing the same thing for odd first coupons and odd last coupons in the coming weeks.
We also made some minor changes to XIRR, which helps it to resolve when you supply a guess to the function. Generally, our XIRR function does not require a guess, but there were situations where you could supply a guess and we would not resolve within 100 iterations and we would return NULL, even though we would resolve when there was no guess.
Unlike the EXCEL function, the XLeratorDB version of XIRR does not start with a guess of .10. It actually has some intelligence that comes up with a guess based on the cash flow amounts and dates, and it iterates from there. For more information about problems with the EXCEL XIRR function, you can watch these videos.