|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to create and test user-defined functions to calculate present value and future value.
-- SQL present value calculation - SQL future value calculation
-- SQL user-defined function - UDF - SQL scalar function
USE AdventureWorks;
GO
CREATE FUNCTION fnPresentValue
(@FutureValue MONEY,
@InterestRatePercent DECIMAL,
@Years INT)
RETURNS MONEY
AS
BEGIN
DECLARE @Value MONEY
SELECT @Value = @FutureValue * (1 / Power((1 + @InterestRatePercent / 100.0),
@Years))
RETURN @Value
END
GO
-- Test UDF
-- What is the present value of 500,000 10 years from now at 5% interest
SELECT dbo.fnPresentValue (500000, 5, 10)
GO
-- 306956.5564
-- 306956 will grow to 500,000 in ten years provided no tax payments
CREATE FUNCTION fnFutureValue
(@PresentValue MONEY,
@InterestRatePercent DECIMAL,
@Years INT)
RETURNS MONEY
AS
BEGIN
DECLARE @Value MONEY
SELECT @Value = @PresentValue * Power((1 + @InterestRatePercent / 100.0),
@Years)
RETURN @Value
END
GO
-- Test UDF
-- What is the future value of one million 10 years from now at 5% interest rate
SELECT dbo.fnFutureValue (1000000, 5, 10)
-- 1628895.00
-- 1 million will grow with a factor of 1.628, not 1.5, due to compund interest
-- No tax payments are assumed |
