2

So I searched everywhere but I couldn't find an appropriate solution to my problem. I need to calculate the (annual) interest rate in PHP.

The function RATE on PHPExcel doesn't work (returning NaN), and I tried to use this to write an other function that gives me the interest rate of an investment when you have : the original amount, the final amount, the constant payment (the amount saved for a month) and the period (in months) of the investment.

But it still doesn't work. Here is the code if it can helps. 

public function calculateRate($originalAmount, $finalAmount, $monthlySavings, $savingsPeriod) {
    define('FINANCIAL_MAX_ITERATIONS', 128);
    define('FINANCIAL_PRECISION', 1.0e-06);

    $rate = 0.05; // guess 
    $i = 0;

    do {
        $equ = $monthlySavings * (pow(1 + $rate, $savingsPeriod) - 1) / $rate + $originalAmount * pow(1 + $rate, $savingsPeriod) + $finalAmount;
        $derivative = ( $monthlySavings * ( $savingsPeriod * $rate * pow(1 + $rate, $savingsPeriod - 1 ) - pow(1 + $rate, $savingsPeriod) ) + 1 ) / ($rate * $rate) + $savingsPeriod * $originalAmount * pow(1 + $rate, $savingsPeriod - 1);
        $div = $equ / $derivative;
        $oldRate = $rate;
        $rate -= $div;
        ++$i;
        var_dump($i, $rate, $equ, $derivative, $div);
    } while (abs($div) > FINANCIAL_PRECISION && ($i < FINANCIAL_MAX_ITERATIONS));

    return $rate;
}

I need a function that can calculate the interest rate but I can't find anything that works...

Community
  • 1
  • 1
Xuan-Thi Nguyen
  • 185
  • 1
  • 4
  • 11
  • Have you tried writing the function yourself? What is the formula you are trying to implement. Try writing it, and if it doesn't work, post the code here so we can help. – aniri May 15 '12 at 08:03
  • Have you tried using the edited version of the PHPExcel RATE() function that I supplied as a "fix" in answer to the question you've linked to? If so, can you provide me with details of datasets that cause it to fail. I can't fix the RATE function if I don't know what's wrong with it, and it works for the datasets that I use to test it. – Mark Baker May 15 '12 at 08:07
  • The code in pastebin is what I wrote myself, following the instructions of http://finance.thinkanddone.com/finding_irr_with_tvm_equation_using_newton_raphson_method.html who also posted in the other topic of stackoverflow that I have linked. Yes, I tried with the edited version you provided, Mark. So, the datasets are, for example : nper = 6 (in months), pmt = 1000 (in €), pv = 100 000 (in €), fv = 126068 (in €), type = 0 (by default). In my case, it's not a loan but an investment ... Maybe that's why it doesn't work? – Xuan-Thi Nguyen May 15 '12 at 08:16
  • Using MS Excel with your figures =RATE(6,-1000,100000,-126068,0) gives a result of 4.847212728357280% (or 0.0484721272835728), and I'm getting 0.048472127283573 from the modified PHPExcel version of that function – Mark Baker May 15 '12 at 08:56
  • So I must add the negative values? As an example, is MS Excel, I calculated the final value of an investment with : pv = 100000, (annual) rate = 0.03, pmt = 1000, nper = 6 (in months). And I found 126068... So I guess I should find 3% when I try to find the interest rate... – Xuan-Thi Nguyen May 15 '12 at 09:18
  • Okay... so in MS Excel, with =RATE(6,-1000,-100000,126068,0) I found 3,03%, which is exactly what I want. But with PHPExcel RATE() I have 2,5%... – Xuan-Thi Nguyen May 15 '12 at 09:46
  • Payments and deficits in Excel are generally treated as negative values: Incomes and receipts as positive. If I use +1000 instead of -1000 for the pmt value in MS Excel, I get a result of 0.0302728738275435. The modified PHPExcel RATE() function returns 0.030272873827544. Although not exatly the same, this closely matches Abraham from ThinkAndDone.com's result. – Mark Baker May 15 '12 at 10:03
  • Please note that the RATE() function used in PHPExcel has been modified since the 1.7.6 production release (as per the linked answer in your original question). Using your figures, my tests against the latest SVN code for PHPExcel show correct results – Mark Baker May 15 '12 at 10:06
  • Thank you so much Mark, so the problem was pretty simple! Thank you for your quick response! – Xuan-Thi Nguyen May 15 '12 at 11:50

2 Answers2

1

This is Abraham from ThinkAndDone.com, I had noticed you have visited us a number of times since yesterday.

You have to consider the underlying TVM equation that is used in finding RATE by MS Excel. There are two versions of it as listed below

PV(1+i)^N + PMT(1+i*type)[{(1+i)^N}-1]/i + FV = 0

The first one above compounds the present value and periodic payment at an interest rate i for n periods

FV(1+i)^-N + PMT(1+i*type)[1-{(1+i)^-N}]/i + PV = 0

The second one above discounts the future value and periodic payment at an interest rate i for n periods

These two equation will only hold true meaning will only equal ZERO when at least one or at most two of the three variables of FV, PV or PMT is negative

Any outgoing cash flow is a debit amount reflected by a negative number and any incoming cash flow is a credit amount reflected by a positive number

With that in mind, I would assume that PHPExcel RATE function should work as well

The RATE calculator from ThinkAndDone.com produces the following results for your investment using either of the 2 TVM equations with Newton Raphson method

PV = -100000
PMT = -1000
FV = 126068
NPER = 6
TYPE = 0
RATE = ?

Newton Raphson Method IRR Calculation with TVM equation = 0

TVM Eq. 1: PV(1+i)^N + PMT(1+i*type)[(1+i)^N -1]/i + FV = 0

f(i) = 126068 + -1000 * (1 + i * 0) [(1+i)^6 - 1)]/i + -100000 * (1+i)^6

f'(i) = (-1000 * ( 6 * i * (1 + i)^(5+0) - (1 + i)^6) + 1) / (i * i)) + 6 * -100000 * (1+0.1)^5

i0 = 0.1
f(i1) = -58803.71
f'(i1) = -985780.5
i1 = 0.1 - -58803.71/-985780.5 = 0.0403480693724
Error Bound = 0.0403480693724 - 0.1 = 0.059652 > 0.000001

i1 = 0.0403480693724
f(i2) = -7356.984
f'(i2) = -747902.9062
i2 = 0.0403480693724 - -7356.984/-747902.9062 = 0.0305112524399
Error Bound = 0.0305112524399 - 0.0403480693724 = 0.009837 > 0.000001

i2 = 0.0305112524399
f(i3) = -169.999
f'(i3) = -713555.4448
i3 = 0.0305112524399 - -169.999/-713555.4448 = 0.0302730102033
Error Bound = 0.0302730102033 - 0.0305112524399 = 0.000238 > 0.000001

i3 = 0.0302730102033
f(i4) = -0.0972
f'(i4) = -712739.5905
i4 = 0.0302730102033 - -0.0972/-712739.5905 = 0.0302728738276
Error Bound = 0.0302728738276 - 0.0302730102033 = 0 < 0.000001
IRR = 3.03%


Newton Raphson Method IRR Calculation with TVM equation = 0

TVM Eq. 2: PV + PMT(1+i*type)[1-{(1+i)^-N}]/i + FV(1+i)^-N = 0

f(i) = -100000 + -1000 * (1 + i * 0) [1 - (1+i)^-6)]/i + 126068 * (1+i)^-6

f'(i) = (--1000 * (1+i)^-6 * ((1+i)^6 - 6 * i - 1) /(i*i)) + (126068 * -6 * (1+i)^(-6-1))

i0 = 0.1
f(i1) = -33193.1613
f'(i1) = -378472.7347
i1 = 0.1 - -33193.1613/-378472.7347 = 0.0122970871033
Error Bound = 0.0122970871033 - 0.1 = 0.087703 > 0.000001

i1 = 0.0122970871033
f(i2) = 11403.9504
f'(i2) = -680214.7503
i2 = 0.0122970871033 - 11403.9504/-680214.7503 = 0.0290623077396
Error Bound = 0.0290623077396 - 0.0122970871033 = 0.016765 > 0.000001

i2 = 0.0290623077396
f(i3) = 724.4473
f'(i3) = -605831.2626
i3 = 0.0290623077396 - 724.4473/-605831.2626 = 0.0302580982453
Error Bound = 0.0302580982453 - 0.0290623077396 = 0.001196 > 0.000001

i3 = 0.0302580982453
f(i4) = 8.8061
f'(i4) = -600890.1339
i4 = 0.0302580982453 - 8.8061/-600890.1339 = 0.0302727533356
Error Bound = 0.0302727533356 - 0.0302580982453 = 1.5E-5 > 0.000001

i4 = 0.0302727533356
f(i5) = 0.0718
f'(i5) = -600829.8628
i5 = 0.0302727533356 - 0.0718/-600829.8628 = 0.0302728728509
Error Bound = 0.0302728728509 - 0.0302727533356 = 0 < 0.000001
IRR = 3.03%

The two TVM equation I listed earlier are applicable when interest is compounded discretely as in per period compounding (yearly, quarterly, monthly, weekly, daily) where as most bank accounts pay interest on savings or charge interest on loan when interest is compounded continuously (infinite compounding of interest) as opposed to discrete compounding

The TVM equations for continuous compounding use interest factors that are different from the ones in discretely compounded version

Here are the 2 TVM equations when interest is compounded continuously

PV eni + PMT ei*type[eni-1]/[ei-1] + FV = 0

or the equivalent

FV e-ni + PMT ei*type[1-e-ni]/[ei-1] + PV = 0

here e is the mathematical constant that has the value of 2.7182818284590452353602874713527

The RATE will be different when interest is compounded discretely as opposed to when it is compounded continuously.

  • Hey Abe, did you tell Geneva that the rate was 3.03%? Abe, it seems he is investing in a savings account in a bank? Yes Mike, I overlooked that aspect of his investment. It does seem like an investment in a bank account with an initial deposit followed by six periodic deposits. So Abe, tell him what his rate actually is! Ok Geneva, your rate isn't 3.03% since Excel's RATE function assumes interest is compounded discretely as in per period compounding. For your example investment, the actual rate is 2.98%. This is because most bank accounts earn interest for each iota of seconds of time. –  May 15 '12 at 13:10
  • Yes, it's for an investment in a bank. I didn't get all of your explanations, but the precision of the rate isn't that important in my case, I think. Thank you all for your responses, it's very appreciated ! (and it's "she", actually. ^^) – Xuan-Thi Nguyen May 15 '12 at 14:33
  • Geneva! It's not the precision I was referring to. Banks use continuous compounding of interest on savings accounts as opposed to discrete compounding. In such a case, the interest factors in TVM equation are different than the ones I have listed in my reply. You may want to read on the middle section of this IRR tutorial http://finance.thinkanddone.com/irr.html to find the 2 TVM equations when interest is compounded continuously. –  May 15 '12 at 15:57
  • Geneva! I amended my reply to include to the 2 new TVM equations for the case when interest is compounded continuosly –  May 15 '12 at 16:26
  • BTW you write much compact and neat code as compared to mine. In your implementation of RATE function you would need to ensure for the case when the differential might be zero thus leading to DIVISION BY ZERO –  May 15 '12 at 16:38
0

This will not give you the final answer, but hopefully will point you in the right direction. I'll try to lay out the math background for it.

Let's assume that your initial investment is A, you have N periods, during which you invest fixed amount B at the rate per period of x and you get at the end amount C. Note that I'm talking about a rate per period. If you invest monthly and you are looking for an annual rate of return X, then x = X / 12. Then you can put this into an equation like so: 

         N            N-1            N-2
A * (1+x)  + B * (1+x)    + B * (1+x)    + ... + B * (1+x) + B = C

Using geometrical progression formulas, this can be simplified to be:

                           N-1
         N        1 - (1+x)
A * (1+x)  + B * ------------- = C
                   1 - (1+x)

Continuing with basic algebra, you can turn this into

         N                N-1
A * (1+x)  * x + B * (1+x)   - C * x - B = 0

At this point, you would need to use numerical methods to solve this equation - you can have a look here, for example: http://en.wikibooks.org/wiki/Numerical_Methods/Equation_Solving

Aleks G
  • 56,435
  • 29
  • 168
  • 265