# How to calculate the equivalent of RATE in Excel in R?

Given a repayment and the loan term, I'd like to be able to calculate the interest rate for a loan.

In Excel, I can use the RATE function but I'm not sure what to use in R and googling is difficult since "r" is so frequently included in interest formulae. I've looked through the FinCal package but nothing stands out as equivalent.

Could someone point me to the correct formula that does the equivalent of:

`````` = RATE(12,-500,3000)
= 13%
``````
• Isn't `RATE` a simple internal rate of return calculation? – Glen_b Jan 28 '14 at 11:41
• I believe it is some form of rate of return, however, `FinCal:::irr` has only a cashflow argument `irr(cf)`, so I'm not sure if there's something else I need to be using? – Steph Locke Jan 28 '14 at 11:43
• Hmm, it looks like `discount.rate` might be the one I need: `discount.rate(n, pv, fv, pmt, type = 0)` – Steph Locke Jan 28 '14 at 11:46

Having checked, the `RATE` function in Excel is the internal rate of return, specifically one for an annuity.

There are several ways to approach this in R.

In vanilla R, both the `uniroot` and the `polyroot` functions can be used to solve for the rate, but it takes a bit of fiddling around:

Consider the following in Excel:

``````=RATE(10,-100,800)
``````

which produces the value:

``````4.2775%
``````

Now in R:

1. You can write a function to supply to uniroot:

``````> f <- function(i,n,a) a - (1-(1+i)^(-n))/i
> uniroot(f, interval=c(1e-9,1), 10, 800/100, tol=1e-6)\$root
 0.04277498
``````
2. Alternatively, you can use polyroot, but you need to find the real root that's between 0 and 1 (in the following I use 1+i rather than i because it's simpler, and so need the root between 1 and 2):

``````> res <- polyroot( c(1, rep(0,10-1), -(800/100+1), 800/100) )
> Re(res)[ abs(Im(res))<1e-9 ]
-0.7622679  1.0000000  1.0427750
``````

Where the second line is extracting the real roots of the polynomial equation in terms of (1+i), and the one you want is the one greater than 1 (1.0427750), from which you subtract 1.

3. You can also use more general internal rate of return functions for this problem, by supplying the vector of cash flows corresponding to the annuity.

4. You're correct that `FinCal::discount.rate` does essentially the same thing as RATE, but it looks like it rounds all its rates off to three significant figures without saying anything about it, even in the help as far as I can see. If greater accuracy matters to you, that could be a problem.

(An alternative for more accuracy is to take the output of that `discount.rate` function and take one step of Newton-Raphson, which should do much better.)