Finance 1.06 is available
Nov
14
Written by:
Charles Flock
11/14/2010 7:33 PM
Finance version 1.06, with 19 new functions (not found in EXCEL) is now available.
We are happy to announce the release of XLeratorDB/finance 1.06 which contains 17 new scalar functions and 2 new table-valued functions, and improvements to an existing function. The new scalar functions are:
1. CUMLIPMT – calculates the cumulative interest payments on a loan with an odd first period.
2. CUMLPPMT– calculates the cumulative principal payments on a loan with an odd first period.
3. LIPMT – calculates the interest payment for a specified period for a loan with an odd first period.
4. LPMT – calculates the regular periodic payment for a loan with an odd first period.
5. LPPMT – calculates the principal payment for a specified period on a loan with on odd first period.
6. NPD – calculates the next payment date for a loan.
7. NPNO – calculates the next payment number for a loan.
8. PPD – calculates the previous payment date for a loan.
9. PPNO – calculates the previous payment number for a loan.
10. ODDFINT – calculates the accrued interest in the odd period for a bond with an odd first coupon period.
11. ODDLINT – calculates the accrued interest in the odd period for a bond with an odd last coupon period.
12. R78IPMT – calculates the interest payment for a specified period using the Rule of 78.
13. R78PAYOFF – calculates the payoff amount for a loan using the Rule of 78.
14. R78PPMT – calculates the principal payment amount for a specified period using the Rule of 78.
15. R78REBATE – calculates the interest rebate on the prepayment of a loan using the Rule of 78.
16. PERIODRATE – adjust the interest rate from one compounding period to another.
17. TOTALINT – calculates the total interest on a loan.
As you can see, most of these functions have to do with processing of loan and lease type transactions. In earlier releases, loan and lease type functions were available through PMT, IPMT, PPMT, CUMPRINC and CUMIPMT, which were designed to work like the EXCEL functions of the same name. These 5 functions did not address situations where you want to create a regular, periodic payment (in other words, each payment is the same) but where the first period was either longer or shorter than all the other periods.
To do that type of calculation we created the LPMT function, which requires a start date for the loan and a first payment date. This function supports transactions where the loan/lease might commence on the 13th of the month, but the payments are due on the first of the month. And the first payment might be the first of the following month or the month after that. Or it could be six months or even 12 months in the future. The key is that the function will calculate a level payment for the term of the loan.
Given this new function for calculating the payment amount, it follows logically that we needed a new function to calculate the interest and the principal payments for each period which are handled by the LIPMT and LPPMT functions. From there, it’s easy to see that also need a way to calculate the cumulative principal and interest payments, thus giving us CUMLIPMT and CUMLPPMT.
These 5 new functions may also be used when there is no odd first period. The functions themselves figure out whether or not the first payment date is a regular payment date with respect to the start date of the loan and makes the appropriate calculation.
The new functions address what we think are several complications in the EXCEL functions. First, EXCEL requires that you adjust the interest rate to reflect the number of periods. In the new functions, we ask for the annual rate of interest and then we automatically adjust for the number of payments per year. The new functions support the following payments per year:
1 – annually
2 – semi-annually
3 – quarterly
4 – every 4 months
6 – every 2 months
12 – monthly
13 – every 4 weeks
24 – semi-monthly
26 – every 2 weeks
52 – every week
365 – every day
Second, EXCEL has this very confusing thing going on with signs of both the input and the output for the PMT function. In the new LPMT function, we expect the loan amount, the balloon payment (if there is one), and the value returned by the function to all have the same sign.
Finally, the EXCEL CUMPRINC and CUMIPMT functions do not permit the entry of a future value, which means that any loan that has a balloon payment at the end cannot have the cumulative interest calculated correctly in EXCEL. Of course, as have documented previously, there are big problems with accuracy in the EXCEL CUMPRINC and CUMIPMT function prior to EXCEL 2010, though there are still some problems in 2010. We have also structured the CUMLIPMT and CUMLPPMT such that the total interest on the loan will always be equal to the sum of the payment amount minus the amount of the loan plus the balloon payment.
Since the new functions use dates (we want to know the loan start date and the first payment date) we felt that it would very helpful to have functions that could tell you when the next payment date is on a loan. The NPD (Next Payment Date) function will tell you the first payment date on a loan that is greater than the date that you supply to the function. The NPNO (Next Payment Number) function, will calculate the next payment number if you are more interested in the payment number than the payment date. We have also provided a PPD (Previous Payment Date) function and a PPNO (Previous Payment Number) function.
There are 4 functions that address the requirements for Rule of 78 accounting. (If you don’t know what Rule of 78 accounting is, you can follow this wikipedia link or just go to the next paragraph). The R78PAYOFF function calculates the payoff amount for a loan that is being paid off early using Rule of 78 accounting. The R78REBATE function calculates the rebate amount and R78IPMT and R78PPMT calculate the interest and principal portion of the payment amount for a specified period.
The TOTALINT function calculates the total interest on a loan with regular periodic payments and the PERIODRATE functions converts an interest rate from one compounding period to another
There are 2 new bond scalar functions, also dealing with odd periods. The ODDFINT function calculates the accrued interest for a bond that has an odd first period. One would think that the ACCRINT function in EXCEL would let you do this, but it just doesn’t conform to industry practice (which is a polite way of saying that I cannot really figure out what it is doing). ODDFINT only calculates interest in the odd first period, since all other periods have regular coupon payments.
ODDLINT calculates the accrued interest in the last coupon period for a bond with an odd last coupon period. Both functions also permit you to calculate the coupon amount for the respective odd periods.
The new table-valued functions are:
1. AMORTSCHED – creates an amortization schedule for loan with an odd first period
2. PMTSCHED – creates an amortization schedule for a loan regular periodic payments
The AMORTSCHED function goes hand-in-hand with the LPMT function. Their inputs are the same. While it is possible to create an amortization schedule using the LPMT, LPPMT, LIPMT, and PV functions, it is far more efficient in terms of database resources to use the table-value function and it is less computationally intensive as each row can be calculated from the preceding row.
The PMTSCHED also creates an amortization schedule, but does not accommodate odd periods. It also uses the payment amount as an input into the function, rather than interest rate, mostly because we think that the payment amount is going to be way more readily available than the rate. The PMTSCHED function amortizes by enforcing the relationship that the total interest on the loan or lease is equal to the sum of the pmts less the loan amount plus the future value of the loan. Because we enforce this rule (and EXCEL does not), the amortization schedule may be different than what you would be able to generate in EXCEL.
We have also made improvements to the root-finding algorithm used in IRR and IRR_q, so that it will resolve in more cases, reducing the likelihood that it will return a NULL. All that stuff is done under the covers, so there has been no change to the calling structure of the function.
We hope that you find these new functions useful. They were quite interesting to implement, as they posed some unique mathematical challenges and data processing challenges because we wanted to be able to process tens of thousands of rows (or more) in a single T-SQL and still have reasonable performance (a few seconds), We think that we have achieved that.
As always, we appreciate your feedback.