How to calculate IPMT in QV

[#1]

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]

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]

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]

[petter]

Did you find any use of my solution?

[kkmoraes]

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]

Hi Petter,

Do you have the formula also for CUMIPMT ?

Thank you,

Daniel