T-SQL Stored Procedure to minic Excel Formula RRS feed

  • Question

  • i am working on a task which requires me to build an sql query that will simulate a formula from excel using a particular start amount that will be reducing till maturity date.

    so can someone help me with the script that will simulate a formula for a particular date and apply it the same formula throughout the subsequent period till end date.

    below is the excel formula,

    =XNPV($C$51, Annuity!G37:$NB$37, Annuity!G41:$NB$41)

    i have derived sheet c51. which runs by this query

    select @rt= wct.XIRR(cf_amt, cf_date, NULL)
      FROM (select cf_amt, cf_date from @IRRtab) n(cf_amt, cf_date)
      declare  @rtt as float = (select cast(@rt as float))
      return @rt 

    sheet G37 is = 20,504.63 from excel using this formula

    =IF(G41 > 0, Annuity!$C61, 0)

    and G41 is the date.

    Friday, October 25, 2019 4:08 PM