5g: CorkscrewAvgBalIntλ()

Description

CorkscrewAvgBalIntλ() creates a corkscrew that applies an interest rate the the average of opening and closing balances. Interest is added to close.

CorkscrewAvgBalIntλ( InterestRate, OpeningBalance, Additions, [PositiveOnly])

InterestRate

(Required) The APR divided by the number of periods in a year.

OpeningBalance
(Required) First period's opening balance.

Additions
(Optional) One or more rows (VSTACKed if more than 1 row) of period values to add to close. Default = 0.

PositiveOnly
(Optional) TRUE = display flows as positive values. Opens and Closes are not affected. Default is FALSE.

NOTE! All additions (flows) are added. To subtract a flow make it negative.

To know this function is working as intended, add a formula that averages the opening and closing balance and then applies the rate (see animation).

=AVERAGE( TAKE(<opening>, 1), TAKE( <closing>, -1) * APR / 12

Then add a formula that checks that the absolute value of AvgBalIntλ's result less the check function's result is within tolerance.

=ABS( <check row#> - CHOOSEROWS( <corkscrew array#>, -2)) < 0.001

Page updated

Google Sites

Report abuse