topic Re: How to calculate IPMT in QV in QlikView

How to calculate IPMT in QV

Mon, 11 May 2015 11:14:15 GMT

Hi all,

I'm converting a tool from excel to QV and I need calculate IPMT with all parameters.

QV knows only PMT, FV but not IPMT !!! So I need find some math equivalent to:

IPMT( rate, per, nper, pv, fv, type )

I really need your help because by work on the project is halted.

Thank you

petter — Mon, 11 May 2015 18:12:31 GMT

In QlikView IPMT can be calculated like this:

IPMT = pv*rt*(Pow(rt+1,Tn+1)-Pow(rt+1,n)) / ((rt+1)*(Pow(rt+1,Tn)-1))

pv = present value

rt = interest rate

Tn = number of periods

n = period of interest

petter — Mon, 11 May 2015 18:21:34 GMT

You can make your own user-defined function in a variable like this:

IPMT = '$4*$1*(Pow($1+1,$3+1)-Pow($1+1,$2)) / (($1+1)*(Pow($1+1,$3)-1)) ';

Then you can use the function in an expression anywhere like this:

$(IPMT(<rate>,<period>,<numberofperiods>,<presentvalue>))

$(IPMT(0.005,10,360,100000)

Which should give: 495,42957773604 which corresponds exactly with Excel's IPMT() function at least in Excel 2013 which I tested against ...

petter — Mon, 11 May 2015 18:55:17 GMT

petter — Mon, 16 Nov 2015 07:57:55 GMT

Did you find any use of my solution?

kkmoraes — Wed, 08 Jun 2016 18:10:42 GMT

Hi Petter,

Your explanation was great! thank you for it!

I am studying about Financial Functions because i'm needing to use something like PGTO function (excel) with Qlikview

You have any example about PMT function?

I am attaching an example of what I'm trying to do

Thank you!

dan27gan — Thu, 03 Sep 2020 14:33:07 GMT

Hi Petter,

Do you have the formula also for CUMIPMT ?

Thank you,

Daniel