5g: ScheduleRatesλ()
Description
ScheduleRatesλ() places values from a schedule table (or array) in a timeline's periods. The rate continues for each period after until another rate begins. This can be used for interest rates, pricing, discounts, etc.
Syntax
ScheduleRatesλ( PeriodEndDates , RateStartDates , Rates, [Diagnostics] )
PeriodEndDates (Required)
A row of period end dates (timeline).
RateStartDates (Required)
A row or column rate effective dates in any order
Rates (Required)
A row or column of rates associated with RateStartDates.
Diagnostics (Optional)
If TRUE, input errors that can occur after formula entry will display as diagnostic messages. The default is FALSE. It is recommended that models have a named range called Diagnostics and this named range be set to TRUE during model development and then set to FALSE when the model is placed into production.
Must be period END dates
Example
In this example we are scheduling when rent rates change. On the left we have a table of effective dates and rents. Over our component is a timeline of period end dates. The order of entries in the table is unimportant. The rates and effective dates can be in a horizontal layout. No table is needed but we lose the dynamic advantages of structured references.
Remarks
This is a fairly trivial routine created primarily as a specific-use-formula-wrapper for those new to Excel as it explicitly states its purpose. More experienced modelers may choose to use XLOOKUP as shown below to achieve the same result:
=XLOOKUP(PeriodEndDates ,RateStartDates ,Rates,"",-1)
For our model to be accurate, our timeline's period interval must be as frequent or more frequent than the rate at which rates can change. In other words, if 2 or more rates are schedule for the same period in the timeline, only the last will be scheduled.