Qlik Sense Financial Functions – Example & Syntax

FREE Online Courses: Knowledge Awaits – Click for Free Access!

1. Objective

In our last Qlik Sense Tutorial, we discussed Table Functions. Today, we will see Qlik Sense Financial Functions. We use the financial functions in a Qlik Sense data load script when we deal with data related to finance like to calculate interest rates, payments, profits, loss etc. In the parameters and results of the financial functions which we will discuss below will represent the money or cash paid out (debited) as negative values and the amount or cash received i.e. credited money as positive values.

So, let’s start Qlik Sense Financial Functions.

Qlik Sense Financial Functions

Qlik Sense Financial Functions – Example & Syntax

Have a look at Qlik Sense advantages and disadvantages

2. Qlik Sense Financial Functions

i. BlackAndSchole() function

The Black and schole function evaluates parameters used in the function according to the Black and Schole formula which is so formulated that it calculates the variation in financial instruments like stocks, over a certain period of time. The result is calculated using six variables which are the strike, time left, underlying price, volatility, risk-free rate, and type. This function returns a numeric value.

The syntax of BlackAndSchole Function:

BlackAndSchole(strike , time_left , underlying_price , vol , risk_free_rate , type)

The parameters or variables are described below, and they are all in the context of stock prices. Also, certain things must be kept in mind that is the values of the strike, time_left, and underlying_price must be greater than zero and the values of vol and risk_free_rate must be either less than zero or greater than zero.

Do you know about Qlik Sense Mapping Function

  • strike It is the future purchase price of the stock.
  • time_left It is the number of time periods remaining in stock validity.
  • underlying_price It is the current value of the stock.
  • vol This is for Volatility of the stock price expressed as a percentage in decimal form, per time period.
  • risk_free_rate Is represents the risk-free rate expressed as a percentage in decimal form, per time period.
  • call_or_put There are two type of options that you can choose from. The first is ‘c’, ‘call’ or any non-zero numeric value for call options and second is ‘p’, ‘put’ or 0 for put options.

For example,

BlackAndSchole(130, 4, 68.5, 0.4, 0.04, 'call')

Returns the value 11.245 which is the price for the option given to buy a share. If we translate this formula, we will find out that the current price of that share is 68.5 and it will increase to 130 in 4 years’ time. The volatility is at 40% per year and the risk-free rate of interest is taken as 4%.

ii. FV() Function in Qlik Sense

The FV stands for future value and this function returns the future value of an investment based on payments made as monthly installments, for a fixed period of time, on a fixed rate of interest.

The syntax of Qlik Sense FV Function:

FV(rate, nper, pmt [ ,pv [ , type ] ])

Where the rate is the rate of interest per period.

nper is the total number of installments or payment periods fixed annually.

pmt is the amount due for an installment for each payment period. This value always a negative number because the money is debited.

Let’s discuss Qlik Sense Color Functions

pv is the present value is the total or sum of the amount that is yet to be paid. If you do not mention this in the expression, then the value is taken as 0.

Type is type of payment cycle i.e. 0 is used if the payments are to be made at the end of the month or 1 if the payments are made at the start of the month. By default, it is set on 0.

For example,

FV(0.005,36,-20)

Returns $786.72, as the code given above asks for the future value or total invested money when 36 monthly installments are to be made worth $20 for an item purchased. The rate of interest is 6% per annum. The payments are due at the end of every month.

iii. nPer() function

This function returns the number of months or payments fixed to pay a decided amount annually.

The syntax of Qlik Sense nPer Function:

nPer(rate, pmt, pv [ ,fv [ , type ] ])

Where rate is the rate of interest on the total amount.

pmt is the is the payment made in each period or monthly (if so). The money that is given is always represented as a negative value.

pv is the present value is the total amount which is yet to be paid. This value is set to 0 by default.

fv is the future value i.e. the cash balance left after the last payment has been made. The fv is set to be at 0 by default, you may change it accordingly.

type is set to be 0 if the payments are to be at the end of the month or 1 if they are to be made at the start of the month.

Let’s learn about Qlik Sense Box Plot

For example,

nPer(0.005,-20,0,800)

This expression will return the value 36.56 as the number of periods for payments that are required for the amount to equal $800 after the payment of the last bill. The instalment for each month is $20 at the interest rate of 6% per annum.

iv. Pmt() function

This function returns the amount of money to be paid in each instalment for a fixed number of period(nPer). This function always returns a negative value as money here is been given and not received.

The syntax of Qlik Sense pmt Function:

Pmt(rate, nper, pv [ ,fv [ , type ] ])

Where, rate is the rate of interest on the total amount.

nper is the total number of periods fixed to pay the total amount in.

pv is the present value is the total amount which is yet to be paid. This value is set to 0 by default.

fv is the future value i.e. the cash balance left after the last payment has been made. The fv is set to be at 0 by default, you may change it accordingly.

type is set to be 0 if the payments are to be at the end of the month or 1 if they are to be made at the start of the month.

For instance,

nPer(0.005,36,0,790)

This expression will return -20, which is the amount to be paid as the instalment of each period for 36 months. The total amount must be, $790 and the rate of interest is 6%.

v. PV() function

This function returns the present value of the investment, i.e. the amount which is still be paid to make the complete payment.

Have a look at Qlik Sense Treemap visualization

The syntax of Qlik Sense PV Function:

PV(rate, nper, pmt [ ,fv [ , type ] ])

Where, rate is the rate of interest on the total amount.

nper is the total number of periods fixed to pay the total amount in.

pmt is the is the payment made in each period or monthly (if so). The money that is given is always represented as a negative value.

fv is the future value i.e. the cash balance left after the last payment has been made. The fv is set to be at 0 by default, you may change it accordingly.

type is set to be 0 if the payments are to be at the end of the month or 1 if they are to be made at the start of the month.

For example,

PV(0.07/12,12*5,-100,0,0)

Returns $5,050.20 as the present value of debt left when you have to pay $100 at the end of each month during a period of 5 years, with the rate of interest being 7%.

vi. Rate() function

The rate() function returns the rate of interest upon which an amount of payment is decided to be paid in a fixed amount of time. The format for this function is fixed as a value having two decimal points and a % sign.

Let’s revise Qlik Sense Pie Chart

The syntax of Qlik Sense Rate Function:

Rate(nper, pmt , pv [ ,fv [ , type ] ])

Where, nper is the total number of periods fixed to pay the total amount in.

pmt is the is the payment made in each period or monthly (if so). The money that is given is always represented as a negative value.

pv is the present value is the total amount which is yet to be paid. This value is set to 0 by default.

fv is the future value i.e. the cash balance left after the last payment has been made. The fv is set to be at 0 by default, you may change it accordingly.

type is set to be 0 if the payments are to be at the end of the month or 1 if they are to be made at the start of the month.

For example,

Rate(60,-300,10000)

This expression will return 2.00% as the rate of interest of a 5-year loan of $10,000 having monthly installments worth $300.

So, this was all in Qlik Sense Financial Functions. Hope you like our explanation.

3. Conclusion

Hence, we discussed the Qlik Sense Financial Functions which we use in Qlik Sense apps to deal with data records related to finance and investments. Also, we saw the example and syntax of each Financial Functions in Qlik Sense.

Still, if you have any query related to Qlik Sense Financial Functions, ask in the comment tab.

See also – 

Qlik Sense Null Functions

Reference for Qlik Sense

Did you like this article? If Yes, please give DataFlair 5 Stars on Google

follow dataflair on YouTube

1 Response

  1. Sai k says:

    Dear Author,

    Do we have any alternative way to implement excel CUMPRINC ( ) In qliksense for cumulative principle calculation of a loan to find out between two periods

    Kindly suggest to achieve this.

Leave a Reply

Your email address will not be published. Required fields are marked *