Module: ExcelFunctions

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

Overview

file containing the financial functions

Constant Summary collapse

VERSION = 
"0.0.4"

Instance Method Summary collapse

Instance Method Details

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

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.

Parameters:

  • value —

    the value to be searched for

  • lookup_array (Array) —

    the array in which the value is looked for, and possibly the result is fetched from

  • output_array (Array) (defaults to: nil) —

    If given, the result is fetched from this array

Returns:

  • Returns the value in the output_array or the last column of the lookup_array from the same position in the result_range

  • 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(nil in our case).

  • 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 Also:



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# 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

#npv(rate, *values) ⇒ Object

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).

Parameters:

  • rate —

    is the rate of discount over the length of one period.

  • *values —

    are 1 to 29 arguments representing the payments and income.

Returns:

  • net present value of an investment

See Also:



8
9
10
11
12
 
# 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

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

Calculates the payment for a loan based on constant payments and a constant interest rate.

Parameters:

  • 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 (defaults to: 0) —

    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 (defaults to: 0) —

    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

Returns:

  • (Float) —

    payment for a loan based on the inputs

See Also:



28
29
30
31
32
33
34
35
 
# 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