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