Login     Register

        Contact Us     Search

What’s new in Finance 1.09

May 21

Written by: Charles Flock
5/21/2012 2:26 PM  RssIcon

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 day-count 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 interest-at-maturity 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 user-supplied date, including holidays. This function assumes the Saturday and Sunday are non-business days
 
Calculates the specified number of business days from a user-supplied date, including holidays and user-specified 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 Feb-29
 
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 non-business 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 day-count conventions in the accrued interest calculations. The day-counts 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 non-end-of-month
11
Actual/Actual non-end-of-month
12
Actual/360 non-end-of-month
13
Actual/365 non-end-of-month
14
European 30/360 non-end-of-month
15
30/360 ISDA non-end-of-month
16
NL/ACT non-end-of-month
17
NL/365 non-end-of-month
18
NL/360 non-end-of-month
19
A/364 non-end-of-month
20
BUS/252
21
Actual/ISDA
22
Actual/ISMA
23
Actual/365L
24
Actual/AFB
30
BUS/252 non-end-of-month

 
Two of these day-count conventions, BUS/252 and BUS/252 non-end-of-month, 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('2013-02-28','2013-02-28',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 widely-known 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 Feb-29. So, the NL stands for No Leap-year 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 no-leap-year basis, it can actually be combined with other XLeratorDB functions to calculate internal rates of return on a no-leap-year basis.
The BUSDAYS function calculates the number of business days between two dates, assuming that Saturday and Sunday are weekend days and using a user-supplied 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 user-supplied 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('2012-05-21','D',5,'F',wct.NBD(holiday))
,wct.BUSINESSDATE('2012-05-21','D',9,'F',wct.NBD(holiday))
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
----------------------- -----------------------
2012-05-29 00:00:00.000 2012-06-04 00:00:00.000
If we can make deliveries on Saturdays, we could have entered the following SQL.
SELECT wct.BUSINESSDATEWE('2012-05-21','D',5,'F',wct.NBD(holiday),0,NULL)
,wct.BUSINESSDATEWE('2012-05-21','D',9,'F',wct.NBD(holiday),0,NULL)
FROM HOLIDAYS
WHERE COUNTRY = 'US'
This produces the following result.
----------------------- -----------------------
2012-05-26 00:00:00.000 2012-06-01 00:00:00.000
 
New Time Value of Money Functions
We have added 3 new time-value-of-money 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 high-definition televisions and on May-21-2012 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 15th of July. Further, he tells you that there will 36 monthly payments of 50.22 due on the 15th 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
      ,'2012-05-21'     --Purchase Date
      ,50.22            --Monthly Payment
      ,'2012-07-15'     --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
      ,'2012-05-21'     --Purchase Date
      ,'2012-07-15'     --First Payment Date
      ,360              --Days in Year
      ), 2) as [Purchase Price]
This produces the following result.
        Purchase Price
----------------------
                  1500
The table-valued 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
      ,'2012-05-21'     --Loan Date
      ,NULL             --Monthly Payment
      ,.12              --Rate
      ,'2012-07-15'     --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
2012-05-21
0
0
0
0
1500
1
2012-07-15
1500
50.22
27
23.22
1476.78
2
2012-08-15
1476.78
50.22
14.77
35.45
1441.33
3
2012-09-15
1441.33
50.22
14.41
35.81
1405.52
4
2012-10-15
1405.52
50.22
14.06
36.16
1369.36
5
2012-11-15
1369.36
50.22
13.69
36.53
1332.83
6
2012-12-15
1332.83
50.22
13.33
36.89
1295.94
7
2013-01-15
1295.94
50.22
12.96
37.26
1258.68
8
2013-02-15
1258.68
50.22
12.59
37.63
1221.05
9
2013-03-15
1221.05
50.22
12.21
38.01
1183.04
10
2013-04-15
1183.04
50.22
11.83
38.39
1144.65
11
2013-05-15
1144.65
50.22
11.45
38.77
1105.88
12
2013-06-15
1105.88
50.22
11.06
39.16
1066.72
13
2013-07-15
1066.72
50.22
10.67
39.55
1027.17
14
2013-08-15
1027.17
50.22
10.27
39.95
987.22
15
2013-09-15
987.22
50.22
9.87
40.35
946.87
16
2013-10-15
946.87
50.22
9.47
40.75
906.12
17
2013-11-15
906.12
50.22
9.06
41.16
864.96
18
2013-12-15
864.96
50.22
8.65
41.57
823.39
19
2014-01-15
823.39
50.22
8.23
41.99
781.4
20
2014-02-15
781.4
50.22
7.81
42.41
738.99
21
2014-03-15
738.99
50.22
7.39
42.83
696.16
22
2014-04-15
696.16
50.22
6.96
43.26
652.9
23
2014-05-15
652.9
50.22
6.53
43.69
609.21
24
2014-06-15
609.21
50.22
6.09
44.13
565.08
25
2014-07-15
565.08
50.22
5.65
44.57
520.51
26
2014-08-15
520.51
50.22
5.21
45.01
475.5
27
2014-09-15
475.5
50.22
4.76
45.46
430.04
28
2014-10-15
430.04
50.22
4.3
45.92
384.12
29
2014-11-15
384.12
50.22
3.84
46.38
337.74
30
2014-12-15
337.74
50.22
3.38
46.84
290.9
31
2015-01-15
290.9
50.22
2.91
47.31
243.59
32
2015-02-15
243.59
50.22
2.44
47.78
195.81
33
2015-03-15
195.81
50.22
1.96
48.26
147.55
34
2015-04-15
147.55
50.22
1.48
48.74
98.81
35
2015-05-15
98.81
50.22
0.99
49.23
49.58
36
2015-06-15
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
      ,'2012-05-21'     --Loan Date
      ,55               --Monthly Payment
      ,.12              --Rate
      ,'2012-07-15'     --First Payment Date
      ,36               --Number of Payments
      ,12               --Payments per Year
      ,360              --Days in Year
      ,0