none
T-SQl query for Excel Goal Seek function

    Question

  • Hi,

    I have  a requirement to write a t-sql query to mimic the logic of excel goal seek function. In Excel for that particular cell we have a formula through which we are making that cell to 0. The formula is as below.

    =NPV(D7/12,D10:D481)

    D7:IRR(Internal Rate Return)

    12:No Of months in a year

    D10:D481:The values present in the cell.

    Through my sql code i am able to get the NPV(Net Present Value). I need to find out the value of D7 which makes D8 to 0 through goal seek function.Meaning basing on the value of D7 the cell will be approximately equal to 0.

    Any help is appreciated.


    • Edited by CoolDbGuy Saturday, July 20, 2013 7:17 AM
    Saturday, July 20, 2013 7:16 AM

Answers

  • Google my name and NPV. I have this in my books. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, July 20, 2013 8:08 PM

All replies

  • Post the table DDL, sample data script and the code. Assistance will follow quick.  Thanks.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration



    Saturday, July 20, 2013 4:22 PM
  • Google my name and NPV. I have this in my books. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, July 20, 2013 8:08 PM