Finance should have an implementation of the XIRR function. Some details are available on the OpenOffice.org wiki:

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_XIRR_function

Other implementations I've found on the web:

• http://stackoverflow.com/questions/5179866/xirr-calculation
• http://search.cpan.org/~erwan/Finance-Math-IRR-0.10/lib/Finance/Math/IRR.pm

Some thoughts on the implementation:

• The xirr method will be defined as an Array method (similar to Array#irr) inside Finance::Cashflow.
• Instead of values, the Array should contain Transaction objects, which will have date and amount attributes.
  • The date attribute hasn't been defined yet, but it should probably accept a String, and convert internally to a Ruby Date (or Datetime) object.
• Since the relevant time periods are known, the xirr method ought to return a Rate object (this is not the case in the example below).

Example

> t = []
> t << Transaction.new(-5000, :date => '3/1/2011')
> t << Transaction.new(100, :date => '4/1/2011')
> t << Transaction.new(100, :date => '5/15/2011')
> t << Transaction.new(100, :date => '6/29/2011')
> t << Transaction.new(5000, :date => '7/4/2011')
> t.xirr
#=> 0.19021145803047

I think this is just a problem with the new namespacing... Numeric::amortize calls Amortization.new, probably means to call
Finance::Amortization.new

We are working on a web-based interface for comparing mortgage offerings. I need to be able to compute the APR given an initial loan value, monthly payments, and the number of loan payments.

We have evaluated a number of options for computing APR and while the finance gem's IRR function does appear to be a contender, it has a number of issues that prevent us from adopting it:

1. Speed. The gem is somewhat slow, often completing in 10+ seconds. It needs to be able to compute results very quickly (in the +/- 200ms range) to maintain a responsive user interface.
2. Accuracy. It is remarkably accurate relative to Google spreadsheet's RATE() function. However, it is possible for some test cases to be wildly inaccurate.

Please consult this spreadsheet of test values: https://docs.google.com/spreadsheet/ccc?key=0AlIlnFeJb27JdHFzc0RQa0t5RDBNajRoZ2lYemdTakE#gid=0

And note this particular bug:

[24] pry(main)> t = Time.now; puts [-236282.5, *[1165.81]*360].irr.to_f * 12; puts Time.now - t
-23.801691983216614
70.137133

Hi,

I am using your gem on a project and the npv calculation seems to give different outputs than when I use Excel with the same inputs.
For instance, when I try [1,2,3].npv(0.045) it gives 5.661 whereas Excel gives 5,42.
Do you have any idea what can cause the difference?

Thanks

Does the XIRR calculation handle both Float and Decimal number formats or is it necessary to feed in one vs the other?

Thanks!

@wkranec is there any plan for interest only, partially amortizing, or even negatively amortizing schedules? from reading the code it feels like only fully-amortizing. is that correct?

I'm looking for someone interested in taking over as the maintainer of the finance package. Send me a message on GitHub (or respond to this issue) if you are interested.

As much as I would like to continue to develop and maintain this code, the fact of the matter is that I no longer have the time to do so. There are several pull requests and good suggestions that have been left by the wayside due to my inaction, and I don't think that is right.

I originally wrote this code for a personal project, and I've always been flattered that the Ruby community has found it to be useful. Thanks!

This program yields the wrong solution

irb(main):080:0> [-100,1,50,50,50].irr
-1.802970927840458773306399728082340970593857587447338725049180655651412701292513248441308155402999109362230823362680689447524157318592267102196347382451152920776482908594822377770535664250863```

I've been getting this error on many different series. I've tried various versions of Ruby 1.9 and 2.0. 1.9 tends to hang whereas 2.0 errors out fairly quickly. I've tried these same calculations in both Excel and the Java Apache POI library and they return good values. Any ideas?

Some examples:

[-1,-2,0,5].irr.to_f
=> 0.24189656303203824

[-1,-3,0,5].irr.to_f
RuntimeError: Failed to reduce function values (newton.rb:66 in nlsolve)

[-13000,-3000,-3000,0,0,5000,10000,20000].irr.to_f
RuntimeError: Singular Jacobian matrix. No change at x[0](jacobian.rb:58 in dfdxi)

2.1.1 :002 > arr = [ Finance::Transaction.new(70, :date => Time.new(2015,7,31)),
2.1.1 :003 >     Finance::Transaction.new(-90, :date => Time.new(2021,1,12)),
2.1.1 :004 >     Finance::Transaction.new(-20, :date => Time.new(2021,3,31)) ]
 => [Transaction(70), Transaction(-90), Transaction(-20)]
2.1.1 :005 > arr.xirr
 => Rate.new(0.085713, :apr)

This value matches what Excel 2013's XIRR function returns.

Now, if we change the date of $90 transaction just one day further...

2.1.1 :006 > arr = [ Finance::Transaction.new(70, :date => Time.new(2015,7,31)),
2.1.1 :007 >     Finance::Transaction.new(-90, :date => Time.new(2021,1,13)),
2.1.1 :008 >     Finance::Transaction.new(-20, :date => Time.new(2021,3,31)) ]
 => [Transaction(70), Transaction(-90), Transaction(-20)]
2.1.1 :009 > arr.xirr
 => Rate.new(-1.451804, :apr)

Excel 2013's XIRR function returns correct result in this case: 0.085676876.

sorry, i may be missing something here (probably am :/)
but I just installed the gem (which is awesome by the way thanks for starting this!)

I wasn't able to get the examples working, this is within the context of a rails stack.

when I try the IRR example
[-4000,1200,1410,1875,1050].irr
I seem to be getting the error
Flt::DecNum can't be coerced into BigDecimal
This seems to be working fine in the rails console though.

as for the xirr example

@transactions = [] @transactions << Transaction.new(-1000, :date => Time.new(1985,01,01)) @transactions << Transaction.new( 600, :date => Time.new(1990,01,01)) @transactions << Transaction.new( 600, :date => Time.new(1995,01,01)) @transactions.xirr(0.6)

I am getting this error
wrong argument type Float (expected Fixnum)

not quite sure what I am doing wrong, anyone have an idea?

Thanks!

[10, 20, 30].irr will freeze :-(

It should raise a specific exception instead.

r = Finance::Rate.new( Flt::DecNum('0.06'), :apr, :duration => 10.years, :compounds => 4 )

a = Finance::Amortization.new( 1000, r )

a.interest seems to have 10_12 entries, not 10_4

doesn't seem to be right with duration set to 10 or 10 * 4 either ( it doesn't apply 1/4 of 0.06 as expected )

is there some way to do this that just isn't obvious from the readme? or is this simply non-working?

wrong argument type Flt::DecNum (expected scalar Numeric)

in

finance (1.1.2) lib/finance/rates.rb:162:in `**'

changing

    def Rate.to_nominal(rate, periods)
      rate, periods = rate.to_d, periods.to_d                                                               

      if periods.infinite?                                                                                  
        (rate + 1).log                                                                                      
      else
         periods * ((1 + rate) ** (1 / periods) - 1)                                                         
      end                                                                                                   
    end  

by adding the line

periods = periods.to_f

to the else statements

      if periods.infinite?                                                                                  
        (rate + 1).log                                                                                      
      else
        periods = periods.to_f # my workaround
        periods * ((1 + rate) ** (1 / periods) - 1)                                                         
      end                                                                                                   

seems to fix it. Is this a bug you recognize?

Some companies will only use gems with a certain license.
The canonical and easy way to check is via the gemspec,

via e.g.

spec.license = 'MIT'
# or
spec.licenses = ['MIT', 'GPL-2']

Even for projects that already specify a license, including a license in your gemspec is a good practice, since it is easily
discoverable there without having to check the readme or for a license file. For example, it is the field that rubygems.org uses to display a gem's license.

For example, there is a License Finder gem to help companies ensure all gems they use
meet their licensing needs. This tool depends on license information being available in the gemspec. This is an important enough
issue that even Bundler now generates gems with a default 'MIT' license.

If you need help choosing a license (sorry, I haven't checked your readme or looked for a license file), github has created a license picker tool.

In case you're wondering how I found you and why I made this issue, it's because I'm collecting stats on gems (I was originally looking for download data) and decided to collect license metadata,too, and make issues for gemspecs not specifying a license as a public service :).

I hope you'll consider specifying a license in your gemspec. If not, please just close the issue and let me know. In either case, I'll follow up. Thanks!

p.s. I've written a blog post about this project

Rails defines sum as well, and they do a more thorough job of it...

that is, they allow you to specify an Identity besides 0 (we could specify Flt::DecNum('0') for example)

the problem arises from Rails and Finance both trying to define sum on array, but with different signatures.

could we check to see if Array already responds to sum before defining it? or failing that, could we have sum take the same function signature so that the subclassing succeeds? as it is rails apps blow up when they require the finance gem.

Hello everyone,
For amortization class, I need to calculate a growing annuity. Any ideas on how to do it? Thanks everyone.
https://en.wikipedia.org/wiki/Time_value_of_money#Present_value_of_a_growing_annuity

Good Doc's xirr() thinks the result should be: 0.1877607042

The result should certainly be positive.

DATA:
5/3/2010   --   -3984.732
8/12/2010  --   -1877.98
6/2/2014   --    11684.915242

2.0.0-p353 :044 > trans = []
 => []
2.0.0-p353 :045 > trans << Finance::Transaction.new(-3984.732, date: Date.civil(2010, 5, 3).to_time)
 => [Transaction(-3984.732)]
2.0.0-p353 :046 > trans << Finance::Transaction.new(-1877.98, date: Date.civil(2010, 8, 12).to_time)
 => [Transaction(-3984.732), Transaction(-1877.98)]
2.0.0-p353 :047 > trans << Finance::Transaction.new(11684.915242, date: Date.civil(2014, 6, 2).to_time)
 => [Transaction(-3984.732), Transaction(-1877.98), Transaction(11684.915242)]
2.0.0-p353 :048 > trans.xirr.apr.to_f
 => -2.212298586260735

With ruby1.9.3p0 your XIRR example does not work:

@Transactions = []
@Transactions << Transaction.new(-1000, :date => Time.new(1985,01,01))
@Transactions << Transaction.new( 600, :date => Time.new(1990,01,01))
@Transactions << Transaction.new( 600, :date => Time.new(1995,01,01))
@transactions.xirr(0.6).round(2) #=> Rate("0.024851", :apr, :compounds => :annually)

It results in this error:

Math::DomainError: Zero or negative argument for log
from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/finance-1.1.0/lib/finance/cashflows.rb:106:in **' from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/finance-1.1.0/lib/finance/cashflows.rb:106:inblock in xnpv'
from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/finance-1.1.0/lib/finance/cashflows.rb:105:in each' from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/finance-1.1.0/lib/finance/cashflows.rb:105:ininject'
from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/finance-1.1.0/lib/finance/cashflows.rb:105:in xnpv' from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/finance-1.1.0/lib/finance/cashflows.rb:35:invalues'
from /Users/krschacht/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/bigdecimal/newton.rb:71:in nlsolve' from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/finance-1.1.0/lib/finance/cashflows.rb:88:inxirr'
from (irb):21
from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/railties-3.1.3/lib/rails/commands/console.rb:45:in start' from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/railties-3.1.3/lib/rails/commands/console.rb:8:instart'
from /Users/krschacht/.rvm/gems/ruby-1.9.3-p0/gems/railties-3.1.3/lib/rails/commands.rb:40:in <top (required)>' from script/rails:6:inrequire'
from script/rails:6:in `

Downgrading to ruby 1.9.2p290 and ran the same thing and it error'd with:

NoMethodError: undefined method round' for Rate.new(0.024851, :apr):Finance::Rate from (irb):6 from /Users/krschacht/.rvm/gems/ruby-1.9.2-p290@rails313/gems/railties-3.1.3/lib/rails/commands/console.rb:45:instart'
from /Users/krschacht/.rvm/gems/ruby-1.9.2-p290@rails313/gems/railties-3.1.3/lib/rails/commands/console.rb:8:in start' from /Users/krschacht/.rvm/gems/ruby-1.9.2-p290@rails313/gems/railties-3.1.3/lib/rails/commands.rb:40:in<top (required)>'
from script/rails:6:in require' from script/rails:6:in

At least we're making progress. :)

@transactions.xirr(0.6).apr.round(2)

Fixed the last issue.

the NPV calculation (and probably others) have unexpected side effects on the input array. It will turn all numbers in the input array into DecNums.

Example:

x = [1,2,3]
=> [1, 2, 3]
x.npv(1.5)
=> DecNum('2.28')
x
=> [DecNum('1'), DecNum('2'), DecNum('3')]

This example is from the rails console using ruby 2.0.0-p353 and rails 3.2.13

I was going to contribute an extra feature to Finance but wanted to write a test to go with it first. At the moment if I try to run the tests either using the Rakefile or just using e.g. ruby test/test_amortization.rb then the .rb files load but the tests don't actually run.

If I replace require 'minitest/unit' with require 'test/unit' then the tests run correctly.

Is the same thing happening for anyone else or is it something to do with the specific versions of gems that I've got installed?

Thanks,

-Michael

Is this repo still in need of a maintainer? I would be glad to try and help out.

In other implementations of the IRR function, one can set the initial guess and the number of iterations.

Here the irr function doesn't receive any parameter and the xirr receives the number of iterations but i don't see the function make use of this value.

is there a reason for this?

PS: I'm looking at the https://github.com/wkranec/finance/blob/master/lib/finance/cashflows.rb file
PS2: also the tolerance could be nice. For example tolerance = 0.00000001

In Rails this used to work:

10.months.ago

Now it just returns the current time

Regards

This issue is related to issue #24, but with some other cases of getting the wrong answer instead of failing. First, the following sequence gives me an error:

transactions = []
transactions << Transaction.new(-1000, date: Time.new(1990,1,1))
transactions << Transaction.new(390000, date: Time.new(2013,1,1))
transactions.xirr

The error is:

RuntimeError: Failed to reduce function values.
    from /home/drew/.rvm/rubies/ruby-2.0.0-p0/lib/ruby/2.0.0/bigdecimal/newton.rb:66:in `nlsolve'

I was able to coax nlsolve into continuing to try by multiplying minfact by 100 in the BigDecimal module's newton.rb. This made it take longer to get to the fail state, and in the mean time, it ended up finding the answer. I don't know enough about how Newton's method really works to know how to fix this problem from the finance gem's perspective though...

The other error is more perplexing. the following code does not error out, but produces a wildly inaccurate result:

transactions = []
transactions << Transaction.new(-1000, date: Time.new(1957,1,1))
transactions << Transaction.new(390000, date: Time.new(2013,1,1))
transactions.xirr

It returns Rate.new(-9999999999998.948000, :apr) when the answer should be something like: Rate.new(0.112339453419573, :apr) (at least that's what google spreadsheets told me.)

I don't really know what is causing this, but I suspect it is an overflow or some other error that occurs when converting to and from the different number formats used during calculation. Why is there frequent conversion to and from Flt::DecNum, and BigDecimal, and why is :to_s constantly called?

I'll try and look at this this weekend, but I don't know how far I'll get. This issue could really use some attention from someone more familiar with Newton's method and internal rate of return calculations in general.

I need to implement the excel RATE() function to do some financial calculus. I didn't understand both MS documentation and this gem documentation, so... is rate method the ruby implementation of that Excel function?

Hi!

Would you be able to fix this deprecation warning:

.rvm/gems/ruby-2.6.6/gems/finance-2.0.0/lib/finance/cashflows.rb:25: warning: BigDecimal.new is deprecated; use BigDecimal() method instead.

Thank you!

In Amortization Class, the calculations are based on the assumption that the payments are made at the end of the period. The documentation also suggests that.

Is there a way to have the calculations work with a start of the period?

Like in excel PMT function, we can specify a 0 or 1 for the payment period.

Thanks in advance.

Me again. I have a strange performance issue on Ruby 1.9.3p194 with Rails 2.3.8. When I run a simple calculation of irb to limited set of cashflows on irb, it works fine.
But when i'm running ./script/console or in the app itself, .irr takes infinutum time and crashes the app server.

In the Rails app, I include the gem in the Gemfile and include the finance Module on the class.

Thanks in advance.

Hi Bill,

Working through your gem and it looks pretty sweet. Ran into some entertaining error that I had no idea how to solve so I took a deeper dive.

The error occurs with Ruby 1.9.3p0 and Rails 3.2.3.
When you attempt to something simple like

rate = Rate.new(0.04, :effective, :duration => 360)
amortization = Amortization.new(100000, rate)

You get an error

wrong argument type Flt::DecNum (expected scalar Numeric)

Investigating, this is the stack trace

lib/finance/rates.rb:161:in **' lib/finance/rates.rb:161:into_nominal'
lib/finance/rates.rb:75:in effective=' lib/finance/rates.rb:101:ininitialize'
app/controllers/loans_controller.rb:49:in new' app/controllers/loans_controller.rb:49:increate'

If I hop over to Rates RB and update the @periods initializer from

def compounds=(input)
  @periods = case input
             when :annually     then Flt::DecNum 1
             when :continuously then Flt::DecNum.infinity
             when :daily        then Flt::DecNum 365
             when :monthly      then Flt::DecNum 12
             when :quarterly    then Flt::DecNum 4
             when :semiannually then Flt::DecNum 2
             when Numeric       then Flt::DecNum input.to_s
             else raise ArgumentError
             end
end

and update the "monthly" setting to

             when :monthly      then '12.0'.to_d

It seems to work.

Of the top of your head, do you know what I lose if I update all those Flt:DecNum initializers to something simpler?

Thanks & thanks for the great work on the gem.

Reid

So the gem appears to incorrectly calculate IRR (using xirr) for highly negative returns. For example, using this set of cashflows / transactions

2014-04-15 00:00:00 -0700 : -10000.0
2014-04-16 00:00:00 -0700 : -10000.0
2014-05-16 14:59:00 -0700 : 305.6
2014-06-15 23:59:59 -0700 : 9800.07
2014-06-15 23:59:59 -0700 : 5052.645

the gem gave an IRR of -107% while other tools gave an IRR of -80%. Can you guys look into this please?

Thank you.

AK

The #payment method should be guaranteed to return a number, as 1) methods that depend on it are almost certainly expecting a number; and 2) if having an amount of zero is unrecoverable an exception should be thrown instead of returning nil, as returning nil is ambiguous.