topic Re: How to Calculate NPV in QlikView

How to Calculate NPV

Thu, 26 Sep 2013 16:59:36 GMT

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

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

Re: How to Calculate NPV

Clever_Anjos — Thu, 26 Sep 2013 17:32:49 GMT

You dont need SQL,

QlikView implements NPV() function

Re: How to Calculate NPV

Thu, 26 Sep 2013 17:52:56 GMT

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

Re: How to Calculate NPV

Clever_Anjos — Thu, 26 Sep 2013 18:37:40 GMT

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;

Re: How to Calculate NPV

Thu, 26 Sep 2013 19:31:28 GMT

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

For example

Project	interest	investment	Cash Flows
1234	0.08	1,000,000.00	200,000.00
1234	0.00	0.00	200,000.00
1234	0.00	0.00	200,000.00
1234	0.00	0.00	200,000.00
1234	0.00	0.00	200,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

Re: How to Calculate NPV

Clever_Anjos — Thu, 26 Sep 2013 21:30:50 GMT

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

Re: How to Calculate NPV

Fri, 27 Sep 2013 12:35:40 GMT

Thank you.

Re: How to Calculate NPV

Fri, 27 Sep 2013 17:13:52 GMT

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

same data

Project	interest	investment	Cash Flows
1234	0.08	1,000,000.00	200,000.00
1234	0.00	0.00	200,000.00
1234	0.00	0.00	200,000.00
1234	0.00	0.00	200,000.00
1234	0.00	0.00	200,000.00

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

Thanks again for all your help

Re: How to Calculate NPV

Clever_Anjos — Fri, 27 Sep 2013 17:28:01 GMT

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 )

Re: How to Calculate NPV

Fri, 27 Sep 2013 17:34:11 GMT

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

Re: How to Calculate NPV

Clever_Anjos — Fri, 27 Sep 2013 17:45:24 GMT

Sorry, never worked with this function