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                --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
55
27
28
1472
2
2012-08-15
1472
55
14.72
40.28
1431.72
3
2012-09-15
1431.72
55
14.32
40.68
1391.04
4
2012-10-15
1391.04
55
13.91
41.09
1349.95
5
2012-11-15
1349.95
55
13.5
41.5
1308.45
6
2012-12-15
1308.45
55
13.08
41.92
1266.53
7
2013-01-15
1266.53
55
12.67
42.33
1224.2
8
2013-02-15
1224.2
55
12.24
42.76
1181.44
9
2013-03-15
1181.44
55
11.81
43.19
1138.25
10
2013-04-15
1138.25
55
11.38
43.62
1094.63
11
2013-05-15
1094.63
55
10.95
44.05
1050.58
12
2013-06-15
1050.58
55
10.51
44.49
1006.09
13
2013-07-15
1006.09
55
10.06
44.94
961.15
14
2013-08-15
961.15
55
9.61
45.39
915.76
15
2013-09-15
915.76
55
9.16
45.84
869.92
16
2013-10-15
869.92
55
8.7
46.3
823.62
17
2013-11-15
823.62
55
8.24
46.76
776.86
18
2013-12-15
776.86
55
7.77
47.23
729.63
19
2014-01-15
729.63
55
7.3
47.7
681.93
20
2014-02-15
681.93
55
6.82
48.18
633.75
21
2014-03-15
633.75
55
6.34
48.66
585.09
22
2014-04-15
585.09
55
5.85
49.15
535.94
23
2014-05-15
535.94
55
5.36
49.64
486.3
24
2014-06-15
486.3
55
4.86
50.14
436.16
25
2014-07-15
436.16
55
4.36
50.64
385.52
26
2014-08-15
385.52
55
3.86
51.14
334.38
27
2014-09-15
334.38
55
3.34
51.66
282.72
28
2014-10-15
282.72
55
2.83
52.17
230.55
29
2014-11-15
230.55
55
2.31
52.69
177.86
30
2014-12-15
177.86
55
1.78
53.22
124.64
31
2015-01-15
124.64
55
1.25
53.75
70.89
32
2015-02-15
70.89
55
0.71
54.29
16.6
33
2015-03-15
16.6
16.77
0.17
16.6
0

So, you can see that by increasing the payments a few dollars per month, the term of the loan is actually shortened by three months and the total value of the monthly payemnts decreases by 30.87.
Bond amortization
We have introduced 2 new functions for bond amortization: as scalar function, AMORTRATE, and a table-valued function BONDAMORT. Both of these functions calculate the constant daily effective rate used to amortize (accrete) the premium (discount) on a bond. The premium /discount is the difference between the purchase price of the bond (exclusive of accrued interest) and the redemption value of the bond. The constant daily effective rate is a value such that rate at which the income is recognized on the bond is constant over the life of the bond.
Here’s a very a simple example of a bond a few days before maturity.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
AMORTRATE
2012-06-03
27
0.0000
0.0000
0.0000
0.0000
999900.0000
0.00000000
2012-06-04
26
999900.0000
138.8889
142.5857
3.6968
999903.6968
0.00014260
2012-06-05
25
999903.6968
138.8889
142.5863
3.6974
999907.3942
0.00014260
2012-06-06
24
999907.3942
138.8889
142.5868
3.6979
999911.0921
0.00014260
2012-06-07
23
999911.0921
138.8889
142.5873
3.6984
999914.7905
0.00014260
2012-06-08
22
999914.7905
138.8889
142.5878
3.6990
999918.4895
0.00014260
2012-06-09
21
999918.4895
138.8889
142.5884
3.6995
999922.1890
0.00014260
2012-06-10
20
999922.1890
138.8889
142.5889
3.7000
999925.8890
0.00014260
2012-06-11
19
999925.8890
138.8889
142.5894
3.7005
999929.5895
0.00014260
2012-06-12
18
999929.5895
138.8889
142.5900
3.7011
999933.2906
0.00014260
2012-06-13
17
999933.2906
138.8889
142.5905
3.7016
999936.9922
0.00014260
2012-06-14
16
999936.9922
138.8889
142.5910
3.7021
999940.6943
0.00014260
2012-06-15
15
999940.6943
138.8889
142.5915
3.7026
999944.3969
0.00014260
2012-06-16
14
999944.3969
138.8889
142.5921
3.7032
999948.1001
0.00014260
2012-06-17
13
999948.1001
138.8889
142.5926
3.7037
999951.8038
0.00014260
2012-06-18
12
999951.8038
138.8889
142.5931
3.7042
999955.5080
0.00014260
2012-06-19
11
999955.5080
138.8889
142.5936
3.7048
999959.2128
0.00014260
2012-06-20
10
999959.2128
138.8889
142.5942
3.7053
999962.9181
0.00014260
2012-06-21
9
999962.9181
138.8889
142.5947
3.7058
999966.6239
0.00014260
2012-06-22
8
999966.6239
138.8889
142.5952
3.7063
999970.3302
0.00014260
2012-06-23
7
999970.3302
138.8889
142.5958
3.7069
999974.0371
0.00014260
2012-06-24
6
999974.0371
138.8889
142.5963
3.7074
999977.7445
0.00014260
2012-06-25
5
999977.7445
138.8889
142.5968
3.7079
999981.4524
0.00014260
2012-06-26
4
999981.4524
138.8889
142.5973
3.7085
999985.1609
0.00014260
2012-06-27
3
999985.1609
138.8889
142.5979
3.7090
999988.8699
0.00014260
2012-06-28
2
999988.8699
138.8889
142.5984
3.7095
999992.5794
0.00014260
2012-06-29
1
999992.5794
138.8889
142.5989
3.7100
999996.2894
0.00014260
2012-06-30
0
999996.2894
138.8889
142.5995
3.7106
1000000.0000
0.00014260

 
As you can see, the amount of income generated every day changes, but the rate remains constant. So the BONDAMORT function has figured what that rate is, and has generated an amortization schedule using the calculated rate. The difference between the daily effective rate value and the daily coupon appears in the dly_amort column. This amount represents an adjustment to the coupon income (expense) of the bond and is taken as an adjustment against book value.
Here’s an example of a bond with a premium.
SELECT *
,CASE begin_book_val
      WHEN 0 THEN 0
      ELSE dly_eff_rate / begin_book_val
 END as [AMORTRATE]
FROM wct.BONDAMORT(
 '2012-06-03'           --Settlement
,'2012-06-30'           --Maturity
,0.05                   --Rate
,1000000.00             --FaceAmount
,1000100.00             --CleanPrice
,NULL                   --Redemption
,2                      --Frequency
,'0'                    --Basis
,NULL                   --IssueDate
,NULL                   --FirstInterestDate
,NULL                   --LastInterestDate
,NULL                   --Holidays 
)
This produces the following result.

amort_date
dtm
begin_book_val
dly_coup
dly_eff_rate
dly_amort
end_book_val
AMORTRATE
2012-06-03
27
0.0000
0.0000
0.0000
0.0000
1000100.0000
0.00000000
2012-06-04
26
1000100.0000
138.8889
135.1917
-3.6972
1000096.3028
0.00013518
2012-06-05
25
1000096.3028
138.8889
135.1912
-3.6977
1000092.6051
0.00013518
2012-06-06
24
1000092.6051
138.8889
135.1907
-3.6982
1000088.9069
0.00013518
2012-06-07
23
1000088.9069
138.8889
135.1902
-3.6987
1000085.2082
0.00013518
2012-06-08
22
1000085.2082
138.8889
135.1897
-3.6992
1000081.5090
0.00013518
2012-06-09
21
1000081.5090
138.8889
135.1892
-3.6997
1000077.8093
0.00013518
2012-06-10
20
1000077.8093
138.8889
135.1887
-3.7002
1000074.1091
0.00013518
2012-06-11
19
1000074.1091
138.8889
135.1882
-3.7007
1000070.4084
0.00013518
2012-06-12
18
1000070.4084
138.8889
135.1877
-3.7012
1000066.7072
0.00013518
2012-06-13
17
1000066.7072
138.8889
135.1872
-3.7017
1000063.0055
0.00013518
2012-06-14
16
1000063.0055
138.8889
135.1867
-3.7022
1000059.3033
0.00013518
2012-06-15
15
1000059.3033
138.8889
135.1862
-3.7027
1000055.6006
0.00013518
2012-06-16
14
1000055.6006
138.8889
135.1857
-3.7032
1000051.8974
0.00013518
2012-06-17
13
1000051.8974
138.8889
135.1852
-3.7037
1000048.1937
0.00013518
2012-06-18
12
1000048.1937
138.8889
135.1847
-3.7042
1000044.4895
0.00013518
2012-06-19
11
1000044.4895
138.8889
135.1842
-3.7047
1000040.7848
0.00013518
2012-06-20
10
1000040.7848
138.8889
135.1837
-3.7052
1000037.0796
0.00013518
2012-06-21
9
1000037.0796
138.8889
135.1832
-3.7057
1000033.3739
0.00013518
2012-06-22
8
1000033.3739
138.8889
135.1827
-3.7062
1000029.6677
0.00013518
2012-06-23
7
1000029.6677
138.8889
135.1822
-3.7067
1000025.9610
0.00013518
2012-06-24
6
1000025.9610
138.8889
135.1817
-3.7072
1000022.2538
0.00013518
2012-06-25
5
1000022.2538
138.8889
135.1812
-3.7077
1000018.5461
0.00013518
2012-06-26
4
1000018.5461
138.8889
135.1807
-3.7082
1000014.8379
0.00013518
2012-06-27
3
1000014.8379
138.8889
135.1802
-3.7087
1000011.1291
0.00013518
2012-06-28
2
1000011.1291
138.8889
135.1797
-3.7092
1000007.4199
0.00013518
2012-06-29
1
1000007.4199
138.8889
135.1792
-3.7097
1000003.7102
0.00013518
2012-06-30
0
1000003.7102
138.8889
135.1787
-3.7102
1000000.0000
0.00013518

 
The BONDAMORT function supports a wide variety of bonds, including Odd First Coupon, Odd Last Coupon, Zero-Coupon, Interest-At-Maturity, and others. It supports all the day-count conventions mentioned at the beginning of this article.
The AMORTRATE function simply lets you calculate the rate that is implicit in the BONDAMORT function. Based on the previous example, we could calculate
SELECT wct.AMORTRATE(
 '2012-06-03'           --Settlement
,'2012-06-30'           --Maturity
,0.05                   --Rate
,1000000.00             --FaceAmount
,1000100.00             --CleanPrice
,NULL                   --Redemption
,2                      --Frequency
,'0'                    --Basis
,NULL                   --IssueDate
,NULL                   --FirstInterestDate
,NULL                   --LastInterestDate
,NULL                   --Holidays 
) as [Amortization Rate]
This produces the following result.
     Amortization Rate
----------------------
 0.000135178171841108
 
We think that these functions are a powerful addition to XLeratorDB / finance, supporting calculations for a wide variety of financial instruments in most financial markets. Since XLeratorDB becomes an object inside your SQL Server database, you can call these functions from any development platform that can open a connection to the database. Whether you are a .NET developer or you prefer Python, Java, C++, PHP, or Perl (to name a few), you can use XLeratorDB with your SQL Server database. You can use it in SSRS, in SMS, or even call it from a VBA macro in EXCEL.

As always, if there are functions that you would like to see added to the XLeratorDB libraries, just let us know, by dropping us an e-mail at support@westclintech.com. All comments are welcome.

Tags:
Categories:

Search Blogs

Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service