# FUNCTION FUTURE VALUE

### Question

• Dear friends,

I have prepared this financial function to count Future value:
CREATE FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL, @YEAR INT)
RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = @PV*(1+@RATE)^@YEAR
RETURN  @FV
END
GO

But there is a problem: Msg 402, Level 16, State 1, Procedure FUTURE_VALUE, Line 6
The data types decimal and int are incompatible in the '^' operator.
Is there some possibility to solve this problems?

Anna

Wednesday, October 16, 2013 8:44 PM

• Try the below:

```Alter FUNCTION dbo.FUTURE_VALUE (@PV MONEY, @RATE DECIMAL(10,2), @YEAR INT)
RETURNS DECIMAL(16,5)
AS
BEGIN
DECLARE @FV DECIMAL(16,5)
SET @FV =   @PV*power((cast(1 as decimal(10,5))+@RATE),@YEAR)
RETURN  @FV
END

Select dbo.FUTURE_VALUE(1000,0.05,3)```

Friday, October 18, 2013 9:15 AM
• Hi,

You should declare your @Rate as a decimal(5,2) to provide some precision, otherwise a @Rate like 0.05 will be truncated to 0. That's why you obtain 1000 instead of 1157.63

Here's the corrected code

```CREATE FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL (5,2), @YEAR INT) RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = @PV*POWER(1e0+@RATE,@YEAR)
RETURN @FV
END
Select dbo.FUTURE_VALUE(1000,0.05,3)```

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, October 18, 2013 5:44 PM

### All replies

• ^ is a bitwise XOR operator between two integer numbers, see here: http://technet.microsoft.com/en-us/library/ms190277.aspx

You probably need POWER function, see here: http://technet.microsoft.com/en-us/library/ms174276.aspx

Best regards,

Vedran Kesegic

Wednesday, October 16, 2013 8:53 PM
• ```CREATE FUNCTION FUTURE_VALUE
(
@PV MONEY ,
@RATE DECIMAL ,
@YEAR INT
)
RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = @PV * POWER(( 1 + @RATE ), @YEAR)
RETURN  @FV
END
```

Saeid Hasani, sqldevelop.wordpress.com

Wednesday, October 16, 2013 8:55 PM
• Try

```CREATE FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL, @YEAR INT)
RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = power(@PV*(1+@RATE),@YEAR)
RETURN  @FV
END
GO```

Many Thanks & Best Regards, Hua Min

Thursday, October 17, 2013 6:15 AM
• Sorry Vedran, but it don't work good. For ex., if PV=1000, RATE=0.05, Year=3, results must be 1157.625, but SQL Server gave me result 1000. : (
Thursday, October 17, 2013 8:32 PM
• Sorry, it is not good  formula.
Thursday, October 17, 2013 8:35 PM
• Change the input parameter  @RATE DECIMAL to
` @RATE DECIMAL(4,3)`

Thanks, hsbal

Thursday, October 17, 2013 8:59 PM
• Try

```CREATE FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL, @YEAR INT)
RETURNS MONEY
AS
BEGIN
DECLARE @FV decimal(16,3)
SET @FV = power(@PV*(1+@RATE),@YEAR)
RETURN  @FV
END
GO```

Many Thanks & Best Regards, Hua Min

Friday, October 18, 2013 1:34 AM
• I tried it and I get result  1157625000.00  instead of  1157.625. Incredible! : (
This financial function FV works in Excel without problems.

• Edited by Friday, October 18, 2013 8:59 AM
Friday, October 18, 2013 8:58 AM
• Try the below:

```Alter FUNCTION dbo.FUTURE_VALUE (@PV MONEY, @RATE DECIMAL(10,2), @YEAR INT)
RETURNS DECIMAL(16,5)
AS
BEGIN
DECLARE @FV DECIMAL(16,5)
SET @FV =   @PV*power((cast(1 as decimal(10,5))+@RATE),@YEAR)
RETURN  @FV
END

Select dbo.FUTURE_VALUE(1000,0.05,3)```

Friday, October 18, 2013 9:15 AM
• Incredible! It works good now! : -)
But I don't understant why. Difference is only in data type FV. Why "money" is not suitable?
And why number 1 must be changed to decimal?

Thanks a lot,
Anna

• Edited by Friday, October 18, 2013 11:14 AM
Friday, October 18, 2013 10:58 AM
• You can use Money. But the problem with the Cast-ing of 1. It takes as integer value, there by missing the decimal values.

Try the below:

```Alter FUNCTION dbo.FUTURE_VALUE (@PV MONEY, @RATE DECIMAL(10,2), @YEAR INT)
RETURNS money
AS
BEGIN
DECLARE @FV money
SET @FV =   @PV*power((cast(1 as decimal(10,5))+@RATE),@YEAR)
RETURN  @FV
END
Go
Select dbo.FUTURE_VALUE(1000,0.05,3)```

Friday, October 18, 2013 11:39 AM
• Incredible! I used to set up: cast(1 as decimal(10,2)), cast(1 as decimal(10,3)), cast(1 as decimal(10,1)), step by step and only cast(1 as decimal(10,5)) works optimally. I don't know why!?

Anna

Friday, October 18, 2013 11:56 AM
• Well, 1/3 is 0.33, 0.333, 0.3, 0.33333 in those decimal representations.

Truncation & rounding occurs in the calculations, hence the results will be different.

You can increase scale for more accuracy if needed, however, you can never get 1/3 exactly.

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Friday, October 18, 2013 12:03 PM
• By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale.

To get more precision, you need to specify the Precision and scale explicitly. May be you can check Cast(1 as decimal(10,6)) which will be accurate as your expectation.

Friday, October 18, 2013 12:09 PM
• Hi Anna,

MONEY is perfectly suitable for the end result, but not for the exponentiation (power) operation which is based on float numbers. By declaring 1 as a float constant instead of an integer one (1e0 instead of plain 1), you'll get best precision.

```CREATE FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL, @YEAR INT) RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = @PV*(1e0+@RATE)^@YEAR
RETURN @FV
END
GO```

For example, let's compute cubic root of 2 = 2^(1/3) ≈ 1.25992

SELECT POWER (2, 1/3) gives 1 (!)

But POWER(2e0, 1e0/3) gives proper result

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, October 18, 2013 1:09 PM
• Hi Anna,

MONEY is perfectly suitable for the end result, but not for the exponentiation (power) operation which is based on float numbers. By declaring 1 as a float constant instead of an integer one (1e0 instead of plain 1), you'll get best precision.

```CREATE FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL, @YEAR INT) RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = @PV*(1e0+@RATE)^@YEAR
RETURN @FV
END
GO```

For example, let's compute cubic root of 2 = 2^(1/3) ≈ 1.25992

SELECT POWER (2, 1/3) gives 1 (!)

But POWER(2e0, 1e0/3) gives proper result

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi Sebastian,

Unless you give the input parameter with precision and scale, I do no think it will work as expected. The below code is returning 1000.00 not 1157.625. Please correct me if am wrong. By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale.

```Alter FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL, @YEAR INT) RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = @PV*power((cast(1 as float)+@RATE),@YEAR)--@PV*(1e0+@RATE)^@YEAR
RETURN @FV
END
Go
Select dbo.FUTURE_VALUE(1000,0.05,3)```

Friday, October 18, 2013 1:17 PM
• Sorry, this problem is not as easy as you mean...
Friday, October 18, 2013 1:49 PM
• I used this script, but with this result:

Msg 402, Level 16, State 1, Procedure FUTURE_VALUE1, Line 5

The data types float and int are incompatible in the '^' operator.

Friday, October 18, 2013 1:54 PM
• OK, but why did you use: @RATE DECIMAL(10,2) and cast(1 as decimal(10,5))
I changed
@RATE to DECIMAL(10,5) - result is the same: 1157.63
But if I changed cast(1 as decimal(10,2)) - result is wrong: 1160.00

Friday, October 18, 2013 2:18 PM
• Hi,

You should declare your @Rate as a decimal(5,2) to provide some precision, otherwise a @Rate like 0.05 will be truncated to 0. That's why you obtain 1000 instead of 1157.63

Here's the corrected code

```CREATE FUNCTION FUTURE_VALUE (@PV MONEY, @RATE DECIMAL (5,2), @YEAR INT) RETURNS MONEY
AS
BEGIN
DECLARE @FV MONEY
SET @FV = @PV*POWER(1e0+@RATE,@YEAR)
RETURN @FV
END
Select dbo.FUTURE_VALUE(1000,0.05,3)```

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Friday, October 18, 2013 5:44 PM
• Thanks Sebastian, it works good. ( :
Anna

• Edited by Sunday, October 20, 2013 8:03 AM
Sunday, October 20, 2013 7:35 AM