3 Replies Latest reply: Aug 30, 2015 11:05 PM by Mike Fitzgerald RSS

    Qlik scripting NPV solution

      Here's a tricky one...

       

      Using this equation:

       

      RangeSum(NPV(Discount, -Payments + Income, Investment)


      Say I had:

       

      RangSum ( NPV ( 0.08, ( -X + 140000 ) , 50000 )  = 85000

       

      where the payments and income were generated over 12 years......

       

      Can I solve this equation for X ?

       

      I would prefer to solve it in the script using inputs from a single line table like this.

       

      Load * Inline

      [

      NPV,Discount,Income,LifeOfLoan

      85000,0.08,140000,12

      ];

       

      Any recommendations?

       

      Mike

        • Re: Qlik scripting NPV solution
          arjun rao

          Hi Can you attac the sample qvw with th data. Thank you.

          • Re: Qlik scripting NPV solution
            Maxim Senin

            Hi Mike,

             

            Not sure I understand your purpose since QV takes only two parameters for NPV - discount rate and cash flow for all the preriods.

            The simple present value is calculated as: Cash Flow / ((1 + Discount Rate) ^ No. of periods), so if you are looking for cash flow you just need to multiply your NPV by ((1 + Discount Rate) ^ No. of periods).

             

            Best regards,

            Maxim

              • Re: Qlik scripting NPV solution

                Thanks for the reply Maxim,

                 

                You have me thinking that I may have overthought this one.

                 

                The thing is I know the incoming part of the cash flow (Benefits) but not the payments. So according to your rationale I  thought that would be:

                 

                Benefits - Payments = NPV * ((1 + Discount Rate) ^ No. of periods)

                Therefore....

                Payments = NPV * ((1 + Discount Rate) ^ No. of periods) + Benefits

                 

                Using this Im getting a payments figure far in excess of my benefits so Im not sure this is correct. Note that I am first creating a table that discount the year on year benefits and summing them to get the properly discounted benefit value. Your logic makes sense, but can you see where I might go wrong with my approach?

                 

                Thanks Mike