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 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
|
|