Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

How to Calculate NPV


Hi,

I know this shouldn't be that difficult, but somehow, it is.

Here's the issue. I need to learn how to calculate a simple Net Prsent Value in Qlikview.

Total Costs - 1.279 mln

Cash Flows -

200k

233k

233k

233k

233k

rate - 20%

The answer should be a negative NPV of -609.7k.

I am not an expert in sql, so I am a bit confused.

Thanks in advance

Tom

Tags (5)
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: How to Calculate NPV

=rangesum(NPV(0.08, -[Cash Flows]),sum(investment))

View solution in original post

10 Replies
Highlighted
Employee
Employee

Re: How to Calculate NPV

You dont need SQL,

QlikView implements NPV() function

Highlighted
Not applicable

Re: How to Calculate NPV

ok.

But I can't seem to get the formula to work.

Does anyone have an existing NPV formula? I would like to be able to review it and see how it should look.

Thanks

Tom

Highlighted
Employee
Employee

Re: How to Calculate NPV

npv (rate, expression)

returns the aggregated net present value of an investment based on a discount rate and a series of future payments

(negative values) and incomes (positive values) represented by the numbers in expression iterated over

a number of records as defined by a group by clause. The result has a default number format of money. Rate

is the interest rate per period. The payments and incomes are assumed to occur at the end of each period.Text

values, null values and missing values are disregarded.

Example:

Load Year, npv(0.05, Payments) as PValue from abc.csv group by Year;

Highlighted
Not applicable

Re: How to Calculate NPV

ok thanks. I get that, but my difficulty is in creating the formula in Qlikview

For example

ProjectinterestinvestmentCash Flows
12340.081,000,000.00200,000.00
12340.000.00200,000.00
12340.000.00200,000.00
12340.000.00200,000.00
12340.000.00200,000.00

The above data in excel would calculate an NPV of -201,457.99

My question is, how does this data above translate into the formula below that is in qlikview.

I thought I had it, but it's not working.

NPV ([Total[<fld{,fld}>]]discount_rate,value)

Thanks
Tom

Highlighted
Employee
Employee

Re: How to Calculate NPV

=rangesum(NPV(0.08, -[Cash Flows]),sum(investment))

View solution in original post

Highlighted
Not applicable

Re: How to Calculate NPV

Thank you.

Highlighted
Not applicable

Re: How to Calculate NPV

Sorry to be a pest, but how about an IRR calculation?

same data

ProjectinterestinvestmentCash Flows
12340.081,000,000.00200,000.00
12340.000.00200,000.00
12340.000.00200,000.00
12340.000.00200,000.00
12340.000.00200,000.00

rangesum(IRR(-[Cash Flows]),sum([Capital 2014 Capital]))

Thanks again for all your help

Highlighted
Employee
Employee

Re: How to Calculate NPV

Which field is [Capital 2014 Capital])?

irr([[set_expression] total [<fld {,fld}>] ] expression)

returns the aggregated internal rate of return for a series of cash flows represented by the numbers in expression

iterated over the chart dimension(s). These cash flows do not have to be even, as they would be for an

annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal

rate of return is the interest rate received for an investment consisting of payments (negative values) and

income (positive values) that occur at regular periods. The function needs at least one positive and one negative

value to calculate. Text values, null values and missing values are disregarded. See also the rangeirr(

value { ,value} ) (page 313) function.

Expression must not contain aggregation functions, unless these inner aggregations contain the total qualifier.

For more advanced nested aggregations, please use the Advanced Aggregation (page 291) function in

combination with calculated dimensions, see Add calculated dimension... (page 614).

If the word total occurs before expression the calculation will be made over all possible values given the current

selections but disregarding the chart dimension variables.

The total qualifier may be followed by a list of one or more field names within angle brackets. These field

names should be a subset of the chart dimension variables. In this case the calculation will be made disregarding

all chart dimension variables except those listed, i.e. one value will be returned for each combination

of field values in the listed dimension fields. Also fields which are not currently a dimension in a

chart may be included in the list. This may be useful in the case of group dimensions, where the dimension

fields are not fixed. Listing all of the variables in the group causes the function to work when the cycle or

drill-down level changes.

Examples:

irr( Payments )

irr( X*Y/3 )

irr( total Payments )

irr( total <Grp> Payments )

Highlighted
Not applicable

Re: How to Calculate NPV

Sorry for the confusion.

The Capital number is the investment number.

Below is what my formula is now, but it's not correct.

(IRR(-[Cash Flows]),sum([Investment]))

I've also tried

rangesum(IRR(-[Cash Flows]),sum([Investment])) which is also wrong.

Thanks

Tom