3

I have less knowledge in Excel , now I want your help to calculate rate of interest in excel without using RATE command. (so that I can try to convert the formula in php) This is the code we use in Excel

  • B5= 60
  • B4= 16070
  • B3= 750000

  • B6= 1

    =RATE(B5,B4,-B3,0,B6)*12

It should give a result of "10.75%" as rate.

How can I get the same result without using 'RATE' or any other commands?

Shijil
  • 51
  • 1
  • 7
  • This question is not related to the excel. – Ethun_Hunt Mar 17 '16 at 13:10
  • Ethun_Hunt@ it is related to Excel , I am not asking for help in php , I need help with a formula in Excel. – Shijil Mar 17 '16 at 13:11
  • What he means is this is a mathematics question as you do not want to use inherent excel functions so it can be done on a calculator as easy as excel. – Scott Craner Mar 17 '16 at 13:21
  • It can be done, but I guess it is not that easy because RATE is calculated by an iterative process. There is an excellent answer here: http://stackoverflow.com/questions/18840190/calculating-interest-rate-in-php – Sebastian B Mar 17 '16 at 13:22
  • 1
    It is a better fit here, http://math.stackexchange.com/ – Scott Craner Mar 17 '16 at 13:28
  • A quick search on google finds this: https://www.math.nmsu.edu/~pmorandi/math210gs99/InterestRateFormulas.html which gives the formula to use to find the interest. – Scott Craner Mar 17 '16 at 13:41

1 Answers1

2

From pgc01's answer:


Function MyRATE(nper As Integer, pmt As Double, pv As Double, Optional fv As Double = 0, _
                              Optional PaymentEnd As Integer = 0, Optional guess As Double = 0.1)
    Dim a As Double, b As Double, c As Double ' coefficients of the equation
    Dim R As Double, RTmp As Double, i As Integer

    ' Initialize coefficients and R
    R = 1 + guess
    a = (pmt * (1 - PaymentEnd) - pv) / (pv + pmt * PaymentEnd)
    b = (fv - pmt * PaymentEnd) / (pv + pmt * PaymentEnd)
    c = (-pmt * (1 - PaymentEnd) - fv) / (pv + pmt * PaymentEnd)

    ' Iterate
    For i = 1 To 20
        RTmp = R - (R ^ (nper + 1) + a * R ^ nper + b * R + c) / ((nper + 1) * R ^ nper + a * nper * R ^ (nper - 1) + b)
        If Abs(RTmp - R) < 0.0000001 Then Exit For
        R = RTmp
    Next i

    If i <= 20 Then
        MyRATE = RTmp - 1
    Else
        MyRATE = "N/A" ' Must try another guess
    End If
End Function

enter image description here

Note:

This is not my code. I have not tested it over a full range of inputs.

EDIT#1:

This is a User Defined Function. User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel worksheet cell:

=myrate(B5,B4,-B3,0,B6)*12

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Community
  • 1
  • 1
Gary's Student
  • 95,722
  • 10
  • 59
  • 99