CODEWITHSUNDEEP
rexcelirr
showkey
showkey

Reputation: 298

Different results for IRR computation between R and MS Excel

The internal rate of return (IRR) or economic rate of return (ERR) is a rate of return used in capital budgeting to measure and compare the profitability of investments.

I wrote some R code to calculate the internal rate of return (IRR) like this:

cal_irr <- function(amount,fee,duration) {
    cash<-c(amount,rep(-1*(amount*fee+amount/duration),duration))
    NPV<-function(r){sum(cash /((1 + r) ^ (seq(along.with = cash)-1)))}
    return(uniroot(NPV, c(0, 1))$root)
}

cal_irr can calculate Instalment Payments, but the annoying thing is that my result is different from the financial function IRR in MS Excel.

For example, you borrow 3600 from the bank, the administrative fee is 0.006*3600, equal principal instalments in 24 months, so every month you have to pay 3600*0.006+3600/24=171.6.

The cost you incur is cal_irr(3600,0.006,240) = 0.01104071 per month, but in Excel I got 1.1054657%. What is wrong with my R code?

enter image description here

Upvotes: 1

Views: 1053

Answers (2)

landroni
landroni

Reputation: 2988

If you have a CF that looks like this: 

> cal_cash <- function(amount,fee,duration) c(amount,rep(-1*(amount*fee+amount/duration),duration))
> cal_cash(3600,0.006,24)
 [1] 3600.0 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6 -171.6
[20] -171.6 -171.6 -171.6 -171.6 -171.6 -171.6

Then it's easy to use the financial package to compute the IRR: 

> require(financial)
> cf(cal_cash(3600,0.006,24))$irr
[1] -185.755352    1.105466

Upvotes: 0

BrodieG
BrodieG

Reputation: 52657

You are unirooting to find small numbers, which can cause issues with tolerance. Try: 

cal_irr <- function(amount,fee,duration) {
  cash<-c(amount,rep(-1*(amount*fee+amount/duration),duration))
  NPV<-function(r){sum(cash /((1 + r) ^ (seq(along.with = cash)-1)))}
  return(uniroot(NPV, c(0, 1), tol=.0000001)$root)}
cal_irr(3600,0.006,24)
# [1] 0.01105466

Upvotes: 3

Related Questions