Feeds:
Posts

## 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
```

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. on March 14, 2013 at 9:06 pm | Reply Renato

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. on July 21, 2020 at 12:50 am | Reply dia gea

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?