Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

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

Tags (1)
3 Replies
qlikviewwizard
Honored Contributor II

Re: Qlik scripting NPV solution

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

Partner
Partner

Re: Qlik scripting NPV solution

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

Not applicable

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