Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate IPMT in QV

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

6 Replies
petter
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

2015-05-11 #2.PNG

petter
Partner - Champion III
Partner - Champion III

Did you find any use of my solution?

kkmoraes
Contributor III
Contributor III

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
Contributor
Contributor

Hi Petter,

 

Do you have the formula also for CUMIPMT ?

 

Thank you,

Daniel