How Does Access calculate Present Value (PV)

[sqlhell]

I just spent waay too much time trying to figure out a SQL translation of Access and Excel's present value function. I'm posting the solution here to save somebody else some time hopefully.



I found a solution here: http://bytes.com/topic/access/answer...unction-access


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

[pkstormy]

Correct me if I'm wrong but this code looks like a SQL Server cursor or SQL Server stored procedure, not vba coding.

[ajetrumpet]

Not only that, but I'm wondering if this is the same ''SQL Hell'' that is a member of AWF. It certainly doesn't look like it from the elegance of the post!

[pkstormy]

Ha! Good one.