Discussion board where members can get started with QlikView.
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
=rangesum(NPV(0.08, -[Cash Flows]),sum(investment))
You dont need SQL,
QlikView implements NPV() function
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
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;
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
=rangesum(NPV(0.08, -[Cash Flows]),sum(investment))
Thank you.
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
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 )
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