5g: Function Libraries

Below are several function libraries, some available now, some soon. They provide practical examples of 5G functions. These libraries, referred to as modules by Microsoft's Advanced Formula Environment, are available free from GitHub and packaged for sale with examples and videos through Eloquens.com. 

Here are some features common to BXL's 5G functions.

Array Essentials Library
This library contains 5G functions for working with arrays in any industry (not just financial).

• AvgColsλ Calculates averages for each column in a dynamic array.

• AvgRowsλ Calculates averages for each row in a dynamic array.

• CountAColsλ Get the count of everything of each column.

• CountARowsλ Get the count of everything of each row.

• CountCλ Count how many of one or more characters are in a text string

• CountColsλ Get the count of numbers in each column.

• CountRowsλ Get the count of numbers in each row.

• IsBetweenλ Determine if a value is between a lower and upper limit.

• IsInListλ Determine if a value is one of a list of items.

• MaxColsλ Calculates maximum for each column in a dynamic array.

• MaxRowsλ Calculates maximum for each row in a dynamic array.

• MinColsλ Calculates minimum for each column in a dynamic array.

• MinRowsλ Calculates minimum for each row in a dynamic array.

• RangeToDAλ Convert a range reference to a dynamic array reference

• SumColsλ Creates totals for each column in a dynamic array.

• SumRowsλ Creates totals for each row in a dynamic array.

Finance Starter Library
This library provides a sample of 5G functions for the financial modeling industry. Each functions works nicely with dynamic arrays. This is best used with the Date library. Here is what is included in the Finance Library.

AMORTIZATION (Simple Loans) SUITE

• Amortizeλ Creates a corkscrew amortization schedule.

• SumAmortizeλ Create row totals for Amortizeλ's results.

• LabelAmortizeλ Create row labels for Amortizeλ result

DEPRECIATE SUITE

• Depreciateλ Create a block of CAPEX, Opening Balance, Depreciation Values, and Book Value for each asset

• SumDepreciateλ Create row totals for Depreciateλ's results.

• LabelDepreciateλ Create row labels for Depreciateλ result
  
  

MAIN FUNCTIONS - 

• Corkscrewλ Creates a simple corkscrew where the closing balance is the sum of independent flows plus opening balance

• Cumulativeλ Creates a row or column of cumulative totals from a total row or column

• IntOnIntλ Calculates how much we need to borrow, in addition to principal, when we also need to borrow the interest 

• IRRλ Calculates IRR, correcting for when the first investment is not in the first period

• Movementλ Create a row of differences from column to column

• RollingSumλ Creates totals for preceding values of a set size moving from beginning to end over a row of values.

SUMMARIES - Functions that create totals for the main functions

• SumContainsλ Creates a row of totals for each row in an array where its labels contain a unique letter, word, or phrase.

• SumPeriodsλ Groups and totals all columns in a dynamic array by period resulting in one column for each period.

 SUBROUTINES - Functions used by the main functions

• FilterContainsλ Filter an array by another array that contains specifice text

• RangeToDAλ Create a dynamic array from a static range

• TimelineOffsetλ Determines how many columns a date is offset from a timeline's first date

• TimelinePositionλ Places an array or value appropriately within a model's timeline.

Date Module
This module provides 5G components for scheduling things. This is great for financial models, budgets, projects, rentals, etc. It includes these components.

• CountDOWλ Count instances of a specific week day within two dates. Are you closed Sundays? How many Sundays are there next month?

• CountDOWInMonthλ Count instances of a specific week day within a month. Will the plant run Saturdays in July? How many Saturdays is that?

• IsBetweenλ Determine if a value is between a lower and upper limit. Why Excel doesn't have this is a mystery. 

• IsOccurrenceDateλ Determine if a date passed is when a potentially repeating event happens. I use this for budgeting and projects.

• OverLapDaysλ Return how many days overlap two period ranges. Rent by the day but bill by the week? This can help with that.

• PeriodLabelλ Creates a label for a date based on period interval. Want reports with easy to read period headings? Use this.

• Periodsλ Determine the number of periods from date1 to date 2 inclusive. This is like the deprecated =DateDif() but better.

• ScheduleRatesλ Schedules rates in a timeline from a start date until a new rate replaces it. Use this for rental rates, commission rates, etc.

• ScheduleRatesByItemsλ Schedules rates in a timeline from a start date until a new rate replaces it for each item in a list. 

• ScheduleValuesλ Schedules values in a timeline from a scheduled date table. Need to place asset acquisition costs in a timeline?

• ScheduleValuesByItemsλ Schedules values in a timeline from a scheduled date table for each item in a list. Want to keep each asset in its own row?

• Timelineλ Creates a horizontal list of start or end dates for a timeline. Timelines made easy and flexible.

Multi-dimensional Modeling Module

This module was inspired by a challenge issued to the word's financial modeling community by Paul Mireault. The traditional modeling approaches struggled to solve his puzzle because spreadsheets are two dimensional. Even so, this problem was solved in databases decades ago with the n-fold cartesian product, which is a way of making all possible distinct combinations of various dimensions. This is best used in table based modeling methodology.