Geek Answers Handbook

Excel Formula in PHP

Can anyone tell me the math behind these MS Excel functions. I am writing code in php and I cannot use Excel to compute them, but I still need the same result.

CUMIPMT(rate,nper,pv,start_period,end_period,type) Returns the cumulative interest paid on a loan between start_period and end_period.

My Excel function is

 =IF($C$33<0,CUMIPMT($C$36/12,$C$35,-$C$33,$C$34,$C$34+11,0),-CUMIPMT($C$36/12,$C$35,$C$33,$C$34,$C$34+11,0))

Another question - I used (=E11-E12) , but this is not a subtraction, it adds both values, I canโ€™t understand why?

Thank you in advance

+4
source share
3 answers

I have not used it, but you can check out the PHPExcel Library . Excel and PHP are different enough that you will need to use a library similar to the one with which I am associated or writing functions to do all this math manually. In your case, it seems that excel file processing might be the easiest way to achieve your goal.

+2
source

IF( condition, [value_if_true], [value_if_false] )

condition is the value you want to check.

value_if_true is optional. This value is returned if the condition is TRUE.

value_if_false is optional. This value is returned if the condition is FALSE.

So basically, this suggests that

 if ($C$33 < 0) { // if the condition is TRUE return CUMIPMT($C$36 / 12, $C$35, -$C$33, $C$34, $C$34 + 11, 0); } else { // if the condition is FALSE return -CUMIPMT($C$36 / 12, $C$35, $C$33, $C$34, $C$34 + 11, 0); }

Regarding the problem (=E11-E12) . Try changing it to =E11-E12 . If E12 is a negative number, you must remember that the minus sign will spread.

EDIT: Regarding your comment, I hope this link helps

http://www.excelforum.com/excel-programming-vba-macros/515109-need-longhand-formulas-for-cumipmt-and-cumprinc-functions-in-excel.html

0
source

I know this thread is outdated, but today I was working on converting an Excel spreadsheet to objective-c, and I needed an answer too. Here is the solution in which I ended up writing:

 double cumipmt(double rate, int nper, double pv, int start_period, int end_period, BOOL type) { double cumipmt = 0; double thePmt = pmt(rate, nper, pv, 0, type); double endValue = pv; if (type==0) { for (int i=1; i<=end_period; i++) { double beginning = endValue; double interest = beginning * rate; endValue = beginning + interest + thePmt; if (i>=start_period) { cumipmt += interest; } } } else { for (int i=1; i<end_period; i++) { double beginning = endValue + thePmt; double interest = beginning * rate; endValue = beginning + interest + thePmt; if (i>=start_period) { cumipmt += interest; } } } return cumipmt; } double pvFactor(double rate, int nper) { return (1 / fvFactor(rate, nper)); } double fvFactor(double rate, int nper) { return pow(1+rate,nper); } double annuityPvFactor(double rate, int nper, bool type) { if (rate == 0) { return nper; } else { return (1 + rate * type) * (1 - pvFactor(rate, nper)) / rate; } } double pmt(double rate, int nper, double pv, double fv, bool type) { return (-1 * (pv + fv + pvFactor(rate, nper))) / (annuityPvFactor(rate, nper, type)); }

I know this is not in PHP, but it should be a simple task to convert.

0
source

More articles:


All Articles