Answered by:
FUNCTION FUTURE VALUE

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
GOBut 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?
Thanks for advices,Anna
Question
Answers

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)
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Marked as answer by Allen Li  MSFTMicrosoft contingent staff, Moderator Thursday, October 24, 2013 7:29 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
 Marked as answer by Allen Li  MSFTMicrosoft contingent staff, Moderator Thursday, October 24, 2013 7:29 AM
All replies

^ is a bitwise XOR operator between two integer numbers, see here: http://technet.microsoft.com/enus/library/ms190277.aspx
You probably need POWER function, see here: http://technet.microsoft.com/enus/library/ms174276.aspx
Best regards,
Vedran Kesegic
 Edited by Vedran Kesegic Wednesday, October 16, 2013 8:53 PM
 Proposed as answer by Naomi NModerator Friday, October 18, 2013 1:41 AM

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
Download Books Online for SQL Server 2012 Proposed as answer by Kalman TothModerator Thursday, October 17, 2013 1:25 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)
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Marked as answer by Allen Li  MSFTMicrosoft contingent staff, Moderator Thursday, October 24, 2013 7:29 AM


You can use Money. But the problem with the Casting 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)
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


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.
BOL: Precision, Scale, and Length (TransactSQL)
Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70461 Bootcamp: Querying Microsoft SQL Server 2012
 Edited by Kalman TothModerator Friday, October 18, 2013 2:24 PM

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale.
BOL: http://msdn.microsoft.com/enus/library/ms187746.aspx
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.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

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 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)
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
 Edited by Latheesh NKMicrosoft community contributor Friday, October 18, 2013 1: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
 Marked as answer by Allen Li  MSFTMicrosoft contingent staff, Moderator Thursday, October 24, 2013 7:29 AM
