PERIODRATE
Updated 2023-10-11 13:36:35.117000
Syntax
SELECT [westclintech].[wct].[PERIODRATE] (
< @Nominal , float ,>
,< @ComPeriod , float ,>
,< @PayPeriod , float ,>)
Description
Use the scalar function PERIODRATE to Calculate the nominal rate for a loan or other financial instrument when the compounding period of the quoted rate and the compounding period for the calculation of the loan are different.
Arguments
@ComPeriod
the compounding period for @Nominal. @ComPeriod is an expression of type float or of a type that can be implicitly converted to float.
@PayPeriod
the compounding period the @Nominal is to be converted into. @PayPeriod is an expression of type float or of a type that can be implicitly converted to float.
@PV
the nominal or stated rate of interest. @Nominal is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
@ComPeriod must be greater than or equal to 1,
@PayPeriod must be greater than or equal to 1.
@Payperiod must be greater than @ComPeriod.
Examples
Given a 7.0% semi-annually compounded rate, convert it into a monthly-compounded rate.
SELECT wct.PERIODRATE(.07, --nominal rate of interest
2, --semi-annual compounding
12 --monthly compounding
) as [Monthly Rate]
This produces the following result.
{"columns":[{"field":"Monthly Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Monthly Rate":"0.0690004739713013"}]}
In this example, we look at a variety of different compounding period combinations.
SELECT n1,
ROUND([1], 6) as [1],
ROUND([2], 6) as [2],
ROUND([4], 6) as [4],
ROUND([6], 6) as [6],
ROUND([12], 6) as [12],
ROUND([13], 6) as [13],
ROUND([26], 6) as [26],
ROUND([52], 6) as [52],
ROUND([365], 6) as [365]
FROM ( SELECT n1.f as n1,
n2.f as n2,
wct.PERIODRATE(.06, n1.f, n2.f) as r1
FROM ( SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 4
UNION ALL
SELECT 6
UNION ALL
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 26
UNION ALL
SELECT 52
UNION ALL
SELECT 365) n1(f)
CROSS APPLY ( SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 4
UNION ALL
SELECT 6
UNION ALL
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 26
UNION ALL
SELECT 52
UNION ALL
SELECT 365) n2(f) ) d
PIVOT ( MIN(r1)
for n2 in ([1], [2], [4], [6], [12], [13], [26], [52], [365])) as P
This produces the following result.
n1 1 2 4 6 12 13 26 52 365
----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1 0.06 0.059126 0.058695 0.058553 0.058411 0.0584 0.058334 0.058302 0.058274
2 0.0609 0.06 0.059557 0.05941 0.059263 0.059252 0.059185 0.059151 0.059122
4 0.061364 0.06045 0.06 0.059851 0.059702 0.059691 0.059623 0.059589 0.059559
6 0.06152 0.060602 0.06015 0.06 0.059851 0.059839 0.059771 0.059736 0.059707
12 0.061678 0.060755 0.0603 0.06015 0.06 0.059988 0.059919 0.059885 0.059855
13 0.06169 0.060767 0.060312 0.060162 0.060012 0.06 0.059931 0.059896 0.059867
26 0.061763 0.060838 0.060382 0.060231 0.060081 0.060069 0.06 0.059965 0.059936
52 0.0618 0.060873 0.060417 0.060266 0.060115 0.060104 0.060035 0.06 0.05997
365 0.061831 0.060904 0.060447 0.060296 0.060145 0.060134 0.060064 0.06003 0.06
If you look at the first row, it shows the result of 6.0% nominal annual rate converted to an equivalent semi-annual compounded rate, a quarterly compounded rate, a bi-monthly compounded rate, etc.
If you look at the first column is shows a 6.0% rate compounded annually, semi-annually, quarterly, bi-monthly, monthly, etc.