none
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?
    Thanks for advices,

    Anna

    Wednesday, October 16, 2013 8:44 PM

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.

    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

    Download Books Online for SQL Server 2012

    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 Anna1313 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)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    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 Anna1313 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)

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    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!?
    Thanks for answer,

    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.

    BOL: Precision, Scale, and Length (Transact-SQL)


    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
    Moderator
  • 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/en-us/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.

    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)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    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 Anna1313 Sunday, October 20, 2013 8:03 AM
    Sunday, October 20, 2013 7:35 AM