In my earlier article, I implemented a lot of excel financial functions including PMT function using CLR and I found CLR implementation is the effective way to implement excel functions in SQL Server. However you can develop the same functionality in SQL using User Defined Function as well.
Given below is the script of PMT financial function in SQL Server with examples. Basically PMT function not only calculates the future value of any investment on an interest rate, it also calculates the payments for a loan.
CREATE FUNCTION UDF_PMT (@InterestRate NUMERIC(18,8), --Rate is the interest rate per period. @Nper INT, --Nper is the total number of payment --periods in an annuity. @Pv NUMERIC(18,4), --Pv is the present value, or the --lump-sum amount that a series of --future payments is worth right now. --If pv is omitted, it is assumed to be --0 (zero). PV must be entered as a --negative number. @Fv NUMERIC(18,4), --Fv is the future value, or the --lump-sum amount that a series of --future payments is worth right now. --If pv is omitted, it is assumed to --be 0 (zero). PV must be entered as a --negative number. @Type BIT --Type is the number 0 or 1 and --indicates when payments are due. --If type is omitted, it is assumed --to be 0 which represents at the end --of the period. --If payments are due at the beginning --of the period, type should be 1. ) RETURNS NUMERIC(18,2) --float AS BEGIN DECLARE @Value NUMERIC(18,2) SELECT @Value = Case WHEN @Type=0 THEN Convert(float,@InterestRate / 100) /(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1) * -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper) +@Fv) WHEN @Type=1 THEN Convert(float,@InterestRate / 100) / (Power(Convert(float,(1 + @InterestRate / 100)),@Nper)-1) * -(@Pv*Power(Convert(float,(1 + @InterestRate / 100)),@Nper) +@Fv) /(1 + Convert(float,(@InterestRate / 100))) END RETURN @Value END GO SELECT dbo.UDF_PMT(0.625,24,5000,0,0) --OUTPUT in SQL --($225.00) --Equivalent function in excel --=PMT(7.5%/12, 2*12, 5000, 0, 0) --OUTPUT in Excel --($225.00) GO SELECT dbo.UDF_PMT(0.11538461,208,8000,0,1) --OUTPUT in SQL --($43.23) --Equivalent function in excel --=PMT(6%/52, 4*52, 8000, 0, 0) --OUTPUT in Excel --($43.23) GO SELECT dbo.UDF_PMT(5.25,10,6500,0,0) --OUTPUT in SQL --($852.03) --Equivalent function in excel --=PMT(5.25%/1, 10*1, 6500, 0, 0) --OUTPUT in Excel --($852.03) GO SELECT dbo.UDF_PMT(0.666666667,36,5000,-1000,1) --OUTPUT in SQL --($131.14) --Equivalent function in excel --=PMT(8%/12, 3*12, 5000, -1000, 0) --OUTPUT in Excel --($131.14) GO
Appreciate your valuable feedback about this function.
Reference : Castle
Techonthenet
Reblogged this on Sutoprise Avenue, A SutoCom Source.
[…] SQL SERVER – Excel Financial functions – PMT […]