What’s new in Finance 1.09
May
21
Written by:
Charles Flock
5/21/2012 2:26 PM
We introduce seventeen useful new functions which help analyze accrued interest on bonds, create payments schedules for commercial loans and leases, calculate dates and the number of days between two dates incorporating weekends, holidays, and daycount conventions, and perform time value of money calculations when there is an odd first period.
We have added the following new functions to XLeratorDB / finance and XLeratorDB / finance 2008.

Calculates the accrued interest on a bond as at the settlement date


Calculates the accrued interest on an interestatmaturity security from the issue date to the settlement date


Calculates the accrued interest in the odd first coupon period from the issue date to the settlement date


Calculates the accrued interest in the odd last coupon period from the last coupon date to the settlement date


Calculates the accrued interest in a regular coupon period from the previous coupon date to the settlement date


Calculates the constant daily effective rate on a bond, used for the daily amortization of the premium or discount on a bond


Creates an amortization schedule for a commercial loan or lease


Creates a daily amortization schedule (including the daily coupon interest) for a bond


Calculates the number of business days between 2 dates, including holidays


Calculates the specified number of business days from a usersupplied date, including holidays. This function assumes the Saturday and Sunday are nonbusiness days


Calculates the specified number of business days from a usersupplied date, including holidays and userspecified weekend days


Calculates the number of days between a start date (inclusive) and an end date (exclusive) using any one of three 30/360 day count conventions


Calculates the number of says between a start date (inclusive) and an end date (exclusive), excluding all occurrences of Feb29


Creates an amortization schedule for an installment loan or a lease


Calculates the rate of regular periodic cash flows with odd first period.


Creates a string on nonbusiness days which can be passed into other XLeratorDB functions


Calculates the present value (PV) of regular periodic cash flows with an odd first period.

New Accrued Interest Calculations
We have added 5 new functions for the calculation of accrued interest on bonds: AIFACTOR, AIFACTOR_IAM, AIFACTOR_OFC, AIFACTOR_OLC, and AIFACTOR_RPI. Each of these functions calculates the Accrued Interest Factor for a bond. To calculate the monetary value of the accrued interest, you simply multiply the accrued interest factor by the face amount of the bond.
These new functions were added to provide support for more daycount conventions in the accrued interest calculations. The daycounts supported for accrued interest calculations are:
0

US (NASD) 30/360

1

Actual/Actual

2

Actual/360

3

Actual/365

4

European 30/360

5

30/360 ISDA

6

NL/ACT

7

NL/365

8

NL/360

9

A/364

10

US (NASD) 30/360 nonendofmonth

11

Actual/Actual nonendofmonth

12

Actual/360 nonendofmonth

13

Actual/365 nonendofmonth

14

European 30/360 nonendofmonth

15

30/360 ISDA nonendofmonth

16

NL/ACT nonendofmonth

17

NL/365 nonendofmonth

18

NL/360 nonendofmonth

19

A/364 nonendofmonth

20

BUS/252

21

Actual/ISDA

22

Actual/ISMA

23

Actual/365L

24

Actual/AFB

30

BUS/252 nonendofmonth

Two of these daycount conventions, BUS/252 and BUS/252 nonendofmonth, only accrue interest on business days. This requires that you pass a holiday variable into the functions, and the aggregate function NBD will create the properly formatted variable which can then be passed into the relevant accrued interest calculation function.
We decided to create new functions rather than change the signatures of existing functions like ACCRINT and ACCRINTM. ACCRINT and ACCRINTM still behave as they did in previous releases and do not support the additional day count conventions, keeping them consistent with the EXCEL functions of the same name.
New Date Calculation functions
We have added 5 new date calculation functions: BUSDAYS, BUSINESSDATE, BUSINESSDATEWE, DAY360, and DAYSNL.
The DAYS360 function calculates the number of days between 2 dates using any of the following three methods: US, GERMAN, and SPECIAL GERMAN. This function does not work the same way as the EXCEL function of the same name. For example, in EXCEL, if you enter =DAYS360(DATE(2013,02,28),DATE(2013,02,28),0), it returns 2 (which doesn’t really make any sense) whereas this SQL,
SELECT wct.DAYS360('20130228','20130228',0)
returns zero (which seems intuitively obvious). EXCEL is also very unclear about what it means with respect to its method variable, which it defines as “A logical value that specifies whether to use the U.S. or European method in the calculation”. Which European method is it talking about? We certainly have no idea. So we explicitly support the 2 most widelyknown European methods, the German and the Special German methods, as well as the US method.
The DAYSNL function calculates the number of days from a start date and end date without counting any Feb29. So, the NL stands for No Leapyear and makes it easier to do calculations assuming that every year has exactly 365 days, instead of 365 ^{97}/_{400} days.
While this is designed for securities that trade on a noleapyear basis, it can actually be combined with other XLeratorDB functions to calculate internal rates of return on a noleapyear basis.
The BUSDAYS function calculates the number of business days between two dates, assuming that Saturday and Sunday are weekend days and using a usersupplied calendar. This is very similar to the existing NETWORKDAYS function, except that the NETWORKDAYS function includes the end date and the BUSDAYS function does not.
The BUSINESSDATE function lets you calculate a specified number of days forward or backwards from a start date, using Saturday and Sunday as weekend days and using a usersupplied variable of holiday dates. If you want to use other than Saturday and Sunday as weekend days, you can use the BUSINESSDATEWE function, which allows you to specify none, one, or two weekend days of your choice.
The BUSINESSDATE function will also calculate a date a specified number of weeks, months, or years from the start date, and then adjust the calculated date based on a date roll rule: A for the actual date, F for the following business date, P for the preceding business date, M for the modified following date, meaning that we use the following business date unless it would change the month of the calculated date, in which case we use the preceding business date, and MP for modified preceding, in which case we use the preceding date, unless it is in a different month, in which case we use the following business date.
For example, if you are promising delivery to a customer in 5 to 9 business days for a customer and you have already entered US holidays in a holiday table, you could use the following SQL to do the calculation.
SELECT wct.BUSINESSDATE('20120521','D',5,'F',wct.NBD(holiday))
,wct.BUSINESSDATE('20120521','D',9,'F',wct.NBD(holiday))
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
 
20120529 00:00:00.000 20120604 00:00:00.000
If we can make deliveries on Saturdays, we could have entered the following SQL.
SELECT wct.BUSINESSDATEWE('20120521','D',5,'F',wct.NBD(holiday),0,NULL)
,wct.BUSINESSDATEWE('20120521','D',9,'F',wct.NBD(holiday),0,NULL)
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
 
20120526 00:00:00.000 20120601 00:00:00.000
New Time Value of Money Functions
We have added 3 new timevalueofmoney functions. The ODDPV function calculates the present value of a series of periodic cash flows where there is on odd first period. In other words, the first period is either longer or shorter than any other period, and all the other periods are assumed to be of equal length. The LRATE function, calculates the rate on a set of periodic cash flows where there is an odd first period.
For example, let’s say that there was a big sale on highdefinition televisions and on May212012 you decide to buy one for $1,500, and the salesman offers you a special financing deal where there is no money down and no payments until the 15^{th} of July. Further, he tells you that there will 36 monthly payments of 50.22 due on the 15^{th} of every month. What’s the annual interest rate?
We can solve this easily using the LRATE function. Let’s enter the following SQL.
SELECT wct.LRATE(
1500 Purchase Price
,'20120521' Purchase Date
,50.22 Monthly Payment
,'20120715' First Payment Date
,36 Number of Payments
,12 Payments per Year
,360 Days in Year
,0 Balloon payment
,NULL Interest Raule
,NULL Guess
) as [Annual Rate]
This produces the following result.
Annual Rate

0.120053278172357
We can use this annual rate to calculate the present value of the payments, to see if it agrees with the purchase price.
SELECT ROUND(wct.ODDPV(
0.120053278172357 Rate
,36 Number of Payments
,50.22 Monthly Payment
,0 Balloon payment
,12 Payments per Year
,'20120521' Purchase Date
,'20120715' First Payment Date
,360 Days in Year
), 2) as [Purchase Price]
This produces the following result.
Purchase Price

1500
The tablevalued function LPMTSCHED let’s us create an amortization schedule for these type of loans. In talking about an amortization schedule we mean schedule that shows the principal and interest payment amounts on the scheduled payment dates. This is different than that amortization of premium/discount on bonds which we will discuss in the BONDAMORT function.
SELECT num_pmt
,date_pmt
,amt_prin_init
,amt_pmt
,amt_int_pay
,amt_prin_pay
,amt_prin_end
FROM wct.LPMTSCHED(
1500 Loan Amount
,'20120521' Loan Date
,NULL Monthly Payment
,.12 Rate
,'20120715' First Payment Date
,36 Number of Payments
,12 Payments per Year
,360 Days in Year
,0 Balloon payment
,NULL Interest Rule
,2 Decimals
,NULL Last Payment Number
)
This produces the following result.
num_pmt

date_pmt

amt_prin_init

amt_pmt

amt_int_pay

amt_prin_pay

amt_prin_end

0

20120521

0

0

0

0

1500

1

20120715

1500

50.22

27

23.22

1476.78

2

20120815

1476.78

50.22

14.77

35.45

1441.33

3

20120915

1441.33

50.22

14.41

35.81

1405.52

4

20121015

1405.52

50.22

14.06

36.16

1369.36

5

20121115

1369.36

50.22

13.69

36.53

1332.83

6

20121215

1332.83

50.22

13.33

36.89

1295.94

7

20130115

1295.94

50.22

12.96

37.26

1258.68

8

20130215

1258.68

50.22

12.59

37.63

1221.05

9

20130315

1221.05

50.22

12.21

38.01

1183.04

10

20130415

1183.04

50.22

11.83

38.39

1144.65

11

20130515

1144.65

50.22

11.45

38.77

1105.88

12

20130615

1105.88

50.22

11.06

39.16

1066.72

13

20130715

1066.72

50.22

10.67

39.55

1027.17

14

20130815

1027.17

50.22

10.27

39.95

987.22

15

20130915

987.22

50.22

9.87

40.35

946.87

16

20131015

946.87

50.22

9.47

40.75

906.12

17

20131115

906.12

50.22

9.06

41.16

864.96

18

20131215

864.96

50.22

8.65

41.57

823.39

19

20140115

823.39

50.22

8.23

41.99

781.4

20

20140215

781.4

50.22

7.81

42.41

738.99

21

20140315

738.99

50.22

7.39

42.83

696.16

22

20140415

696.16

50.22

6.96

43.26

652.9

23

20140515

652.9

50.22

6.53

43.69

609.21

24

20140615

609.21

50.22

6.09

44.13

565.08

25

20140715

565.08

50.22

5.65

44.57

520.51

26

20140815

520.51

50.22

5.21

45.01

475.5

27

20140915

475.5

50.22

4.76

45.46

430.04

28

20141015

430.04

50.22

4.3

45.92

384.12

29

20141115

384.12

50.22

3.84

46.38

337.74

30

20141215

337.74

50.22

3.38

46.84

290.9

31

20150115

290.9

50.22

2.91

47.31

243.59

32

20150215

243.59

50.22

2.44

47.78

195.81

33

20150315

195.81

50.22

1.96

48.26

147.55

34

20150415

147.55

50.22

1.48

48.74

98.81

35

20150515

98.81

50.22

0.99

49.23

49.58

36

20150615

49.58

50.08

0.5

49.58

0

We can actually use this function to adjust the schedule, for example, by rounding the payment up to $55, but holding the interest at 12%
SELECT num_pmt
,date_pmt
,amt_prin_init
,amt_pmt
,amt_int_pay
,amt_prin_pay
,amt_prin_end
FROM wct.LPMTSCHED(
1500 Loan Amount
,'20120521' Loan Date
,55 Monthly Payment
,.12 Rate
,'20120715' First Payment Date
,36 Number of Payments
,12 Payments per Year
,360 Days in Year
,0 Balloon payment
,NULL Interest Rule
,2 Decimals
,NULL Last Payment Number
)
This produces the following result.
num_pmt

date_pmt

amt_prin_init

amt_pmt

amt_int_pay

amt_prin_pay

amt_prin_end

0

20120521

0
