some excel formulas in Ruby

gistfile1.rb

module Excel
  module Formulas  
    def pmt(rate, nper, pv, fv=0, type=0)
      ((-pv * pvif(rate, nper) - fv ) / ((1.0 + rate * type) * fvifa(rate, nper)))
    end

    def ipmt(rate, per, nper, pv, fv=0, type=0)
      p = pmt(rate, nper, pv, fv, 0);
      ip = -(pv * pow1p(rate, per - 1) * rate + p * pow1pm1(rate, per - 1))
      (type == 0) ? ip : ip / (1 + rate)
    end
  
    def ppmt(rate, per, nper, pv, fv=0, type=0)
     p = pmt(rate, nper, pv, fv, type)
     ip = ipmt(rate, per, nper, pv, fv, type)
     p - ip
    end
    
    protected
    
    def pow1pm1(x, y)
      (x <= -1) ? ((1 + x) ** y) - 1 : Math.exp(y * Math.log(1.0 + x)) - 1
    end
  
    def pow1p(x, y)
      (x.abs > 0.5) ? ((1 + x) ** y) : Math.exp(y * Math.log(1.0 + x))
    end
  
    def pvif(rate, nper)
      pow1p(rate, nper)
    end
  
    def fvifa(rate, nper)
      (rate == 0) ? nper : pow1pm1(rate, nper) / rate
    end
    
  end
end

Hi Matt,
Thank you very much for this gist which is quite helpful (even today 😉)!

To complete this gist, it would be nice to add the 2 other Excel functions that work with PMT, IPMT and PPMT which are CUMPRINC and CUMIMPT. I let the code here if it can help some people:

CUMPRINC - Returns the cumulative principal paid on a loan between start_period and end_period

def cumprinc(rate, nper, pv, start_per, end_per, type =  #0)
    (start_per..end_per).map do |per|
      ppmt(rate, per, nper, pv, 0, type)
    end.sum
  end

CUMIPMT - Returns the cumulative interests paid on a loan between start_period and end_period

def cumipmt(rate, nper, pv, start_per, end_per, type = 0)
    (start_per..end_per).map do |per|
      ipmt(rate, per, nper, pv, 0, type)
    end.sum
 end

⚠ these functions do assume that the loan is always paid off by the end of the nper periods

Mth0158

Thanks @Mth0158. I totally forgot I wrote and published this gist!