How to create PPMT, IPMT, etc FInancial functions in MySQL

Hi MWVISA1,
Thanks for this response. I already have PMT but could you help me derive PPMT and IPMT from the code below;

>>>> Working PMT Function in MySQL
DELIMITER $$

DROP FUNCTION IF EXISTS `C245037_InterAct`.`PMT` $$
CREATE DEFINER=`C245037_biolgb`@`%` FUNCTION `PMT`(
      mPv double,
      mFv double,
      mNPer int,
      mApr float,
      mMonths int
     ) RETURNS double
BEGIN

  Declare mVal double;


/*
In excel it should look like this:
=PMT(A1/12,B2,-C3, D4,1)
A1 = 7.40%
B2 = 36 (Month)
C3 = 106344 (Financing)
D4 = 75960 (Rest value)
In excel the result = 1404

set Pv=106344;
set Fv=75960;
set NPer=36;
set Apr=.074;
set mMonths=12;

*/

select (mPV-mFV)/mNPER + (mPV+mFV-(mPV-mFV)/mNPER)/2*(mAPR/mMonths) into mVal;


return mVal;

END $$

DELIMITER ;

I am working on this by the way, just wanted to run through some tests before posting back to ensure I understand correctly the effects of payments at beginning of month versus end of month.

Okay, here we go ...

http://en.wikipedia.org/wiki/Mortgage_calculator

Above is usually a reference I use to remind myself of the mortgage payment calculation formula.

rate / (1 - (1+rate)^-Nper) * pv

Where rate is monthly rate; hence, APR / 12.  Therefore, the rate passed in should already be divided by 12 just like in Excel.  Nper is the number of payments, so it is the term of the loan multiplied by 12 (or other number of payments per year value if different than monthly).

This is the PMT() function at its basic level; however, Excel allows for two other factors to the calculated payment: future value and type.

Future value establishes amount of loan left after making all payments specified by Nper.  

Type specifies if loan is paid at beginning of period versus end.

These are both spelled out in the definition of PMT I linked to earlier; however, pointing out again as they alter the *pv* used in above formula.  

So :

P = pv - fv / (1+rate)^Nper

If type = 1, then :
P = P * (1-rate)

And updated formula :
rate / (1 - (1+rate)^-Nper) * P

Guess you can adjust the formula to handle the negative input to truly mimic Excel; however, tend to think of this in the positive.

From the above, we can also derive a future value formula.

(1+rate)^per * pv - ((1+rate)^per - 1) / rate * pmt

Where rate is same as above; per(iod) is the number of the specific payment you want like finding the future value on 6th payment; pmt is the calculated payment like from PMT() function.

Adjusting this again for type in same way as above with if statement, we get the FV() function similar to Excel.

Using the two functions afore mentioned, the PPMT and IPMT functions are greatly simplified to these formulas :

PPMT > pmt -  (fv * rate)
IPMT > fv * rate

Both are achieved by using pv and per -- same definitions as above -- to get fv.

fv( rate, per-1, c, pv, type )

Not that per-1 one used as in both cases we want the future value the payment before, so that we can then calculate the monthly interest for next payment (IPMT) and subsequently the principal of next payment (PPMT).  Consequently, you can see that the PPMT formula can even be:

pmt - ipmt

So implementing this to MySQL just remember pow() or power() function for exponents shown (e.g., pow(1+rate, nper)).  

See code snippet for example of PMT().


M-1

DELIMITER $$ 
 
DROP FUNCTION IF EXISTS `PMT`$$
-- Pmt( interest_rate, number_payments, PV, FV, Type )
CREATE FUNCTION `PMT`(
   mApr double,
   mNPer int,   
   mPv double,
   mFv double,
   type int
) RETURNS double
BEGIN 
  Declare mVal double; 
/*
In excel it should look like this:
=PMT(A1/12,B2,-C3, D4,1)
A1 = 7.40%
B2 = 36 (Month)
C3 = 106344 (Financing)
D4 = 75960 (Rest value)
In excel the result = 1404 
set Pv=106344;
set Fv=75960;
set NPer=36;
set Apr=.074;
set mMonths=12; 
*/ 
 
select mApr / (1 - pow(1 + mApr, -mNPer)) * ((mPv - mFv / pow(1 + mApr, mNPer)) * (1-if(type=1, mApr, 0))) into mVal; 
 
return mVal; 
END$$ 
 
DELIMITER ;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:

Have tested this against Excel extensively, but please test to ensure it holds up in all scenarios you are working with.  Then I would create the FV() function next using the formula above I showed.

My code snippet should have given you all the syntax you need.

Using both, the IPMT and PPMT will be much easier.

Give them a try yourself and post back as you will have a better understanding if you code on your own.

This is very good my friend, let me try it out. This looks more like the solution I am looking for. I will let you know after I test it in the actual program. Thanks.

Okay, not sure how things are going on your end, but did some more testing to see if can match up to the sign of Excel since I was inverse (i.e., returning positive values for positive pv) and refined formulas to this:


c = payment
rate = interest rate (monthly)
nper = number of payments / periods
pv = present value
fv = future value
per = period to check -- PPMT / IPMT


PMT:
pmt = rate / ((1 + rate)^nper - 1) * -(pv * (1 + rate)^nper + fv)

if type = 1, then pmt = pmt / (1+rate)

(so if type = 1 -- beginning of month payment -- divide first result by (1+rate))


FV:
if type = 1, then c = c * (1+rate)

(if beginning of the month payment, then multiply the passed in payment by (1+rate))

fv = -(((1 + rate)^nper - 1) / rate * c + pv * (1 + rate)^nper);


IPMT:
ipmt = fv(rate, per-1, pmt(rate, nper, pv, fv, type), pv, type) * rate

Use the fv() and pmt() functions.

if type = 1, then ipmt = ipmt / (1+rate)

(same as above, the result changes if type = 1)


PPMT:
pmt(rate, nper, pv, fv, type) - ipmt(rate, per, nper, pv, fv, type);

Use the pmt() and ipmt() functions.


Hope that helps.

So what does this mean in code :

pmt = rate / ((1 + rate)^nper - 1) * -(pv * (1 + rate)^nper + fv)
if type = 1, then pmt = pmt / (1+rate)

select (mApr / (pow(1 + mApr, mNPer) + 1) * -(mPv * pow(1 + mApr, mNPer) + fv)) / if(type=1, 1+mApr, 1) into mVal;


Or the last piece can be written as "... / (1+if(type=1,mApr,0))".

Hope that makes sense.

Great job Kevin. You are a man to rely on. Much thanks.