module ExcelFunctions
module to be included in a client class
file containing the financial functions
file containing the lookup based functions
Constants
- VERSION
Public Instance Methods
The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, the LOOKUP function will return the first column of data. In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array. @param value the value to be searched for @param [Array] lookup_array the array in which the value is looked for, and possibly the result is fetched from @param [Array] output_array If given, the result is fetched from this array
@return Returns the value in the output_array or the last column of the lookup_array from the same position in the result_range @return If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. @return If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A(nil in our case). @return If the values in the LOOKUP_range are not sorted in ascending order, the LOOKUP function will return the incorrect value (raise error in our case). @see http://www.techonthenet.com/excel/formulas/lookup.php Specification
# File lib/excel_functions/lookup.rb, line 14 def lookup(value, lookup_array, output_array = nil) #combining both syntaxes here by taking 1st col of lookup_array to lookup # and the output_array or the last column of the lookup_array to output output_array = output_array || lookup_array.map{|arr| Array(arr)[-1]} lookup_array = lookup_array.map{|arr| Array(arr)[0]} index = lookup_array.find_index(value) return output_array[index] if index #raise only after exact check fails, we only need sorted for approx lookup raise "lookup_array must be sorted" unless lookup_array.sort == lookup_array #better way to check sorted? first_greater_index = lookup_array.find_index{|look| look > value} || lookup_array.count less_than_index = first_greater_index - 1 less_than_index >=0 ? output_array[less_than_index] : nil end
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
@param rate is the rate of discount over the length of one period. @param *values are 1 to 29 arguments representing the payments and income. @return net present value of an investment @see http://office.microsoft.com/en-sg/excel-help/npv-HP005209199.aspx Specification
# File lib/excel_functions/financial.rb, line 8 def npv(rate,*values) #If n is the number of cash flows in the list of values, the formula for NPV is: # sum upto n (values[i]/(1+rate)^i) values.each_with_index.inject(0){|sum,(val, index)| sum + val/((1.0+rate)**(index+1))} end
Calculates the payment for a loan based on constant payments and a constant interest rate.
@param rate is the interest rate for the loan. @param nper is the total number of payments for the loan. @param pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal. @param fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. @param type is the number 0 (zero) or 1 and indicates when payments are due. Set type equal to 0 or omitted If payments are due At the end of the period or 1 If payments are due At the beginning of the period @return [Float] payment for a loan based on the inputs @see http://office.microsoft.com/en-sg/excel-help/pmt-HP005209215.aspx Specification @see http://answers.yahoo.com/question/index?qid=20080822070859AAY94ZT formula using first 3 params @see http://www.getobjects.com/Components/Finance/TVM/formulas.html Full Formula
# File lib/excel_functions/financial.rb, line 28 def pmt(rate,nper,pv,fv =0,type = 0) #- pv/((1 - (1 / (1 + rate)**nper )) / rate) pv = -pv k = [1,1+rate][type] -(pv + (pv - fv)/((1+rate) ** nper - 1))* -rate/k #added some extra neg signs to get it to work end