5g: Amortizeλ()

Description

Beyond Excel provides a suite of functions to produce loan amortization schedules. They include:

Each function produces a dynamic array which means we place the function in one cell and it automagically 'spills' all values to all other cells needed to present results. This makes producing amortization schedules incredibly fast and flexible.

Each function can read entire table columns which means we can have a table of several loans and our one function, in one cell, will produce all results for all loans defined in the table.

The Amortizeλ( ) function adjusts to our model's timeline, whether that be in years, months, or weeks. It also places the results with respect to the model timeline's start date, and the loans start date so loans can start anywhere within the timeline, or even outside the timeline.

This will be available in the Financial Starter Pack on Eloquens.Com soon. 

Syntax

Amortizeλ( Principals, APRs, Terms, StartDates, [Timeline])

Principals    
(Required) One or more Loan/debt amounts

APR
(Required) Annual Percentage Rates for interest.

Term
(Required) Number of years for loans/debts.

Timeline
(Optional) The model's timeline. If omitted, the system assumes a timeline based on the earliest start date to maximum end date in months.

NOTE! Want to see this vertically? Place this function inside TRANSPOSE( ).


LabelAmortizeλ( LoanNames)

LoanNames    
(Required) One or more loan names or brief descriptions


SumAmortizeλ( AmoritizationSchedule)

AmoritizationSchedule
(Required) An array produced by Amortizeλ()

Example 1

This example is similar to traditional methods like FAST, where all the "ingredients" are listed on the left in individual rows and the calculations are to the right under a timeline.

Example 2

This example is consistent with table based modeling where items which repeat, like the number of loans, are placed in tables. Tables are maintenance free dynamic named ranges which dynamic arrays can use to size themselves to however many items are listed in the table.