469,963 Members | 1,242 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,963 developers. It's quick & easy.

PV function in Access

Does anyone know sql of the PV function in Access? (same function in
Excel)

In access the inputs are PV («rate», «nper», «pmt», «fv»,
«due») where rate = discount rate, nper = number of payments, pmt =
payment) other two are not required.

nper is allowed to be a number with decimals, not just an integer.

Seeking sql code which will replicate the above, including nper with
decimals allowed. Thanks.

Nov 20 '05 #1
4 3612
Assuming you mean SQL in JET, the expression service seems to be quite
happy to send PV off to VBA for evaluation. In other words one can
execute a query such as:
SELECT PV(0.08,12.768,PaymentAmount) FROM Table1
where PaymentAmount is a numeric field.

Nov 20 '05 #2
Thanks, but I do not mean sql in jet. SQL code in another sql
platform such as sql server. Or SQL in jet that would not use the
function pv but show the actual underlying code.

Nov 20 '05 #3
I think you will have to write your own in line function then, or
probably, better still, create a UDF in MS-SQL.
Perhaps, if you do a web search for Present Value you will find one
already created.

Nov 20 '05 #4
You could try this MS-SQL UDF which is something I cobbled together on
(this) Sunday morning BUT (trying to say this without offending)
If you can't write this yourself, can you apply it correctly in all
situations and should you?
I have tested it against the Excel function for !!!!!ONE!!!!! case; if
I were going to use it I would test it a few more thousand times.

ALTER FUNCTION dbo.PresentValue
(
@Rate smallmoney,
@Periods money,
@Payment money,
@FutureValue money = 0,
@Type int = 0
)
RETURNS money
AS
BEGIN
DECLARE @PresentValue money

/*
Deal with Nulls
*/
If @FutureValue IS NULL
SET @FutureValue = 0
IF @Type IS NULL
SET @Type = 0

/*
Type should be one or zero.
*/
IF @Type != 0 AND @Type != 1
SET @PresentValue = 0
ELSE
/*
This is just a port of the Excel function
without any shortcuts for Rate = 0 or FutureValue = 0,
or simplification.
*/
SET @PresentValue =
-(@Payment * (1 + @Rate * @Type)
* ((Power((1 + @Rate), @Periods) - 1) / @Rate)
+ @FutureValue) / Power((1 + @Rate), @Periods)
RETURN @PresentValue

END

Nov 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Martin Vorbrodt | last post: by
11 posts views Thread by Yelena Varshal via AccessMonster.com | last post: by
3 posts views Thread by william | last post: by
9 posts views Thread by CryptiqueGuy | last post: by
12 posts views Thread by Bryan Parkoff | last post: by
1 post views Thread by seanmatthewwalsh | last post: by
2 posts views Thread by Immortal Nephi | last post: by
7 posts views Thread by =?Utf-8?B?SmVycnkgQw==?= | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.