5g: Financial Library

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

GIST: https://gist.github.com/CHatmaker/472f65269519a0937b03f741e9271334

Here is what is included in the Financial Library.

• Allocateλ Divide and distribute one or more amounts equally equally across smaller periods.

• AvgBalIntλ Calculates interest on the average of opening and closing balances.

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

• Growthλ Project growth or decline factors that can be applied to values to increase or decrease them.

• 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

• PMTAλ Calculate even monthly payments for commercial loans (Actual/360, Actual/365, Actual/Actual)

• Reversalλ Create a row that reverses input values in the next period.

• SumContainsλ Filter and total an array by another array that contains specific text.

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

AMORTIZATION SUITE - simple consumer installment loans

• Amortizeλ Creates a corkscrew amortization schedule.

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

• LabelAmortizeλ Create row labels for Amortizeλ result

CORKSCREW FUNCTIONS - calculations where prior period's closing balance becomes current period's opening balance

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

• CorkscrewRxOλ Creates a corkscrew where a rate is applied to the opening balance and added with other flows to closing balance

• CorkscrewReversalλ Creates a corkscrew where closing balance of n prior periods is reversed and added with opening and flows to closing balance

• CorkscrewAvgBalIntλ Creates a corkscrew that applies a rate to the average of opening and closing balances. Interest is added to close. 

DEPRECIATE SUITE - complete depreciation schedules

• 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

 SUB-FUNCTIONS - Functions used by the main functions and not intended for standalone use. 

• FitInTimelineλ Positions and adjusts an array to fit in a model's timeline.

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

• PeriodDiffλ Determine the number of periods between two dates (like DATEDIF() but better).