Feeds:
Posts
Comments

## SQL SERVER – Present value of Annuity(PV)

In my last article, I shared how to calculate Future Value of Annuity(FV) function in SQL Server and as promised in this article, today I will be sharing about Present Value of Annuity(PV) function in SQL Server, another important financial function.

Given below is the script of Present Value of Annuity function in SQL with examples :

```CREATE FUNCTION UDF_PresentValue
(@InterestRate NUMERIC(18,8), --Rate is the interest rate per period.
@Nper          INT,           --Nper is the total number of payment
--periods in an annuity.
@Pmt           NUMERIC(18,4), --Pmt is the payment made each period;
--it cannot change over the life
--of the annuity.PaymentValue 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 Fv is omitted,
--it is assumed to be 0 (zero).
--FV 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 @Pmt*(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
-1) /(((@InterestRate / 100))
* Power((Convert(float,1 + @InterestRate / 100)),@Nper))
+ @Fv *
Power(Convert(float,(1 + @InterestRate / 100)),@Nper)

WHEN @Type=1
THEN @Pmt*(Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
-1) /(((@InterestRate / 100))
* Power((Convert(float,1 + @InterestRate / 100)),@Nper))
* (1 + @InterestRate / 100)
+ @Fv
* Power(Convert(float,(1 + @InterestRate / 100)),@Nper)
END
RETURN @Value*-1
END
GO
--Syntax
--dbo.UDF_PresentValue(@InterestRate,@Nper,@Pmt,@Fv ,@Type)
--You can find the same syntax in excel.
--PV(Rate,Nper,Pmt,Fv ,Type)

SELECT dbo.UDF_PresentValue(0.625,24,250,0,0)
---5555.61						--SQL OUTPUT
--=PV(7.5%/12, 2*12, 250,0, 0)	--EXCEL FORMULA
--(\$5,555.61)					--EXCEL OUTPUT
GO

SELECT dbo.UDF_PresentValue(0.1153846,208,50,0,1)
---9252.07						--SQL OUTPUT
--==PV(6%/52, 4*52, 50, , 1)	--EXCEL FORMULA
--(\$9,252.07)					--EXCEL OUTPUT
GO
SELECT dbo.UDF_PresentValue(5.25,10,100,0,0)
---762.88						--SQL OUTPUT
--=PV(5.25%/1, 10*1, 100, , 0)	--EXCEL FORMULA
--(\$762.88)					    --EXCEL OUTPUT
GO
```

Appreciate your valuable feedback about this function.

Reference : Castle
Techonthenet

### 6 Responses

1. […] days ago, I developed two excel functions Future Value of Annuity and Present Value of Annuity in SQL Server as a User defined function and shared […]

2. very good! helped a lot

3. on March 27, 2014 at 1:46 pm | Reply Albert Fitzgerald

Hi Imran, This is great. How about if the payments are indexed?

4. […] SQL SERVER – Present value of Annuity(PV) Time money tables excel | tvmcalcs., Virtually finance textbook , , series tables multipliers easily calculate present future values . […]

5. This is very helpful!!

In SqlServer:
SELECT dbo.UDF_PresentValue(8.0/12,12,1970,0,0)

In Excel:
=PV(8%/12,12,-1970,0,0)

Both results return \$22,647

When I change the @Fv value to 456,300
=PV(8%/12,12,-1970,456300,0 = 398,683.14.

SELECT dbo.UDF_PresentValue(8.0/12,12,1970,456300,0) = -516819.33

Results no longer match.

Would you know how to correct the formula?