xirr函数

xirr函数

内部收益计算函数

曾经看过一个帖子：有一个理财产品，每年年初存入10000元，每年年底得到利息1000元。

持续5年，5年后返还本金50000元：问：利率是多少？
下面有个回复：每年存10000，利息1000，所以利率是10%。

那么问题来了，真实利率到底是多少？
office的excel有个公式叫xirr，参考 https://support.office.com/zh-cn/article/XIRR-%E5%87%BD%E6%95%B0-de1242ec-6477-445b-b11b-a303ad9adc9d
irr，参考 https://support.office.com/zh-cn/article/IRR-%E5%87%BD%E6%95%B0-64925eaa-9988-495b-b290-3ad0c163c1bc

实现如下：

require 'date'

def simply_parse str

   year, month, day = str.split('-')

   return Date.new(year.to_i, month.to_i, day.to_i)

end

def simply_sum arr

   arr.inject(0) { |sum, element| sum + element }

end

# guess : 从该值开始计算

# accuracy ： 计算精度，即每次递进的增量

# cal_times : 计算次数

def xirr(arr, guess = 0.05, accuracy = 0.0001, cal_times = 1000)

   rate = guess

   start_date = simply_parse(arr[0][1])

   end_date = simply_parse(arr[-1][1])

   __n = arr.map(&:first).map(&:to_f).select{|x| x < 0}

   __p = arr.map(&:first).map(&:to_f).select{|x| x > 0}

   t1 = ((simply_sum(__p))*accuracy).abs

   t2 = ((simply_sum(__n))*accuracy).abs

   tt = (t2+t1)/2

   return 'number error' if __n.empty? && __p.empty?

   last_sum = 0

   sum = 0

   flag = 0

   cal_times.times do

      sum = 0

      arr.each do |item|

         _date = simply_parse(item[1])

         return 'date error' if _date < start_date || _date > end_date

         profit = item[0].to_f * ((1+rate)**((end_date-_date)/365))

         sum = sum + profit

      end

      if last_sum == 0

         last_sum = sum

         next

      end

      if last_sum.abs < sum.abs

         accuracy = 0 - accuracy

         flag = flag + 1

      end

      if sum > (0 + tt)

         return rate if flag > 1

         rate = rate + accuracy

         last_sum = sum

      elsif sum < (0 - tt)

         return rate if flag > 1

         rate = rate - accuracy

         last_sum = sum

      else

         return rate

      end

   end

   if sum > (0 - tt*2) && sum < (0 + tt*2)

      return rate

   end

   return "guess failed, try guess = #{rate}"

end

#test

arr = '10000 2010-01-01

-1000 2010-12-31

10000 2011-01-01

-1000 2011-12-31

10000 2012-01-01

-1000 2012-12-31

10000 2013-01-01

-1000 2013-12-31

10000 2014-01-01

-1000 2014-12-31

-50000 2014-12-31'.split("\n").map{|x| x.split}

xirr arr, 0.05, 0.0001, 2000 # => 0.03409999999999955

arr = '-10000 2008-1-1

2750 2008-3-1

4250 2008-10-30

3250 2009-2-15

2750 2009-4-1'.split("\n").map{|x| x.split}

xirr arr, 0.37 # => 0.37329999999999963

arr = '10000 2008-1-1

-2750 2008-3-1

-4250 2008-10-30

-3250 2009-2-15

-2750 2009-4-1'.split("\n").map{|x| x.split}

xirr arr, 0.37 # => 0.37329999999999963

arr = '-700000 2008-1-1

120000 2009-1-1

150000 2010-1-1

180000 2011-1-1

210000 2012-1-1'.split("\n").map{|x| x.split}

xirr arr, 0.0 # => -0.021199999999999927

arr = '-700000 2008-1-1

120000 2009-1-1

150000 2010-1-1

180000 2011-1-1

210000 2012-1-1

260000 2013-1-1'.split("\n").map{|x| x.split}

xirr arr, 0.07 # => 0.08670000000000049

arr = '-700000 2008-1-1

120000 2009-1-1

150000 2010-1-1'.split("\n").map{|x| x.split}

xirr arr, -0.47 # => -0.44300000000000295