Module: ExcelFunctions

Included in:
Excel
Defined in:
lib/excel_functions/lookup.rb,
lib/excel_functions.rb,
lib/excel_functions/version.rb,
lib/excel_functions/financial.rb

Overview

file containing the financial functions

Constant Summary collapse

VERSION = 
"0.0.2"

Instance Method Summary collapse

Instance Method Details

#lookup(value, lookup_array, output_array = nil) ⇒ Object

attempt to implement www.techonthenet.com/excel/formulas/lookup.php



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/excel_functions/lookup.rb', line 4

def lookup(value, lookup_array, output_array = nil)
  #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.
  #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]}

  #If the values in the LOOKUP_range are not sorted in ascending order, the LOOKUP function will return the incorrect value.
  raise "lookup_array must be sorted" unless lookup_array.sort == lookup_array #better way to check sorted?

  index = lookup_array.find_index(value)
  return output_array[index] if index

  # 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.
  # If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A.
  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

#npv(rate, *values) ⇒ Object

attempt to implement office.microsoft.com/en-sg/excel-help/npv-HP005209199.aspx



4
5
6
7
8
 
# File 'lib/excel_functions/financial.rb', line 4

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

#pmt(rate, nper, pv, fv = 0, type = 0) ⇒ Object

office.microsoft.com/en-sg/excel-help/pmt-HP005209215.aspx Calculates the payment for a loan based on constant payments and a constant interest rate. Rate is the interest rate for the loan. Nper is the total number of payments for the loan. Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal. 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. Type is the number 0 (zero) or 1 and indicates when payments are due. Set type equal to If payments are due 0 or omitted At the end of the period 1 At the beginning of the period full formula at www.getobjects.com/Components/Finance/TVM/formulas.html first 3 params at answers.yahoo.com/question/index?qid=20080822070859AAY94ZT



22
23
24
25
26
27
28
29
 
# File 'lib/excel_functions/financial.rb', line 22

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