topic Re: Qlik scripting NPV solution in QlikView

Qlik scripting NPV solution

Sat, 29 Aug 2015 03:17:58 GMT

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

qlikviewwizard — Sun, 30 Aug 2015 12:45:59 GMT

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

Re: Qlik scripting NPV solution

maksim_senin — Mon, 31 Aug 2015 01:39:58 GMT

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

Mon, 31 Aug 2015 03:05:04 GMT

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