Pro Comp

The 'Pro Comp' Edition

Home-Web Site
The Real Estate Compatibility Edition is the Real Estate Edition plus a compatibility module for previous users of the Blib Library.

The Pro Comp Edition contains 410 components in the following categories, families and components:

Date and Time Calculations Category
Calculations on Dates and TimePeriods

The Date and Time category is about manipulating and presenting dates, in particular:

  • Date Information, which is about information about dates (eg IsLeap), representing dates (eg DYear), simple date identities (eg DaysInMonth), and week numbers (eg WeekNumberToDate).
  • Date Difference, which is about determining the time difference between two dates, either in a basic way with functions like DiffY and DiffM, or a more detailed way (using daycount) with functions like DiffYX and DiffMX.
  • Date Increment, which is about finding the next or previous date, with reference to another date and a time difference or annual date sequence (see 'Annual Date Sequences').
  • Time Period, which is about determining the length of a time period (in Business Functions time periods are specified by two variables Time and Base). The functions in this family are similar to those in the Date Difference family.

Date Information Family
This is the most general Dates family, with functions that calculate date values and functions that provide information about a date value.
This family of date functions deals with:
  • Information about dates, for example DayName, MonthName and IsLeap.
  • Ways to represent dates, like DYear, DateYMD, DMonth and DateNum.
  • Simple Month calculations. for example determining the last day in the month (MonthEnd) or the days in the month DaysInMonth.
  • Week Number calculations including WeekNumber, which will convert a date to either an ISO week number or an Excel WEEKNUM week number (the basis is different), and WeekNumberToDate, a rather unique function which does the inverse, ie converts a week number to a date.

About Dates in Business Functions
This family is the first and introductory family of the Date and Time Calculations category, and some of its functions touch on how dates are stored, used and represented in Excel and Business Functions. Here are some key points:
  • A date is the number of days elapsed since 1/1/1900, where 1/1/1900 is deemed to be date number 1 and the year 1900 is assumed by Excel (erroneously, as it turns out) to have 365 days. There is another date system called the 1904 date system that is not recommended.
  • Business Functions stores a date in a number of ways. It actually stores the date number as well as the year, month and day (which it remembers), and a thing called a decimal month. It stores the decimal month (the fraction of months elapsed since 1900 where fractions of a month are treated as DaysElapsed/DaysInMonth because that turns out to be useful in calculations and is the default daycount method in BF.
  • A date is actually the instant after midnight on the day in question ie first thing in the early morning. Its important to remember this when dealing with dates - that a date is not really a day but a very precise point in time at the very start of the day. In Excel, 6am on a 1 Jan 2000 would be represented as 36526.25, which tells you how Excel deals with times. BF, on the other hand, does not consider times at all - it just has 1 Jan 2000 as 36526 and 2 Jan as 36427.
  • Business Functions uses Annual Date Sequences a lot to describe annually recurring dates such as are used in Periods variable. An annual date sequence is just a range (or array) of a few numbers in the format mm.dd (month.day), for example {01.01, 07.01} describes 1st Jan, 1st July. One of the functions in this family, DateSeqNum, actually creates a number in this format, given a date, so 1 July becomes 07.01.
DYear (TheDate)Determine the decimal year for a date
DMonth (TheDate)Determine the Decimal Month of a date
DateYMD (Yr, Mnth, Day)Determine a Date Number given Years,Months,Days
DateSeqNum (TheDate)Convert a Date to an Annual Sequence Number
DayName (TheDate)Name of the Day of the Week
MonthName (TheDate)Name of the Month
IsLeap (TheDate)Determine if the year is a leap year
DaysInMonth (TheDate)How many Days in the Month
IsAnniversary (Date1, Date2)Determine if one date is an anniversary of the other
WeekNumber (TheDate, StartOfWeek, [StartOfWeek1])Week Number
WeekNumberToDate (WeekNum, StartOfWeek, [StartOfWeek1], Yr)Convert a Week Number to a date
MonthStart (TheDate)MonthStart
MonthEnd (TheDate)MonthEnd
AnnSeq (AnnSeqNum, NumPmtsPerYr, [EOMRule])Generate an annual date sequence
DateNum (TheDate)Return the Date Number of a date
Date Difference Family
This family works with differences between two dates.
The Date Difference Family is for calculating the difference between two dates, in either months or years.

The four functions in this family have two mandatory arguments (Date1 and Date2, the dates, which will as much as many of us need to know for determining the number of years, months or days between two dates.

The final two function arguments are concerned with how the date difference is calculated. Believe it or not, there are a myriad of ways to do this (See Using Daycount) and all of us are familiar with a couple of them (Actual/365, 30/360 etc).

If you default DayCount and Periods then the function will use Business Functions ACTM/ACTM decimal month method, a method unique to us but very broadly useful. It simply says that a month is always 1/12 of a year and the fraction of a month is the number of days elapsed in that month divided by the number of days in the month. It's simple and useful for a wide range of business applications, does not suffer from the inaccuracies that any of ACT methods can have when dealing with periods of several years, and is more comprehensible than 30/360 (which has at least two sub-definitions within it).

The purpose of DayCount and Periods being there is, of course, so that you can use them to firmly determine how the date difference should be calculated, and here you have the full panoply of daycount options available to you.

The DiffD function sometimes causes controversy - surely the difference in days is just one Date less the other? Well, the answer is no and it can be demonstrated by looking at say 30/360 mode where there are strict rules in any standar text book on for how you establish the difference in days between the 2 dates, prior to dividing by 360.

The most common and to many the most perplexing daycount method, ACT/ACT (in period) is also available in these functions, and is the daycount method used in quite common business situations like rent and bond (but not necessarily loan) interest. This method determines the number of periods and fractional periods (done on a strict day total basis) between 2 dates. Then, if you want to turn that number of periods into a fraction of a year, you divide by the number of periods in a year. This is what the function DiffP does, and this function really exposes what ACT/ACT (in period) is all about. So if you wanted the number (including fractions) of rental payments between two dates, or the accrued bond interest, you can use a function as simple as DiffP before resorting to specific functions like Interest or Con.
DiffY (Date1, Date2, [DayCount], [Periods])Difference in Years
DiffM (Date1, Date2, [DayCount], [Periods])Difference in Months
DiffD (Date1, Date2, [DayCount], [Periods])Difference in Days
DiffP (FromDate, ToDate, AnnDateSeq)Number of periods between two dates
DiffPSeq (FromDate, ToDate, AnnDateSeq)Number of periods between two dates
DiffYX (Date1, Date2, [DayCount], [Periods])Difference in Years, extended version
DiffMX (Date1, Date2, [DayCount], [Periods])Difference in Months, extended version
DiffDX (Date1, Date2, [DayCount], [Periods])Difference in Days, extended version
Date Increment Family
This family increments dates by specified amounts of time.
The Date Increment Family deals with adding or deducting periods of time to a date, or incrementing a date according to an annual sequence of dates. The functions here are often used for a whole row or column of a spreadsheet, to create a list of dates, or timebase, for a model. The functions can be grouped as follows:
  • Basic date incrementing is done by DpY and DpM, and these are very popular functions. These can frequently used without worrying about the two optional arguments DayCount and Periods, but you can use the full panoply of daycount options should you wish to specify more exactly how you want the date incremented, see Using Daycount.
  • Incrementing a date from a date sequence is done by NextDateSeq and PrevDateSeq. These functions find the next previous date by reference to an annual date sequence (for example {6.15,12.15} for 15th July, 15th December) (see Annual Date Sequences for more details). Note that you can also use a BusDaySwitch as part of the annual date sequence if you want to use a particular business day convention to ensure your date does not fall on a holiday.
  • The DateSeq array function takes the use of date sequences to generate a list of ascending dates a step further, calculating them in a single Array Function.
  • Finally NextDateCombine function and the DateSeqCombine array function use the Combine feature to allow the use of several date sequences, which the function will 'merge' internally and create the next date in the sequence by taking the earliest of these.
DpY (TheDate, Yrs, [DayCount], [Periods])Date Plus Years
DpM (TheDate, Months, [DayCount], [Periods])Date Plus Months
DpD (TheDate, Days, [DayCount], [Periods])Date Plus Days
PrevDateSeq (TheDate, AnnDateSeq, [SpecificDates])Determine the Previous Date in an annual sequence
NextDateSeq (TheDate, AnnDateSeq, [SpecificDates])Next Date in the Sequence
PrevDateList (TheDate, [SpecificDates])Return the previous date from the list
NextDateList (TheDate, [SpecificDates])Return the next date in the list
NextDateCombine (TheDate, [AnnDateSeq1], [SpecificDates1], [AnnDateSeq2], [SpecificDates2], [AnnDateSeq3], [SpecificDates3], [AnnDateSeq4], [SpecificDates4])Calculates the next date using up to 4 or more possible date streams
DatePer (WhichOne, FirstPmtDate, Interval)Choose a date from a Periodic series of dates
DateSeq (FromDate, ToDate, AnnDateSeq, [SpecificDates])Build a Date Sequence
DateSeqCombine (FromDate, ToDate, [AnnDateSeq1], [SpecificDates1], [AnnDateSeq2], [SpecificDates2], [AnnDateSeq3], [SpecificDates3], [AnnDateSeq4], [SpecificDates4])Combines up to 4 lists of dates
DpYX (TheDate, Yrs, [DayCount], [Periods])Date Plus Years, extended version
DpMX (TheDate, Months, [DayCount], [Periods])Date Plus Months, extended version
DpDX (TheDate, Days, [DayCount], [Periods])Date Plus Days, extended version
Time Period Family
Calculations on TimePeriods
The Time Period family deals with determing the length of a time period. In Business Functions a timeperiod is specified by two variables Time and Base.
Similar to the Date Difference family, you can either let DayCount and Periods default by just missing them out, or you can use the daycount options to accurately calculate the length of a timeperiod.
  • TpY, TpM and TpD use the usual method of defininga timeperiod using Time and Base, where Time is the date at the start of the timeperiod and Base is either the length of the timeperiod, in months, or the date signifying the end of the timeperiod.
  • A word or two about using Base to signify the end of the timeperiod: Business Functions' approach to start and end dates is to 'include the first, exclude the last'. This is consistent with what you do intuitively when to determine the number of days between, say the 3rd of the month and the 15th of the month to give 12 days. If you were including both dates the answer would be 13. This method also accords with what a day number in Excel really is - it's a point in time representing the microscopic instant after midnight on that day - not noon or the end of the day. Finally it's consistent with the way banks usually calculate interest. You will notice that in some of the more sophisticated projections functions in the library that involve a variable called ProjMode that you can, in these functions, optionally include both dates for the purposes of accruing something.
  • There is also in this family a very useful function that just determines if an event occurs in a particular time period, called Occurs, that is often used within some of the other Business Functions.
TpY (Time, Base, [DayCount], [Periods])Years in Timeperiod
TpM (Time, Base, [DayCount], [Periods])Months in timeperiod
TpD (Time, Base, [DayCount], [Periods])Days in timeperiod
Occurs (Time, Base, EventDates)Determine if an event occurs in a time period
TpYX (Time, Base, [DayCount], [Periods])Years in Timeperiod, extended version
TpMX (Time, Base, [DayCount], [Periods])Months in timeperiod, extended version
TpDX (Time, Base, [DayCount], [Periods])Days in timeperiod, extended version
Special Dates Category
Date calculations using or taking account of special days in the calendar such as holidays.

The Special Dates category is about functions principally that take into account or calculate holidays.

Business Days Family
Calculations that take into account Business Days.
The Business Days family deals with functions that explicitly take into account Business Days (see List of Holidays). It does this using a variable called BusDaySwitch, although any function that accepts an annual date sequence (see Annual Date Sequences) can also accept a BusDaySwitch within the date sequence. The Business Days family itself is quite large:
  • The basic building block is the IsBD function, which determines if a day is a business day or not. You can specify whether to exclude just weekends or particular National Holidays. Using the optional arguments AnnHols and HolDates you can specify almost any type of holiday scenario. AnnHols is an annual date sequence of recurring holidays, and HolDates is a list of specific holiday dates.
  • Like the Date Difference and Date Increment families you can either choose to let the optional ØDayContØ and Periods default or specify a specific daycount method to govern how a date difference is computed (See Using Daycount for more details). Functions like DpYBD, DpMBD and DpDBD are used to increment dates so that the resulting date falls on a business day. In other words, first of all the function calculates the target date, and if that date falls on a non-business day it will slip the date forward until it encounters the first business day (note that you can't slip the day backwards).
  • For determining the next or previous date, with reference correspondingly to a specific time interval, a sequence of dates or list of dates, there is NextBD and PrevBD, NextDateSeqBD and PrevDateSeqBD, and NextDateListBD and PrevDateListBD.
  • Finally there is NetWorkDaysBF - a function like Excel's own NetworkDays, which determines the number of work days between two dates (including the two end dates). The differences with the Business Functions version is that whereas Excel's function assumes which days of the week are weekends, the BF function allows you to specify. Also, the BF function allows you to specify an annually recurring date sequence of holidays (non-work days), and, via a BusDaySwitch, a bank holiday convention (eg US, UK etc).
DpYBD (TheDate, Yrs, [BusDaySwitch], [AnnHols], [HolDates], [DayCount], [Periods])Date plus years ensuring the date determined is a Business Day
DpMBD (TheDate, Months, [BusDaySwitch], [AnnHols], [HolDates], [DayCount], [Periods])Date plus Months (Business Days)
DpDBD (TheDate, Days, [BusDaySwitch], [AnnHols], [HolDates], [DayCount], [Periods])Date plus days allowing for Business Days
NextBD (TheDate, [BusDaySwitch], [AnnHols], [HolDates])Determines the date of the next Business Day
PrevBD (TheDate, [BusDaySwitch], [AnnHols], [HolDates])Previous Business Day
NextDateSeqBD (TheDate, AnnDateSeq, [SpecificDates], [BusDaySwitch], [AnnHols], [HolDates])Determine the next date in an annual sequence, slipped to the next Business Day
PrevDateSeqBD (TheDate, AnnDateSeq, [SpecificDates], [BusDaySwitch], [AnnHols], [HolDates])Determine the previous date in an annual sequence, slipped to the next Business Day
IsBD (TheDate, [BusDaySwitch], [AnnHols], [HolDates])Determines if the date is a Business Day
NetWorkDaysBF (FromDate, ToDate, [WeekendDays], [AnnHols], [HolDates], [BusDaySwitch])Days Elapsed, not counting holidays including the two end dates
NextDateListBD (TheDate, [SpecificDates], [BusDaySwitch])Next date in list, taking into account Business Days
PrevDateListBD (TheDate, [SpecificDates], [BusDaySwitch])Return the previous date from the list, taking into account Business Days
NetWorkDaysX (FromDate, ToDate, [WeekendDays], [AnnHols], [HolDates], [BusDaySwitch])Days Elapsed, not counting holidays including the two end dates
PrevBDX (TheDate, [BusDaySwitch], [AnnHols], [HolDates])Previous Business Day, extended version
PrevDateSeqBDX (TheDate, AnnDateSeq, [SpecificDates], [BusDaySwitch], [AnnHols], [HolDates])Determine the previous date in an annual sequence, slipped to the next Business Day, extended version
DpYBDX (TheDate, Yrs, [BusDaySwitch], [AnnHols], [HolDates], [DayCount], [Periods])Date plus years ensuring the date determined is a Business Day, extended version
DpMBDX (TheDate, Months, [BusDaySwitch], [AnnHols], [HolDates], [DayCount], [Periods])Date plus Months (Business Days), extended version
NextDateSeqBDX (TheDate, AnnDateSeq, [SpecificDates], [BusDaySwitch], [AnnHols], [HolDates])Determine the next date in an annual sequence, slipped to the next Business Day, extended version
DpDBDX (TheDate, Days, [BusDaySwitch], [AnnHols], [HolDates], [DayCount], [Periods])Date plus days allowing for Business Days, extended version
IsBDX (TheDate, [BusDaySwitch], [AnnHols], [HolDates])Determines if the date is a Business Day, extended version
NextBDX (TheDate, [BusDaySwitch], [AnnHols], [HolDates])Determines the date of the next Business Day, extended version
Holidays Family
Calculates the occurence of holidays.
The Holidays family is a collection of functions that give the date of a particular holiday given the year. Currently the national holidays of the UK, USA, Canada and Australia are catered for (see List of Holidays for details). This diverse family of functions includes the following:
  • Holiday gives you the date of a particular holiday in a specified year, using the holiday number in the List of Holidays, so for example you can determine the date of Washingtons Birthday for any particular year. DescribeHol simply accepts a holiday number and tells you in words what that holiday is (useful as a check on the spreadsheet).
  • WhatHol tells you in words what, if any, is the holiday of the specified date and business day convention (BusDaySwitch). So you can find out if a date is a Saturday, Sunday, or Bank Holiday directly. Similar to this there is IsHol, which simply tells you true or false whether a particular date is a holiday or not, again according to the specified business day convention. Even more basic is IsWE, which simply says whether a date is on a weekend or not (optionally allowing you to specify which days of the week are weekend days).
  • WesternEaster is the only function for determining a specific holiday, using an established algorithm to calculate the date of Easter in a given year.
  • CreateDate determines generically a holiday from first principles such as '3rd Friday in February', and indeed this function is used internally to generate the holidays specified by BusDaySwitch. It only has one compulsory variable, the YearOrDate in which you want to create the holiday date. Thereafter you can optionally specify the MonthInYearOpt and DayInMonthOpt. Then, the rules by which the date is determined are given. OccurrenceNo and DayinWeekOpt describes the part of a rule like '3rd Friday'. Relativity provides the second part of the rule, whether it should be relative to the month start eg (3rd Friday in March), or relative to Easter (3rd Friday after Easter). The other parameters define the rest of the rule. FirstYear optionally specifies the first year where this date occurs, RecurringYrs how often it recurs,and MoveWeekend, whether the date moves forwards or back if it falls on a weekend.
Holiday (HolNum, YearOrDate)Date of the Holiday in question
DescribeHol (HolNum)Describes the holiday in question
WhatHol (TheDate, [BusDaySwitch])What the name of the holiday is on the date in question
IsHol (TheDate, [BusDaySwitch])Whether TheDate is a holiday or not
IsWE (TheDate, [WeekendDays])IsWeekend
WesternEaster (YearOrDate)Western Easter
CreateDate (YearOrDate, [MonthInYearOpt], [DayInMonthOpt], [OccurrenceNo], [DayinWeekOpt], [Relativity], [FirstYear], [RecurringYrs], [MoveWeekend], [AfterDate])Creates a date using specified assumptions and constraints
CalcDateX (YearOrDate, [MonthInYearOpt], [DayInMonthOpt], [OccurrenceNo], [DayinWeekOpt], [Relativity], [FirstYear], [RecurringYrs], [MoveWeekend], [AfterDate])Calculate a date, extended version
CalcDate (YearOrDate, MonthInYear, DayInMonth, [FirstYear], [RecurringYrs], [MoveWeekend], [AfterDate])Calculate a date
Discrete Amount Projections Category
Project one or a series of amounts that are paid at discrete times

Discrete Amount Projections are about projecting single or multiple values, or payments, in a timebase. In contrast to projections about rates (eg Constant Rate, Stepped Rate), Discrete Amounts are more like one-off lumps that occur at specific times. There are three families of functions within the Discrete Amount Projections category:

  • The Payments family has functios for inserting one or more payments in a projection. These range from the simple MkPmts to many more advance variations, such as those that grow the payments according to a forecast (MkPmtsGrow, MkPmtsFcst), those that introduce a time delay (MkPmtsLag, MkPmtsLagProf) and those that take out a payment from one part of the projection and put it in another (RePhasePmt, RePhasePmts).
  • The Payments Periodic family has functions like the Payments family but that repeat periodically after set time intervals. For example there is MkPmtsPer, which takes a ØPmtIntervalØ in months, and MkPmtsGrow, which does the same thing but grows the payment every time it is applied.
  • The Payments Market family uses growth and forecasts like the Payments family, but in a more subtle way. It uses a value from a forecast if no value has been specified against a particular date. So MkPmtsFcstMkt will simply apply it's Pmts against the PmtDates unless a payment is missing or zero, in which case it will substitute in its place a value from the forecast.

Payments Family
Functions that make one or more discrete payments in a timeperiod.
The Payments family is about making specific payments in a timeperiod. Although all the functions are described in the plural (eg MkPmtss) they also worki for single payments, just by putting in one value instead of a range.
  • The basic version (e.g. MkPmts) does a simple lookup to see if a payment is required in the timeperiod and makes it accordingly.
  • The Fcst feature is added to MkPmts, to create MkPmtsFcst. This function looks to see if payment(s) are required in a timeperiod and determines their value(s) by reference to values using a forecast provided.
  • The Grow feature is also added to the basic MkPmts function, to create MkPmtsGrow. This function looks to see if payment(s) are required in the timeperiod and determines their value(s) by taking a base level of payment and growing it according to one or more percentage growth rates.
  • The SF feature is added to the basic MkPmts functions to create MkPmtsSF. This function looks to see if payment(s) are required in the timeperiod, but will only make the payment(s) provided the payment date(s) are within the specified Start and Finish date.
  • RePhasePmts deducts payment(s) from one part of the cashflow and adds the same payments(s) into another part, at a different time.
  • MkPmtsLim makes payments up until a certain cumulative limit CumLimit is met or exceeded (useful in budgeting).
  • Two functions deal with the issue of lagging payments. MkPmtsLag applies a simple lag, in months (MonthsLag). MkPmtsLagProf is more sophisticated and potentially more useful when modelling delays, accounts receivable, etc, using two ranges MonthsLag and Proportions to determine how the payments are delayed according to a time profile.
  • There are also 2 functions that deal with the input of multiple ranges, the straightforward MkPmtsX which allows a number of ranges for a basic MkPmts, and MkPmtsLagX, dealing with the simple lagged situation.
  • There are some functions which are a slight departure from the simple MkPmts kind of projections function. There is SumBetween, which totals up all the payments between certain dates. There is also the very important and useful MatchPmts, which is like a MkPmts except the payment date must match the date in MatchPmts, not just occur in a specified a time period.
  • Finally there are two functions concerned with carrying forward negatives (or positives) until the overall aggregate turns positive (or negative), such as might be used for tax calculations where losses are carried forward. For carrying forward negatives in this there is CarryNeg, and also the reverse, CarryPos.
MkPmts (Time, Base, PmtDates, Pmts)Make several Payments
MkPmtsGrow (Time, Base, PmtDates, Pmts, GrowthDates, GrowthRates)Make one or more Payments that Grow
MkPmtsFcst (Time, Base, PmtDates, FcstVals, StartFcst, FcstBase)Make one or more Payments according to a forecast
MkPmtsSF (Time, Base, Start, Finish, PmtDates, Pmts)Make Payments within Start and Finish
MkPmtsLim (Time, Base, PmtDates, Pmts, CumLimit)Make Payments up to a maximum Limit of cumulative Payments
MkPmtsLag (Time, Base, [LagMonths], PmtDates, Pmts, [DayCount], [Periods])Make Payments Lagged
MkPmtsLagProf (Time, Base, MonthsLag, Proportions, PmtDates, Pmts, [DayCount], [Periods])Make Payments lagged according to a profile
RePhasePmts (Time, Base, FromPmtDates, ToPmtDates, Pmts)RePhase Payments
TotPmts (PmtDates, Pmts)The Total of all the Payments
TotPmtsSF (Start, Finish, PmtDates, Pmts)Total or average of payments Start and Finish
SumBetween (FromDate, ToDate, PmtDates, Pmts, [InclusiveOpt])Sum of Payments between two dates
MatchPmts (TheDate, PmtDates, Pmts)Make Payments if the payment dates match the date in question
CarryNeg (Amounts)CarryNeg
CarryPos (Amounts)Carry forward positive balances
MkPmtsX (Time, Base, PmtDates, Pmts, [Pmts2], [Pmts3], [Pmts4], [Pmts5], [Pmts6], [Pmts7], [Pmts8], [Pmts9], [Pmts10])Make several Payments, extended version
MkPmtsLagX (Time, Base, [LagMonths], PmtDates, Pmts, [Pmts2], [Pmts3], [Pmts4], [Pmts5], [Pmts6], [Pmts7], [Pmts8], [Pmts9], [Pmts10], [DayCount], [Periods])Make Payments Lagged, extended version
MkPmt (Time, Base, PmtDate, Pmt)Make a Payment if it the payment date falls in the timeperiod
MkPmtGrow (Time, Base, PmtDate, Pmt, GrowthDates, GrowthRates)Make a Payment, but grow it first
MkPmtGrowQ (Time, Base, PmtDate, Pmt, GrowFrom, GrowthRatePA)Make a Payment, but grow it first (Simplified Version)
MkPmtFcst (Time, Base, PmtDate, FcstVals, StartFcst, FcstBase)Make a single Payment according to a forecast
MkPmtSF (Time, Base, Start, Finish, PmtDate, Pmt)Make a Payment providing it is within Start and Finish Dates
RePhasePmt (Time, Base, FromPmtDate, ToPmtDate, Pmt)RePhase a payment
MkPmtX (Time, Base, PmtDate, Pmt, [Pmt2], [Pmt3], [Pmt4], [Pmt5], [Pmt6], [Pmt7], [Pmt8], [Pmt9], [Pmt10])Make a Payment if it the payment date falls in the timeperiod, extended version
Payments Periodic Family
Functions that make discrete payments periodically at regular intervals. The family is very similar to the Payments family except that the payments repeat at intervals.
The PeriodicPayments family is about making repetitive discrete payments at regular intervals. This family enables you to time payments, for example, 30 months, that grow with inflation.
  • The basic function takes a series of payments and repeats them at periodic intervals (MkPmtsPer).
  • The Grow feature is added to the basic type to give MkPmtsGrow. These functions make the initial payment or set of payments according to the payments specified, but then subsequent payments are grown according to one or more supplied percentage growth rates.
  • The Fcst feature is added to the basic type to give MkPmtsFcst. These functions look up to see if a payment is due in a particular timeperiod, but then determine the level of the payment by reference to a forecast.
MkPmtsPer (Time, Base, AbsFinish, PmtDates, Pmts, Interval)Make a sequence of Payments Periodically
MkPmtsPerGrow (Time, Base, AbsFinish, PmtDates, Pmts, GrowthDates, GrowthRates, Interval)Make a sequence of payments periodically with growth
MkPmtsPerFcst (Time, Base, AbsFinish, PmtDates, FcstVals, StartFcst, FcstBase, Interval)Make a sequence of payments according to a forecast
MkPmtPer (Time, Base, AbsFinish, FirstPmtDate, Pmt, Interval)Make Payment Periodically
MkPmtPerGrow (Time, Base, AbsFinish, FirstPmtDate, Pmt, GrowthDates, GrowthRates, Interval)Make Payment periodically with growth
MkPmtPerFcst (Time, Base, AbsFinish, FirstPmtDate, FcstVals, StartFcst, FcstBase, Interval)Make a periodic payment according to a forecast
Payments Market Family
Project payments that determine their amount with reference to a market forecast
This family has just a few functions in it, and its aim is to do the various things that the Payments family does, but allow for the fact that sometimes you don't have a fixed value for a payment, and you would want the function to look up the value off a forecast or a market rate.

The difference between functions in this family, like MkPmtsFcstMkt, and functions in the Payments family, like MkPmtsFcst, is that in the Payments family the value of the payment is definitely read off the forecast, whereas in the Payments Market family the value of the payment is only read off the forecast if one or more of the Pmts is left blank - so it is like the function defaults to the market forecast, but you can input a specific payment instead. Note that this means the functions are particularly sensitive to zero rates (they will default to market if they see zero), so if you want to genuinely input a zero rate, make it instead a very small number (eg 1e-9).

The basic functions are MkPmtsGrowMkt and MkPmtsFcstMkt, depending on whether you want to use the a set forecast or a market rate and some growth rates.
To this are added two periodic functions (see the Payments Periodic family for more on periodic functions), MkPmtsPerGrowMkt and MkPmtsPerFcstMkt.

The way the function works, with regards to inserting market defaults where there are gaps in the Pmts, is similar to that used in those functions that deal with rates rather than one-off payments, such as in the Constant Rate Market family and the Stepped Rate Market family: if a blank or zero payment is found where there is a corresponding non-zero and non-blank date, the value of that payment is read off a forecast (so be careful if you want to blank out a payment - you have to blank out the date as well or the function will make up a payment for you).
MkPmtsGrowMkt (Time, Base, MktValue, PmtDates, Pmts, GrowthDates, GrowthRates)Make payments that are grown with reference to a market forecast
MkPmtsFcstMkt (Time, Base, PmtDates, Pmts, FcstVals, StartFcst, FcstBase)Make payments that are read off a market forecast
MkPmtsPerGrowMkt (Time, Base, Finish, MktValue, PmtDates, Pmts, GrowthDates, GrowthRates, Interval)Make payments periodically that are grown with reference to a market forecast
MkPmtsPerFcstMkt (Time, Base, Finish, PmtDates, Pmts, FcstVals, StartFcst, FcstBase, Interval)Make payments periodically that are read off a market forecast
MkPmtGrowMkt (Time, Base, MktValue, PmtDate, Pmt, GrowthDates, GrowthRates)Make a payments that is grown with reference to a market forecast
MkPmtFcstMkt (Time, Base, PmtDate, Pmt, FcstVals, StartFcst, FcstBase)Make a payment that is read off a market forecast
MkPmtPerGrowMkt (Time, Base, Finish, FirstPmtDate, MktValue, Pmt, GrowthDates, GrowthRates, Interval)Make a single payment periodically that are grown with reference to a market forecast
MkPmtPerFcstMkt (Time, Base, Finish, FirstPmtDate, Pmt, FcstVals, StartFcst, FcstBase, Interval)Make a single payment periodically that are read off a market forecast
Distributed Amount Projections Category
Project an amount that is spread over a period of time

Distributed Amount Projections are about taking a total amount for something, say a project cost, and spreading it over several timeperiods. There are several ways you could spread a cost in this way, the common ones being to spread it uniformly (ie at a constant rate using UniSpread) or to spread it according to an S-curve (SCurve), which generally concentrates most of the cost centrally in the duration of the project.

There are 4 families in the Distributed Amount Projections category:

  • The basic Time Spread family contains all the no-frills basic functions of the Distributed Amount Projections category. These start from the building block UniSpread function which simply spreads an amount uniformly, through to functions that spread amount according to a specified curve, such as SCurve and the highly generalised DistSpread. Finally there are a couple of functons, TStepSpread and FStepSpread, which spread an amount according to stepped annual rates that you specify, with the function just determining either the Start or Finish date.
  • The Time Spread Periodic family takes the basic time spread functions and allows you to repeat the spreading process at intervals, for example for maintenance upgrades, workovers and refitting.
  • The Time Spread Weighted family is for functions where you want more control over the disribution or pattern of disbursement. UniSpreadS deals with the highly specific situation of an annually recurring seasonal pattern. WeightSpread is the most general of this family, enabling you to specify any number of Weights, which are applied at equal intervals between Start and Finish to spread the overall Total. If you don't want the Weights spread equally you can use FWeightSpread or TWeightSpread.
  • The Time Spread History family is a set of time spread functions adapted for use in budgeting situations where you have historic payments (PmtsHistory) or ActualsToDate. What these functions do is a straightforward Time Spread function but, in the case of forecast payments, adjusted so that the overall Total projected is unchanged by the historic payments. This turns out to be a useful technique for budgets that are constantly updated, where you can either choose to update the PmtsHistory with the actuals (as in SCurveH), or specify the ActualsToDate (as in SCurveAdj).

Time Spread Family
This family is about spreading a Total amount over time. It differs from other kinds of projections families because the function decides what the rate or level is required (based on distributions or formulae) to disburse the Total between the Start and Finish dates.
The Time Spread family is for spreading amounts over a time interval, and it is useful for budgeting and business planning, particularly for capital projects. The functions it contains offer different ways of spreading an amount over time:
  • UniSpread does a simple uniform (flat) spread between a Start and a Finish date. This is a basic workhorse for lots of budgeting situations.
  • SCurve spreads an amount according to what is know in costing circles as an 'S curve' . An S curve is simply a distribution that concentrates the amount in the centre of the time interval, and mant kinds of distribution (normal, etc) will do this. We have preferred to use our own design of S curve, based on the integral of sin(time*skewness)+peakness which works out to be -cos(time*skewness) + peakness*time. This formula enables you to control the skewness and degree of peaking (centre concentration) of the curve. A companion function to SCurve, SCurveGrow, grows the amount prior to spreading it using an S-curve.
  • Other ways of spreading an amount over time according to a statistical distribution are offered by DistSpread. With this function you specify the Start time, MidPoint, and Finish time, and you can then spread the Total using any of the distribution types available from the variable DistType, including the Uniform, Triangular, Double Triangular, Normal and Log Normal distributions.
  • Two other functions, FStepSpread and TStepSpread, are hybrids between Time Spread functions and Stepped Rate Projections functions. They spread a Total amount according to sets of AnnualRates and either FromDates and ToDates. To get the correct Total amount disbursed, they calculate the Finish date (in the case of FStepSpread), or Start date (in the case of TStepSpread. These functions are useful in budgeting situations where you want to stipulate a specific rate of disbursement, but have an absolute amount you want disbursed which, once depleted, must not be exceeded.
UniSpread (Time, Base, Start, Finish, Total, [DayCount], [Periods], [ProjMode])Spread an amount Uniformly over time
SCurve (Time, Base, Start, Finish, Total, Skew, Peakness, [DayCount], [Periods], [ProjMode])Distribute an amount over time according to an S-Curve
SCurveGrow (Time, Base, Start, Finish, Total, Skew, Peakness, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])Distribute an amount over time according to an S-Curve, but grow it first
DistSpread (Time, Base, Start, MidPoint, Finish, Total, DistType, [DayCount], [Periods], [ProjMode])Distribute an amount over time according to a specified distribution type
FStepSpread (Time, Base, Total, FromDates, AnnualRates, [DayCount], [Periods], [ProjMode])Stepped Rate where there is a Total to be spread, using From Dates
TStepSpread (Time, Base, Total, ToDates, AnnualRates, [DayCount], [Periods], [ProjMode])Stepped Rate where there is a Total to be spread, using To Dates
MkPmtsWeighted (Time, Base, Total, PmtDates, Weights)Make Payments that are weighted to add to a Total
MultiUniSpread (Time, Base, Starts, Finishes, Totals, [DayCount], [Periods], [ProjMode])A Multiple version of UniSpread
MultiSCurve (Time, Base, Starts, Finishes, Totals, Skew, Peakness, [DayCount], [Periods], [ProjMode])A Multiple version of SCurve
Time Spread Periodic Family
The Time Spread Periodic family has periodic versions of the basic Time Spread functions, principal amongst which are:
These functions are good for capital expenditures that repeat over time, such as maintenance overhauls and refitting.
UniSpreadPer (Time, Base, Start, Finish, AbsFinish, Totals, Interval, [DayCount], [Periods], [ProjMode])UniSpread applied periodically (...Per... feature)
SCurvePer (Time, Base, Start, Finish, AbsFinish, Totals, Skew, Peakness, Interval, [DayCount], [Periods], [ProjMode])SCurve applied periodically (...Per... feature)
Time Spread Weighted Family
The Time Spread Weighted family is for the situation where you have a fixed amount to be spread over a timeperiod, but the rate at which this occurs varies, and you want to closely specify that rate. There are three ways the rate can vary:
  • It can vary on a recurring annual, or seasonal basis within the year. This is what UniSpreadS does, which does a straightforward UniSpread where the rate varies throughout the user-defined seasons of the year.
  • The total amount dispersed can be spread along a user-define profile as specified in the Weights variable of WeightSpread, where each weight is applied to an equal mini-timeperiod of length = (Finish-Start)/Number of Weights. This is effectively a user-defined curve. The weighting applies to the amount disbursed in each period and, because each mini-timeperiod is of the same length, to the rate in each mini-timeperiod.
  • The rate of disbursment can vary in a defined, step-like manner as in FWeightSpread, dependent on predefined dates of when the stepped changes take place. Because the function is required to specify an exact Total between the first of the FromDates and the Finish date, the only thing that is not pre-determined are the stepped sequence of annual rates themselves. Instead, the relative Weights of these annual rates are specified, which enables the function to determine the exact annual rates that would exactly disburse the Total, and then apply them to the timeperiod specified by Time and Base.
UniSpreadS (Time, Base, Start, Finish, Total, [SeasonFactors], [SeasonSeq], [DayCount], [Periods], [ProjMode])Spread an amount Uniformly over time, but allow for seasonality
WeightSpread (Time, Base, Start, Finish, Total, Weights, [DayCount], [Periods], [ProjMode])Spreads an amount through time using a series of weights
FWeightSpread (Time, Base, Finish, Total, FromDates, Weights, [DayCount], [Periods], [ProjMode])Distribute an amount over time according to specific weighting factors and FromDates
TWeightSpread (Time, Base, Start, Total, ToDates, Weights, [DayCount], [Periods], [ProjMode])Distribute an amount over time according to specific weighting factors and ToDates
Time Spread History Family
The Time Spread History family is an extension of the Time Spread family that contains functions that can be adjusted for historical payments or payments made 'to date', a problem often found in budgeting. The aim of these functions is to make the overall amount disbursed equal to the overall Total, adjusting the future rate of disbursement accordingly.

There are two ways in which the payments to date are taken into account:
  • One way is to explicitly specify a payments history using the variables DatesHistory and PmtsHistory. The remainder of the Total, after taking into account the historical payments, is spread according the different kind of spread mechanism in the function. The way these functions work is that firstly the historical payments are calculated to see if any historical payments occur in the timeperiod. Then the Total is reduced by the total amount of historical payments made to date, or payments yet to be made. Then the function tries to spread the remaining total in its usual way, either uniformly in the case of UniSpread,or in an S-Curve as in SCurve, etc. It will probably do this calculation internally more than once and adjust it, to make sure that no more or less than the Total is dispersed. To make it absolutely clear, the function will always disperse exactly the Total over the period of the projection, no more and no less.
  • A second and more direct way is to specify the overall amount incurred prior to Time in a single úActualsToDateú variable, for example in the UniSpreadAdj function. Here the Total is spread over the appropriate length of time after allowing for the ActualsToDate. This is not as trivial a calculation as first appears, because for functions like SCurveAdj the remainder of the curve has to honour the original shape before the actuals were incurred. Indeed, the function arrives at the forecast by pro-rata-ing the original forecast so that the total forecast by the function, plus the ActualsToDate, always comes to the Total. See the example of the SCurveAdj function for a demonstration of this principle. Again, the function will always disperse exactly the Total over the period of the projection, no more and no less.

Note: These functions will disperse the Total, no less and no more, regardless of the amount of historical payments specified.

These functions are very good for budgeting, where you always have a projection for the future and are constantly updating the past data. Without this family of functions it is a pain to keep changing your forecast to reflect actuals, whereas these functions do that job for you.
UniSpreadH (Time, Base, Start, Finish, Total, [DatesHistory], [PmtsHistory], [DayCount], [Periods], [ProjMode])UniSpread that uses historical data
SCurveH (Time, Base, Start, Finish, Total, Skew, Peakness, [DatesHistory], [PmtsHistory], [DayCount], [Periods], [ProjMode])SCurve that uses historical data
WeightSpreadH (Time, Base, Start, Finish, Total, Weights, [DatesHistory], [PmtsHistory], [DayCount], [Periods], [ProjMode])WeightSpread that uses historical data
FWeightSpreadH (Time, Base, Finish, Total, FromDates, Weights, [DatesHistory], [PmtsHistory], [DayCount], [Periods], [ProjMode])FWeightSpread that uses historical data
TWeightSpreadH (Time, Base, Start, Total, ToDates, Weights, [DatesHistory], [PmtsHistory], [DayCount], [Periods], [ProjMode])WeightSpread that uses historical data
DistSpreadH (Time, Base, Start, MidPoint, Finish, Total, DistType, [DatesHistory], [PmtsHistory], [DayCount], [Periods], [ProjMode])DistSpread that uses historical data
UniSpreadAdj (Time, Base, Start, Finish, Total, [ActualsToDate], [DayCount], [Periods], [ProjMode])UniSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
SCurveAdj (Time, Base, Start, Finish, Total, Skew, Peakness, [ActualsToDate], [DayCount], [Periods], [ProjMode])SCurve that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
WeightSpreadAdj (Time, Base, Start, Finish, Total, Weights, [ActualsToDate], [DayCount], [Periods], [ProjMode])WeightSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
FWeightSpreadAdj (Time, Base, Finish, Total, FromDates, Weights, [ActualsToDate], [DayCount], [Periods], [ProjMode])FWeightSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
TWeightSpreadAdj (Time, Base, Start, Total, ToDates, Weights, [ActualsToDate], [DayCount], [Periods], [ProjMode])TWeightSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
DistSpreadAdj (Time, Base, Start, MidPoint, Finish, Total, DistType, [ActualsToDate], [DayCount], [Periods], [ProjMode])DistSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
Constant Rate Projections Category
Project using a constant rate of payment

Constant Rate Projections are about taking one or more constant rates and applying them to timeperiods, say a budget or cashflow projection. There are 3 families:
Constant Rate Family

  • The basic Constant Rate family has its as its main building block the simple Con function, which just takes an annual rate and applies it between a Start and Finish date.
  • The Constant Rate Periodic family has periodic, or repeating versions of the basic Constant Rate functions.
  • The Constant Rate family goes on to apply this to various differing needs and situations. MultiCon just does several Cons at the same time (the îMulti...î feature), ConS is a seasonal version of Con, and RePhaseCon enables you to move an amount from one part of cash flow to another with one function.
  • The Constant Rate family also has functions that project a constant growing rate. They are still Constant Rate functions because the way they are changing is pre-ordained and smooth. These 'constant but changing functions' are useful and popular and consist of ConGrow, its simpler relative ConGrowQ, it's annual-growth-only relative AnnGrow, and the more unusual linear LinGrow.
Constant Rate Market
  • The functions in this family have an InitialAnnRate after which the function, every so often according to RevMonthsOpt, refers to a forecast to get its value. As usual in Business Functions, there are two ways of specifying a forecast (using growth rates or a preset forecast) and therefore there are two functions: ConFcstMkt and ConGrowMkt.
Constant Rate Revenue
  • The ...Rev... feature indicates the functions apply a price to a production stream to calculate a revenue. Whenever the rate changes of price and production are not coincident, this calculation can be messy and the functions simply get it right, ensuring that each bit of production gets multiplied by the correct price.
  • There are just two functions, for each of the two forecasting methods: ConRevGrow and ConRevFcst.

Constant Rate Family
Functions that apply a constant rate of payment to a timeperiod. The constant rate can be flat, as in Con, or growing, as in ConGrow or AnnGrow
The ConstantRate family is about applying a constant rate of payment to a timeperiod, ie working out how much is paid in that timeperiod.

The basic constant rate function is Con, which simply applies a single annual rate of payment between Start and Finish dates. The other functions are variations on this:
  • ConGrow (using the Grow feature) projects a constantly growing annual rate. The rate is allowed to evolve according to one or more percentage growth rates specified in GrowthRates. A variable called RevMonthsOpt determines how often the actual rate changes with respect to the forecast GrowthRates - it can either change continuously (RevMonthsOpt=0), or every few months (eg . RevMonthsOpt=12 or omitted, annually).
  • A very widely used and simplified version of ConGrow is AnnGrow, which projects an annually growing rate.
  • ConS is a seasonal version of Con, and RePhaseCon enables you to move an amount from one part of cash flow to another with one function.
  • LinGrow is a bit of a departure, essentially being a ConGrow but instead of growing in a compound, exponential way, it grows in a linear, straight line way.
  • Finally úAnnualRateú is for determining an AnnualRate given AmountDisbursed, Start and Finish Dates. It is effectively the inverse of Con.
  • The îMulti...î feature is applied to the basic Con function to give MultiCon. These functions simply to a 'Con' several times according to a range of AnnualRates,Starts and Finishes.

All the functions in the Constant Rate family have clearly defined rates, even if these rates are sometimes determined from a growth schedule, eg ConGrow. For functions that have the facility to look up a rate off a forecast, see the Constant Rate Market family.
Con (Time, Base, Start, Finish, AnnualRate, [DayCount], [Periods], [ProjMode])Constant rate
ConGrow (Time, Base, Start, Finish, AnnualRate, GrowthDates, GrowthRates, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Even rate that is continuously growing
AnnGrow (Time, Base, Start, Finish, AnnualRate, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])Constant rate that grows at annual intervals
RePhaseCon (Time, Base, FromStart, FromFinish, ToStart, ToFinish, AnnualRate, [DayCount], [Periods], [ProjMode])RePhase an amount according to a constant rate
ConS (Time, Base, Start, Finish, AnnualRate, [SeasonFactors], [SeasonSeq], [DayCount], [Periods], [ProjMode])Project an constant rate, but allow for seasonality
AnnRate (Time, Base, Start, Finish, AmountDisbursed, [DayCount], [Periods], [ProjMode])Annual Rate given an amount and a time difference
ConSF (Time, Base, Start, Finish, AnnualRate, OverallStartDate, OverallFinishDate, [DayCount], [Periods], [ProjMode])A version of Con that also has overall Start and Finish dates
ConGrowQ (Time, Base, Start, Finish, AnnualRate, GrowFrom, GrowthRatePA, ReviewMonths, [DayCount], [Periods], [ProjMode])A simplified version of ConGrow
MultiCon (Time, Base, Starts, Finishes, AnnualRates, [DayCount], [Periods], [ProjMode])A Multiple version of Con
MultiConGrow (Time, Base, Starts, Finishes, AnnualRates, GrowthDates, GrowthRates, ReviewMonths, [DayCount], [Periods], [ProjMode])A Multiple version of ConGrow
MultiAnnGrow (Time, Base, Starts, Finishes, AnnualRates, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])A Multiple version of AnnGrow
Constant Rate Periodic Family
The Constant Rate Periodic family is about constant rate functions that repeat periodically over time, like a rent with a break in it, or a production stream with regular breaks or disruptions.

The main functions are ConPer, a periodic version of Con, AnnGrowPer, a periodic version of AnnGrow, and ConGrowPer, a periodic version of ConGrow.
ConPer (Time, Base, Start, Finish, AbsFinish, AnnualRates, Interval, [DayCount], [Periods], [ProjMode])Con applied periodically (...Per... feature)
AnnGrowPer (Time, Base, Start, Finish, AbsFinish, AnnualRates, GrowthDates, GrowthRates, Interval, [DayCount], [Periods], [ProjMode])AnnGrow applied periodically (...Per... feature)
ConGrowPer (Time, Base, Start, Finish, AbsFinish, AnnualRates, GrowthDates, GrowthRates, ReviewMonths, Interval, [DayCount], [Periods], [ProjMode])ConGrow applied periodically (...Per... feature)
Constant Rate Market Family
Project a constant rate that determines its level with reference to a market forecast.
The Constant Rate Market family deals with constant rate projections where the rate is not strictly speaking constant, but changes at regular time intervals (denoted by ReviewMonths) with respect to a forecast which runs in the background, so to speak.
  • Both the Grow and Mkt features are added to create ConGrowMkt. The addition of the Mkt feature means that after a certain date FirstRevDate the function reviews to a market value, rather than changing the initial rate as in ConGrow. Apart from this, they are similar to ConGrow.
  • The Fcst and Mkt features are added to create ConFcstMkt. This applies a constant rate, according to values specified in a forecast. The addition of the Mkt feature means that after a certain date FirstRevDate it reviews according to a forecast and has a variable called ReviewMonths which governs how often the rate changes.
ConGrowMkt (Time, Base, Start, Finish, FirstRevDate, MktAnnRate, InitialAnnRate, GrowthDates, GrowthRates, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Project a constant rate that periodically reviews to a market rate prevailing at the time
ConFcstMkt (Time, Base, Start, Finish, FirstRevDate, InitialAnnRate, FcstVals, StartFcst, FcstBase, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Project a constant rate that periodically reviews to a forecast prevailing at the time
ConFcstGrowMkt (Time, Base, Start, Finish, FirstRevDate, InitialAnnRate, FcstVals, StartFcst, FcstBase, GrowthDates, GrowthRates, ReviewMonths, [DayCount], [Periods], [ProjMode])Project a constant rate that periodically reviews to a forecast prevailing at the time, and also apply growth
ConGrowMktQ (Time, Base, Start, Finish, MktAnnRate, InitialAnnRate, GrowFrom, GrowthRatePA, ReviewMonths, [DayCount], [Periods], [ProjMode])Project a constant rate that periodically reviews to a market rate prevailing at the time, quick version
Constant Rate Revenue Family
The Constant Rate Revenue Family is about applying the Constant Rate family of functions in the most common scenario - projecting revenue.

Usually you would do this by separately projecting the price and the production, and multiplying the two together. This can in some circumstances be rather less than perfect, because the price might change midway through a timeperiod and there is some confusion as to whether you use the average, starting or closing price. If your rate changes and price changes are not coincident, it tends to mean that when you run your model on different timebases eg quarterly and annually, you get slightly different answers, and you don't know which is the correct one.

The functions in the Constant Rate Revenue family take care of when price movements occur and even offer you the option, through the RevMonthsOpt variable, of specifying the frequency at which you want the price movements from the forecast to be applied to the revenue stream, for example, you might want the price only to be allowed to change annually. The default is that price changes are applied continually ie as soon as they happen on the forecast.
ConRevGrow (Time, Base, Start, Finish, MktPrice, FirstRevDate, AnnualRate, GrowthDates, GrowthRates, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Constant Rate Revenue with optional Growth
ConRevFcst (Time, Base, Start, Finish, FirstRevDate, AnnualRate, FcstVals, StartFcst, FcstBase, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Constant Rate Revenue according to a price forecast
Stepped Rate Projections Category
Project using a stepped or changing level of payment

Stepped Rate Projections work along similar principles to Constant Rate Projections except that the rate changes over time in a series of distinct steps, giving rise to stream (of, say, cashflow) that changes in level in a staircase-like manner.

Stepped Rate Projections are very widely used 'building block' functions for budgeting and business planning. They are useful for a wide variety of applications, including projecting a sales forecast, interest rate changes, rent etc.

There are 4 families, reflecting the different ways projections can be 'stepped':

Stepped Rate From Family
Functions that apply a stepped (changing level) rate of payment to a timeperiod.
The Stepped Rate From family is about applying a stepped rate of payment whose level is changing, to a timeperiod, ie working out how much is paid in that timeperiod, where the dates are the DATES FROM which each rate applies.

The basic function is FStep. It takes a range of AnnualRates and FromDates and applies them to the timeperiod. Other functions in the family differ as follows:
  • Adding the Fcst feature creates another function, FStepFcst. This function still requires a list of FromDates does not require a list of AnnualRates, because these are read off a forecast.
  • Adding the Grow feature gives rise to another function, FStepGrow. This function, as with the Fcst ones above, requires a list of FromDates but not AnnualRates, because these are determined from an InitialAnnRate, to which is applied one or more percentages from the GrowthRates.
  • FStepCombine is a versatile function that is capable of doing several FSteps and adding the results (rather like a Multi function), but it goes a stage further and can multiply the results together, take the maximum, minimum etc, a bit like the Stepped Rate Revenue functions.
  • All the functions in the Stepped Rate From family have clearly defined rates that apply to each step, or they mandatorily have to take rates from a forecast. For stepped rate functions either have a pre-defined stepped rate or have a default rate that comes from a forecast, see the Stepped Rate Market family.
FStep (Time, Base, Finish, FromDates, AnnualRates, [DayCount], [Periods], [ProjMode])Stepped rate using a series of 'from' dates and rates.
FStepGrow (Time, Base, Finish, InitialAnnRate, FromDates, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])Stepped rate from a series of dates where the rates are derived from an initial rate with growth applied
FStepFcst (Time, Base, Finish, FromDates, FcstVals, StartFcst, FcstBase, [DayCount], [Periods], [ProjMode])Project a stepped continuous rate from a series of dates where the rates are read off a supplied forecast
FStepGrown (Time, Base, Finish, FromDates, AnnualRates, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])FStep that is grown according to growth rates
FStepSF (Time, Base, Start, Finish, FromDates, AnnualRates, [DayCount], [Periods], [ProjMode])FStep incorporating a Start date as well as a Finish date
FStepS (Time, Base, Finish, FromDates, AnnualRates, [SeasonFactors], [SeasonSeq], [DayCount], [Periods], [ProjMode])The same as FStep but with the Seasonality facility
FStepCombine (Time, Base, CombineSwitch, Finish, FromDates, AnnualRates, [FromDates2], [AnnualRates2], [FromDates3], [AnnualRates3], [FromDates4], [AnnualRates4], [DayCount], [Periods], [ProjMode])Combines up to 4 streams of annual rates and calculates the amount due in the period.
Stepped Rate To Family
Functions that apply a stepped (changing level) rate of payment to a timeperiod.
The Stepped Rate To family is about applying a stepped rate of payment whose level is changing, to a timeperiod, ie working out how much is paid in that timeperiod, where the dates are the DATES UP UNTIL which each rate applies.

The basic function is TStep. It takes a range of AnnualRates and ToDates and applies them to the timeperiod. Other functions in the family differ as follows:
  • Adding the Fcst feature creates another function, TStepFcst. This function still requires a list of ToDates does not require a list of AnnualRates, because these are read off a forecast.
  • Adding the Grow feature gives rise to another function, TStepGrow. This function, as with the Fcst ones above, requires a list of ToDates but not AnnualRates, because these are determined from an InitialAnnRate, to which is applied one or more percentages from the GrowthRates.
  • TStepCombine is a versatile function that is capable of doing several TSteps and adding the results (rather like a Multi function), but it goes a stage further and can multiply the results together, take the maximum, minimum etc, a bit like the Stepped Rate Revenue functions.
  • All the functions in the Stepped Rate To family have clearly defined rates that apply to each step, or they mandatorily have to take rates from a forecast. For stepped rate functions either have a pre-defined stepped rate or have a default rate that comes from a forecast, see the Stepped Rate Market family.
TStep (Time, Base, Start, ToDates, AnnualRates, [DayCount], [Periods], [ProjMode])Stepped rate using a series of 'to' dates and rates.
TStepGrow (Time, Base, Start, InitialAnnRate, ToDates, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])Project a stepped continuous rate up to a series of dates where the rates are derived from an initial rate with growth applied
TStepFcst (Time, Base, Start, ToDates, FcstVals, StartFcst, FcstBase, [DayCount], [Periods], [ProjMode])Project a stepped continuous rate up to a series of dates where the rates are read off a supplied forecast
TStepGrown (Time, Base, Start, ToDates, AnnualRates, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])TStep that is grown according to growth rates
TStepSF (Time, Base, Start, Finish, ToDates, AnnualRates, [DayCount], [Periods], [ProjMode])TStep incorporating a Finish date as well as a Start date
TStepS (Time, Base, Start, ToDates, AnnualRates, [SeasonFactors], [SeasonSeq], [DayCount], [Periods], [ProjMode])The same as TStep but with the Seasonality facility
TStepCombine (Time, Base, CombineSwitch, Start, ToDates, AnnualRates, [ToDates2], [AnnualRates2], [ToDates3], [AnnualRates3], [ToDates4], [AnnualRates4], [DayCount], [Periods], [ProjMode])Combines up to 4 streams of annual rates and calculates the amount due in the period.
Stepped Rate Market Family
Project a stepped rate that determine sits level(s) with reference to a market forecast
The Stepped Rate Market family is an extension of the Stepped Rate From and Stepped Rate To families. Whereas the Stepped Rate family of components have all the rates defined, the Stepped Rate Market family allows for the looking up of a rate from a market forecast where a rate is not specified. So these functiobs will use the stepped rates specified, provided that are not blank and not zero, otherwise they will 'default' to a rate read off a forecast. The 2 kinds of functions, reflecting both the T... and F... of the other stepped rate families, are as follow:
  • Incorporating the Fcst feature results in 2 functions, FStepFcstMkt and TStepFcstMkt. These functions require both a list of dates and rates, but if one or more of these rates is blank or zero then the rate is determined from a supplied forecast.
  • Incorporating the Grow feature results in 2 more functions, FStepGrowMkt, and TStepGrowMkt. These functions require both a list of dates and rates, but if one or more of these rates is blank or zero then the rate is determined from a supplied forecast.
FStepGrowMkt (Time, Base, Finish, MktAnnRate, FromDates, AnnualRates, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])Project a stepped continuous rate using a series of 'from' dates and rates, where any gaps (zeros) in the rates are derived using a market rate and growth rates
FStepFcstMkt (Time, Base, Finish, FromDates, AnnualRates, FcstVals, StartFcst, FcstBase, [DayCount], [Periods], [ProjMode])Project a stepped continuous rate using a series of 'from' dates and rates, where any gaps (zeros) in the rates are read off a supplied forecast
TStepGrowMkt (Time, Base, Start, MktAnnRate, ToDates, AnnualRates, GrowthDates, GrowthRates, [DayCount], [Periods], [ProjMode])Project a stepped continuous rate using a series of 'to' dates and rates, where any gaps (zeros) in the rates are derived using a market rate and growth rates
TStepFcstMkt (Time, Base, Start, ToDates, AnnualRates, FcstVals, StartFcst, FcstBase, [DayCount], [Periods], [ProjMode])Project a stepped continuous rate using a series of 'to' dates and rates, where any gaps (zeros) in the rates are read off a supplied forecast
Stepped Rate Revenue Family
Calculates stepped rate projections of revenue where price and revenue are changing over time
The Stepped Rate Revenue Family, like the Constant Rate Revenue family, is about applying selected functions from the Stepped Rate family to the most common scenario - projecting revenue.

Usually you do this by separately projecting the price and the production, and multiplying the two together. This can in some circumstances be rather less than perfect, because the price might change midway through a timeperiod and there is some confusion as to whether you report the average, starting or closing price. It tends to mean that when you run your model on different timebases eg quarterly and annually, you get slightly different answers.

The functions in the Stepped Rate Revenue family take care of when price movements occur and offer you the option, through the RevMonthsOpt variable, of specifying the frequency at which you want the price movements to be applied to the forecast, for example, you might want the price only to be allowed to change annually. The default is that price changes are applied continually ie as soon as they happen on the forecast.
FStepRevGrow (Time, Base, Finish, MktPrice, FirstRevDate, FromDates, AnnualRates, GrowthDates, GrowthRates, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Stepped Rate Revenue with Growth using From Dates
FStepRevFcst (Time, Base, Finish, FirstRevDate, FromDates, AnnualRates, FcstVals, StartFcst, FcstBase, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Stepped Rate Revenue according to a forecast, using From Dates
TStepRevGrow (Time, Base, Start, MktPrice, FirstRevDate, ToDates, AnnualRates, GrowthDates, GrowthRates, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Stepped Rate Revenue with Growth using To Dates
TStepRevFcst (Time, Base, Start, FirstRevDate, ToDates, AnnualRates, FcstVals, StartFcst, FcstBase, [RevMonthsOpt], [DayCount], [Periods], [ProjMode])Stepped Rate Revenue according to a forecast, using To Dates
Ramped Rate Projections Category
Project using a changing level of payment

The Ramped Rate Projections Category is like the Stepped Rate Projections and Constant Rate Projections categories, except that instead of a rate being constant, smoothly changing or stepped, the rates are fitted to a plotted curve. The curve can either be a straight line (the default), or can be fitted to a polynomial.

The only function in this category, Ramp in the Ramped Rate family, is easy to use and permits an infinite variety of 'shapes' of projection, using the variable ConnectOpt to govern how the AnnualRates and Dates data points are used.

Ramped Rate Family
The Ramped Rate family is, like the families of the Stepped Rate Projections and Constant Rate Projections categories, concerned with projecting amounts over time. Instead of having the rate change smoothly and progressively over time (constant rate), or with sharp staircase-like step-changes, ramped rates are where the rate changes like points on a plotted curve.

In terms of application, ramped rates are good for things like sales or production projections, where you want the ability effectively to 'draw a curve' of the most likely scenario (stepped rates are good for contractual things like rent and loans).

Currently there is only one function in this family, Ramp, but which can apply to a wide variety of situations. It takes a list of AnnualRates and Dates as the basic input points to the plotted curve. The question of how the points governingg the annual rate should be joined together is dealt with by ConnectOpt. The default if omitted is a straight line connection, in other words if the rate in Jan 2004 was 100 and in Jan 2006 it was 200, then in Jan 2005 it would be 150. Other the staight line, you can choose a polynomial curve fit. This is especially useful because you can basically let the function worry about connecting the points to provide the best fit. You can either let the function choose the type of polynomial (recommended, ConnectOpt=0), or specify an order of polynomial you want fitted.
Ramp (Time, Base, Dates, AnnualRates, [ConnectOpt], [DayCount], [Periods], [ProjMode])Ramped Rate
Instantaneous Level Projections Category
Project a level of something at a certain time by reference to one or a series of levels that change with time.

Instantaneous Level Projections are quite different from the projections about rates and amounts that other preceding projections categories like Stepped Rate Projections deal with. Whereas the other categories generally deal with flows of money, or production, rent, whatever, Instantaneous Level Projections are about looking up a level according to the prevailing date. They are more like a lookup.

There are 5 families in this category:

  • Levels is similar to the Stepped Rate From and Stepped Rate To families, except that what is returned is a level at a particular time, not the results of applying a rate over a time period. So the familiar models of functions are present such as Level (whose constant rate equivalent would be Con), FLevels (stepped rate equivalent FStep) etc.
  • Levels Market, is the levels equivalent of the Stepped Rate Market family, with functions such as FLevelsGrowMkt and TLevelsFcstMkt. As usual with the Mkt feature, the function uses specified levels where they are non-blank and non-zero, otherwise it defaults to a level read off a forecast.
  • Growth is about the various ways of applying GrowthRates to inflate a figure in the future. Grow simply applies GrowthRates and GrowthDates to inflate a figure from one date to another, with the added ability to specify, via RevMonthsOpt, whether that growth is applied continuously or in steps, every few months. FGrowStep and TGrowStep allow you to specify the dates of those steps (using FromDates or ToDates), and there a further group of functions that use the Mkt feature to either use a specified level or one off a supplied forecast.
  • Forecast is a very basic family concerned with looking up values off a forecast. Fcst is simple, popular and versatile and does just this kind of lookup.
  • Forecast Specific Times is about when the supplied forecast is not regularly time-spaced as is the requirement in the Forecast family and forecasts generally. The basic workhorses are FcstT and TFcstT, which use FcstFromDates and FcstToDates respectively to allow specification of the forecast.

Levels Family
Project one or more series of levels
Levels is similar to the Stepped Rate From and Stepped Rate To families, except that what is returned is a level at a particular time, not the results of applying a rate over a time period. So the familiar models of functions are present such as Level (whose constant rate equivalent would be Con), FLevels (stepped rate equivalent FStep) etc.
Level (TheDate, Start, Finish, Level)Level with a Start and Finish date
FLevels (TheDate, Finish, FromDates, Levels)Series of levels using 'from' dates and levels
TLevels (TheDate, Start, ToDates, Levels)Series of levels to corresponding dates
FLevelsGrow (TheDate, Finish, InitialLevel, FromDates, GrowthDates, GrowthRates)Series of levels from specific dates the level is determined by an initial level and a growth forecast
TLevelsGrow (TheDate, Start, InitialLevel, ToDates, GrowthDates, GrowthRates)Series of levels to specific dates the level is determined by an initial level and a growth forecast
FLevelsFcst (TheDate, Finish, FromDates, FcstVals, StartFcst, FcstBase)Series of levels from specific dates the level is determined by a forecast
TLevelsFcst (TheDate, Start, ToDates, FcstVals, StartFcst, FcstBase)Series of levels to specific dates the level is determined by a forecast
Levels Market Family
Project one or more series of levels where the missing levels are determined with reference to a market forecast that varies over time
These functions are pretty similar to their non-Mkt counterparts. The only time the market aspect is used is if one or more levels ins a blank cell or zero, in which case a market value is determined.
FLevelsGrowMkt (TheDate, Finish, MktLevel, FromDates, Levels, GrowthDates, GrowthRates)Series of levels from corresponding dates where any gaps are determined by an initial level and a growth forecast
TLevelsGrowMkt (TheDate, Start, MktLevel, ToDates, Levels, GrowthDates, GrowthRates)Series of levels to corresponding dates where any gaps are determined by an initial level and a growth forecast
FLevelsFcstMkt (TheDate, Finish, FromDates, Levels, FcstVals, StartFcst, FcstBase)Series of levels from corresponding dates where any gaps are determined by a forecast
TLevelsFcstMkt (TheDate, Start, ToDates, Levels, FcstVals, StartFcst, FcstBase)Series of levels to corresponding dates where any gaps are determined by a forecast
Growth Family
Project levels of something using growth rates
Growth is about the various ways of applying GrowthRates to inflate a figure in the future. Grow simply applies GrowthRates and GrowthDates to inflate a figure from one date to another, with the added ability to specify, via RevMonthsOpt, whether that growth is applied continuously or in steps, every few months. FGrowStep and TGrowStep allow you to specify the dates of those steps (using FromDates or ToDates), and there a further group of functions that use the Mkt feature to either use a specified level or one off a supplied forecast.
Grow (TheDate, StartingValue, GrowthDates, GrowthRates, [RevMonthsOpt])Grow an amount from one date to another using GrowthRates
FGrowStep (TheDate, Finish, StartingValue, FromDates, GrowthDates, GrowthRates)Grow an amount in steps using FromDates
TGrowStep (TheDate, Start, StartingValue, ToDates, GrowthDates, GrowthRates)Grow an amount in steps using ToDates
FGrowStepMkt (TheDate, Finish, MktAnnRate, FromDates, AnnualRates, GrowthDates, GrowthRates)Grow in steps with reference to a Market
TGrowStepMkt (TheDate, Start, MktAnnRate, ToDates, AnnualRates, GrowthDates, GrowthRates)Grow in steps with reference to a Market
GrowMkt (TheDate, StartingValue, GrowthDates, GrowthRates, ReviewMonths, [RevDiscountOpt])Grow an amount as if it were reviewing to market
GrowLin (TheDate, StartingValue, GrowthDates, GrowthRates, [RevMonthsOpt])Grow an amount linearly
GrowQ (TheDate, StartingValue, GrowthDates, GrowthRates)Grow an amount (Quick Version)
Forecast Family
Determine values at points in time by looking up against a forecast that depends on time
The Forecast family is about reading values off a forecast. This is useful for forecasting revenues that depend on a forecast of price, for example.
  • The simplest type of forecast is where prices change at even (for example annual) intervals. Fcst essentially does a lookup based on the date, against an evenly spread forecast.
  • There is an optional variable in the Forecast family functions called úFcstSwitchOptú which allows you to specify whether you want a simple lookup or whether you want the function to interpolate if the date falls inbetween dates on the forecast.
  • An evenly spaced forecast is specified in terms of a StartFcst date when the forecast starts, a FcstBase, which is the length of time in months between the values of the forecast, and FcstVals which are the forecast values themselves.
  • Other functions in this family are variations on the basic theme. FcstGrow adds the Grow feature and adds the ability to grow the value read off the forecast by one or a number of GrowthRates, effective from GrowthDates. Growth is applied to the forecast value at intervals specified by ReviewMonths.
  • FFcstStep and TFcstStep perform the forecasting job using an evenly spaced underlying forecast, but by adding the additional constraint that the value can only change in stepped amounts on certain dates. So the lookup first of all determines which step you are in, and then the Fcst value at the beginning of that step. This is useful in Real Estate applications where you want the prevailing rent that arose at the last Rent Review.
  • TFcstStepMkt and FFcstStepMkt take the stepped forecast a stage further by allowing you to specify absolutely the first one or more steps, and then when you have not specified any more levels the function will read off the missing values off the forecast. Again, this has particular application in Real Estate.
Fcst (TheDate, FcstVals, StartFcst, FcstBase, [FcstSwitchOpt])Read a value off an evenly spaced forecast
FcstGrow (TheDate, FcstVals, StartFcst, FcstBase, GrowthDates, GrowthRates, [RevMonthsOpt], [FcstSwitchOpt])Read a value off an evenly spaced forecast and then grow it
FcstCollar (TheDate, Minimum, Maximum, FcstVals, StartFcst, FcstBase, [FcstSwitchOpt])Read a value off an evenly spaced forecast, with a maximum and minimum
SumFcst (Date1, Date2, FcstVals, StartFcst, FcstBase, [DayCount], [Periods])Sum of the values of an even forecast between two dates
FFcstStep (TheDate, Finish, FromDates, FcstVals, StartFcst, FcstBase, [FcstSwitchOpt])Read a value off an evenly spaced forecast, but at specific stepped intervals described by FromDates
TFcstStep (TheDate, Start, ToDates, FcstVals, StartFcst, FcstBase, [FcstSwitchOpt])Read a value off an evenly spaced forecast, but at specific stepped intervals described by ToDates
Forecast Specific Times Family
Determine values at points in time by looking up against a forecast that depends on time, where the forecast has irregular dates
FcstT (TheDate, FcstFromDates, FcstValues, [FcstSwitchOpt])Get a Forecast from a general range of times and values
TFcstT (TheDate, FcstToDates, FcstValues, [FcstSwitchOpt])FcstT but with ToDates instead of FromDates
Levels Over Time Projections Category
Project a level of something during a certain time period by reference to one or a series of levels that change with time.

Levels Over Time Projections are similar to Instantaneous Level Projections, with one important difference. Whereas Instantaneous Level Projections deal with the level of something happending at a single date (and in this way they are really just a lookup), Levels Over Time Projections deal with the Level of something over the course of the prevailing timeperiod. This means that there is more than one of way to determine such a level, which the functions in this category allow. The level could, for instance, be the average level over the period, or it could be the prevailing level at the beginning or the end.

Levels Average Family
Project a level of something during a certain time period by reference to one or a series of levels that change with time.
ALevel (Time, Base, StartLevels, FinishLevels, Level, [LevelSwitchOpt], [DayCount], [Periods])Simple level with a Start and Finish date averaged over a timeperiod
FALevels (Time, Base, FinishLevels, FromDates, Levels, [LevelSwitchOpt], [DayCount], [Periods])Series of levels from corresponding dates averaged over a timeperiod
TALevels (Time, Base, StartLevels, ToDates, Levels, [LevelSwitchOpt], [DayCount], [Periods])Series of levels to corresponding dates averaged over a timeperiod
FALevelsGrow (Time, Base, FinishLevels, InitialLevel, FromDates, GrowthDates, GrowthRates, [LevelSwitchOpt], [DayCount], [Periods])Series of levels from specific dates where the level is determined by an initial level and GrowthRates averaged over a timeperiod
TALevelsGrow (Time, Base, StartLevels, InitialLevel, ToDates, GrowthDates, GrowthRates, [LevelSwitchOpt], [DayCount], [Periods])Series of levels to corresponding dates the level is determined by an initial level and GrowthRates averaged over a timeperiod
FALevelsFcst (Time, Base, FinishLevels, FromDates, FcstVals, StartFcst, FcstBase, [LevelSwitchOpt], [DayCount], [Periods])Series of levels from corresponding dates the level is determined by a forecast averaged over a timeperiod
TALevelsFcst (Time, Base, StartLevels, ToDates, FcstVals, StartFcst, FcstBase, [LevelSwitchOpt], [DayCount], [Periods])Series of levels to corresponding dates the level is determined by a forecast averaged over a timeperiod
Levels Average Market Family
Project a level of something during a certain time period by reference to one or a series of levels that change with time, where the absolute value of the level is determined with reference to a forecast.
FALevelsGrowMkt (Time, Base, FinishLevels, MktLevel, FromDates, Levels, GrowthDates, GrowthRates, [LevelSwitchOpt], [DayCount], [Periods])Series of levels from corresponding dates where any gaps are determined by an initial level and a growth forecast averaged over a timeperiod
TALevelsGrowMkt (Time, Base, StartLevels, MktLevel, ToDates, Levels, GrowthDates, GrowthRates, [LevelSwitchOpt], [DayCount], [Periods])Series of levels to corresponding dates where any gaps are determined by an initial level and a growth forecast averaged over a timeperiod
FALevelsFcstMkt (Time, Base, FinishLevels, FromDates, Levels, FcstVals, StartFcst, FcstBase, [LevelSwitchOpt], [DayCount], [Periods])Series of levels from corresponding dates where any gaps are determined by a forecast averaged over a timeperiod
TALevelsFcstMkt (Time, Base, StartLevels, ToDates, Levels, FcstVals, StartFcst, FcstBase, [LevelSwitchOpt], [DayCount], [Periods])Series of levels to corresponding dates where any gaps are determined by a forecast averaged over a timeperiod
Time Value Of Money Analysis Category
Analyse cash flows using Discounted CashFlow analysis, NPV and convert between different bases of Interest Rates

Time Value of Money Analysis is about Discounted Cash Flow Calculations and Interest Rate calculations.

Basic Interest Rate Conversions Family
Do calculations and conversions between different types of interest rates.
There are three main ways interest rates are commonly described:

  1. Simple Interest Rate. This is where the interest rate described by dividing the actual interest paid by the product of the Principal and the fraction of a year represented by the time period. In other words, R = I /PT, or I = P x T x R where I is the total interest paid, R is interest rate per annum, P is the principal and T is time expressed as a fraction of a year. If interest is paid more than once per year, the effective interest rate is somewhat higher than the quoted interest rate.
  2. APR or 'Annual Percentage Rate'. Also known as the 'effective' or 'annual effective' rate. This is where the interest rate is described as the simple interest rate that would be appropriate if interest had been paid annually in arrears. By quoting this rate the periodicity of the payments does not need to be quoted just to compare the economics of different APR's, and comparison is the main reason for the use of APR's. The APR is usually higher than Simple Interest because Simple Interest is usually paid at smaller intervals than yearly.
  3. Continuous Rate. Another way of providing a level basis for comparing interest rates is to assume that payments are made continuously, perhaps best envisaged as having interest paid every day. Using continuous rates has the same advantage as APR's in that there is noo need to quote the periodicity of payments to make a comparison, the rate is continuous.

A fourth way of dealing with time value of money calculations is to simply calculate the difference in time value between two dates, known as a discount factor. This turns out to be a very good way of working because it avoids the question of "was that simple, APR or continuous".

This family converts between all these four methods of calculating interest and time value of money calculations.
The main functions of interest are self-explanatory:


PmtsPerYear, DayCount and Periods

  • All the functions mentioned above that require informatio about periodicity (ie the ones that involve SImple Interest) use a variable called PmtsPerYear to describe this. A value of -4 indicates quarterly in arrears, -1 is annually in arrears and +2 would be biannually in advance (interest in davnace of course rarely happens for lending or borrowing situations, but where rents or finance rents it can be of relevance).
  • The functions involving discount factors ...DF... require the determination of a length of time in years between FromDate and the ToDate. The functions permit this through the use the DayCount which enables you to decide whether you want the length of time in years between the the FromDate and ToDate to measured in a specific DayCount convention or in the default ACT/ACTM. Additionally, you can specify Periods for ACT/ACT (in period).
  • Be careful when using SimpleToDF and úDFTo SImpleú not to confuse Periods and PmtsPerYear. Periods relates to the length of time between FromDate and ToDate, PmtsPerYear refers to the periodicity of simple interest.
  • Whilst it might be useful and more accurate in some circumstances to be more specific than PmtsPerYear to specify simple interest payments, doing so would mean that we could not use annuity formulae because of the irregular periods and we would have to adopt a longhand approach to interest compounding which would be less transparent. For functions that use detailed DayCount and Periods definitions see the Advanced Interest Rate Conversions Family.
SimpleToAPR (InterestSimple, PmtsPerYear)Convert a simple interest rate to an APR (Annual Percentage Rate)
SimpleToCont (InterestSimple, PmtsPerYear)Convert a simple interest rate to a Continuous Interest Rate
SimpleToDF (InterestSimple, PmtsPerYear, FromDate, ToDate, [DayCount], [Periods])Convert a simple interest rate to a discount factor
APRToSimple (InterestAPR, PmtsPerYear)Convert an APR (Annual Percentage Rate) to a simple interest rate
APRToCont (InterestAPR)Convert an APR (Annual Percentage Rate) to a Continuous Interest Rate
APRToDF (InterestAPR, FromDate, ToDate, [DayCount], [Periods])Convert an APR (Annual Percentage Rate) to a discount factor
ContToSimple (InterestCont, PmtsPerYear)Convert a continuous interest rate to a simple interest rate
ContToAPR (InterestCont)Convert a Continuous Interest Rate to an APR (Annual Percentage Rate)
ContToDF (InterestCont, FromDate, ToDate, [DayCount], [Periods])Convert a continuous interest rate to a discount factor
DFToSimple (DiscountFactor, PmtsPerYear, FromDate, ToDate, [DayCount], [Periods])Convert a discount factor to a simple interest rate
DFToAPR (DiscountFactor, FromDate, ToDate, [DayCount], [Periods])Convert a discount factor to an APR (Annual Percentage Rate)
DFToCont (DiscountFactor, FromDate, ToDate, [DayCount], [Periods])Convert a discount factor to a continuous interest rate
Advanced Interest Rate Conversions Family
Do calculations and conversions between different types of interest rates.
There are three main ways interest rates are commonly described:

  1. Simple Interest Rate. This is where the interest rate described by dividing the actual interest paid by the product of the Principal and the fraction of a year represented by the time period. In other words, R = I /PT, or I = P x T x R where I is the total interest paid, R is interest rate per annum, P is the principal and T is time expressed as a fraction of a year. If interest is paid more than once per year, the effective interest rate is somewhat higher than the quoted interest rate.
  2. APR or 'Annual Percentage Rate'. Also known as the 'effective' or 'annual effective' rate. This is where the interest rate is described as the simple interest rate that would be appropriate if interest had been paid annually in arrears. By quoting this rate the periodicity of the payments does not need to be quoted just to compare the economics of different APR's, and comparison is the main reason for the use of APR's. The APR is usually higher than Simple Interest because Simple Interest is usually paid at smaller intervals than yearly.
  3. Continuous Rate. Another way of providing a level basis for comparing interest rates is to assume that payments are made continuously, perhaps best envisaged as having interest paid every day. Using continuous rates has the same advantage as APR's in that there is noo need to quote the periodicity of payments to make a comparison, the rate is continuous.

A fourth way of dealing with time value of money calculations is to simply calculate the difference in time value between two dates, known as a discount factor. This turns out to be a very good way of working because it avoids the question of "was that simple, APR or continuous".
SimpleToAPRX (FromDatesInt, IntRatesSimple, FromDate, ToDate, [DayCount], [Periods], [MarginOpt])Convert a simple interest rate to an APR (Annual Percentage Rate), extended version
SimpleToContX (FromDatesInt, IntRatesSimple, FromDate, ToDate, [DayCount], [Periods], [MarginOpt])Convert a simple interest rate to a Continuous Interest Rate, extended version
SimpleToDFX (FromDatesInt, IntRatesSimple, FromDate, ToDate, [DayCount], [Periods], [MarginOpt])Convert a simple interest rate to a discount factor, extended version
APRToSimpleX (InterestAPR, FromDate, ToDate, [DayCount], [Periods], [MarginOpt])Convert an APR (Annual Percentage Rate) to a simple interest rate, extended version
ContToSimpleX (InterestCont, FromDate, ToDate, [DayCount], [Periods], [MarginOpt])Convert a continuous interest rate to a simple interest rate, extended version
DFToSimpleX (DiscountFactor, FromDate, ToDate, [DayCount], [Periods], [MarginOpt])Convert a discount factor to a simple interest rate, extended version
RateConvert (FromDatesInt, IntRatesSimple, FromDate, ToDate, [DayCountSource], [DayCountTarget], PeriodsSource, PeriodsTarget, [MarginOpt])Generic Rate Convert
Annuity Family
Do Time Value of Money calculations that use variations annuity formulae
  • The two main functions of this family are PVM and PmtM which are simply variations on the basic PV and Pmt functions in Excel. Whereas Excel's functions are most suited to annual cashflows, these two functions allow for the fact that payments can occur during the year as determined by the variable PmtsPerYear. Refer to the ¥CorrectionM¥ function also for how the adjustment for non-annual cashflows is made.

  • Next there are functions that deal with growing annuities and perpetuities, mostly based on the Gordon formula for an exponentially growing perpetuity. Principal amongst these functions are PVEGPerp and PVEGAnn, and for a simple summation without discounting, the somewhat simpler SumEGAnn.

  • There are the growing perpetuity and annuity functions that make the adjustment for non-annual cashflows, these being PVEGPerpM and PVEGAnnM.
All of these functions basically use the standard annuity formula in combination with the annual adjustment formula of CorrectionM, itself a derivation from the annuity formula.

  • There are also functions that deal with the present values of PERIODIC growing annuities, namely PVEGPerPerp and PVEGPerAnn. These deal with the situation where the cashflows, growing exponentially, occur at intervals other than annual.
FVM (AnnPmt, PresentValue, InterestAPR, TermYrs, PmtsPerYear)Future Value of an annuity allowing for different periodicity of payments per year
PVM (AnnPmt, InterestAPR, TermYrs, PmtsPerYear, [FVOpt])Present Value of an Annuity allowing for different periodicity of payments per year
PmtM (PresentValue, InterestAPR, TermYrs, PmtsPerYear, [FVOpt])Annual Payment of an annuity allowing for different periodicity of payments per year
TermM (AnnPmt, PresentValue, InterestAPR, PmtsPerYear, [FVOpt])Length of the Term of an annuity allowing for different periodicity of payments per year
IntRateM (AnnPmt, PresentValue, TermYrs, PmtsPerYear, [FVOpt])Interest Rate (APR) of an annuity allowing for different periodicity of payments per year
PVEGAnn (DiscountRateAPR, TermYrs, AnnGrowthRate)Present Value of an Exponentially Growing Annuity
PVEGAnnM (DiscountRateAPR, TermYrs, PmtsPerYear, AnnGrowthRate, [GrowRevsPerYrOpt])Present Value of an Exponentially Growing Annuity
PVEGPerAnn (DiscountRateAPR, TermYrs, AnnGrowthRate, PmtIntervalYrs)Present Value of an Exponentially Growing PERIODIC Annuity
PVEGPerp (DiscountRateAPR, AnnGrowthRate)Present Value of an Exponentially Growing Perpetuity
PVEGPerpM (DiscountRateAPR, PmtsPerYear, AnnGrowthRate, [GrowRevsPerYrOpt])Present Value of a Perpetuity
PVEGPerPerp (DiscountRateAPR, AnnGrowthRate, PmtIntervalYrs)Present Value of an Exponentially Growing PERIODIC Perpetuity
SumEGAnn (TermYrs, AnnGrowthRate)Sum of an exponentially growing annuity (growth annually in arrear).
SumEGAnnM (TermYrs, AnnGrowthRate, [GrowRevsPerYrOpt])Sum of an exponentially growing annuity (growth applied with specified frequency).
SumEGAnnCont (TermYrs, AnnGrowthRate)Sum of an exponentially growing annuity (growth annually in arrear).
SumLGAnn (TermYrs, AnnGrowthRate)The sum of a linearly growing annuity (growth annually in arrear)
SumLGAnnM (TermYrs, AnnGrowthRate, [GrowRevsPerYrOpt])The sum of a linearly growing annuity, taking into account the number of changes in rate per year.
SumLGAnnCont (TermYrs, AnnGrowthRate)The sum of a linearly growing annuity assuming constant continuous growth
Annuity Extended Family
The standard annuity functions (PMT etc) extended to allow for industry methods of interest daycount and payment periodicity.
This family of functions is for when you want to calculate annuities but are hampered by the fact the annuity in question does to adhere to the restrictive rules of standard annuity formulae ie:
  • The interest is calculated according to a daycount basis eg ACT/365 or ACT/360 which means that the interest component of the annuity will vary from period to period.
  • The payment frequency matches consistent calendar days and is therefore irregular in length.
Usually you would have to build a special spreadsheet and use Goalseek to iteratively determine the annuity PMT, or NPVX or BF's PVT to get the PV.

This seems innapropriate because the calculation is a generic one and the spreadsheet is merely calculating interest according to the actual number of days - it is not modelling anything particularly unique to your situation.

What these functions do is do a complete cashflow's calculations internally, and in some cases use a highly efficient iterative technique to derive the answer.

The two 'different' parameters to a normal annuity calculation are DayCount and PeriodsInt. These govern how and when interest is calculated, respectively.

Footnote: The idea for this family came from an investement bank that contacted us enquiring as to whether there was a way to adapt the standard PMT function for when interest was calculated on the 15th of each month on an ACT/360 basis. Well, there wasn't a way to adapt the conventional financial math, but using Business Functions' daycount methodology combined with its solver produced a highly accurate and efficient solution, eliminating the need for separate spreadsheets for each loan.
PVBF (DrawDate, NumPmts, InterestSimple, Pmt, [FVOpt], [DayCount], [PeriodsInt])Present Value of an annuity using industry values for interest daycount and period frequency.
PMTBF (PresentValue, DrawDate, NumPmts, InterestSimple, [FVOpt], [DayCount], [PeriodsInt])Annuity Payment using industry values for interest daycount and period frequency.
RateBF (PresentValue, DrawDate, NumPmts, Pmt, [FVOpt], [DayCount], [PeriodsInt])Annual interest rate (simple) of an annuity using industry values for interest daycount and period frequency.
PVX (DrawDate, NumPmts, InterestSimple, Pmt, [FVOpt], [DayCount], [PeriodsInt])Present Value of an annuity using industry values for interest daycount and period frequency.
PMTX (PresentValue, DrawDate, NumPmts, InterestSimple, [FVOpt], [DayCount], [PeriodsInt])Annuity Payment using industry values for interest daycount and period frequency.
IntRateX (PresentValue, DrawDate, NumPmts, Pmt, [FVOpt], [DayCount], [PeriodsInt])Annual interest rate (simple) of an annuity using industry values for interest daycount and period frequency.
Discounted Cash Flow Category
Calculate Discounted Cash Flow economic indicators

The Discounted Cash Flow Category is for all aspects of doing calculations on DCF. The library has several families that deal with different configurations of DCF:

  • The DCF One-Off family deals with a single payment in the future or the past.
  • The DCF Annual deals with the common case of annually arranged cashflows.
  • The DCF Even Intervals is like the DCF Annual family but deals with any evenly distributed series of cashflows.
  • The DCF Specific Times family deals with the general case where you have some cashflows and some dates, and they don't have to be evenly distributed.
  • The ÅDispersed AnnualÅ family deals with cashflows that are annual but that are spread through the year according (eg quarterly, monthly)

DCF One-Off Family
PVOne (TheDate, NPVDate, DiscountRateAPR, [DayCountDisc], [PeriodsDisc])Determine the NPV of a future single payment of 1 dollar (ie a factor)
FPVOne (TheDate, NPVDate, DiscountRatesAPR, FromDates, [DayCountDisc], [PeriodsDisc])PV of a single payment using a discount rate that changes over time according to FromDates
TPVOne (TheDate, NPVDate, DiscountRatesAPR, ToDates, [DayCountDisc], [PeriodsDisc])PV of a single payment using a discount rate that changes over time according to ToDates
PVOneQ (TheDate, NPVDate, DiscountRateAPR)Determine the NPV of a future single payment of 1 dollar (Quick Version)
DCF Annual Family
Do Discounted Cash Flow analysis on cashflows that are evenly spaced at one year intervals.
Annual flows are the most simple of DCF analyses and most NPV functions are suited to this kind of analysis.
PVA (NPVDate, DiscountRateAPR, AnnualCashFlows, StartAnnDate, [DCountDisc])Present Value of an annual stream of cashflows
IRRA (AnnualCashFlows, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt], [DCountDisc])Internal Rate of Return of an annual stream of cashflows
PayBackA (AnnualCashFlows, StartAnnDate, MinPayBack, [Interpolate], [DCountDisc])Payback date for Annual cash flows
PayBackDiscA (DiscountRateAPR, AnnualCashFlows, StartAnnDate, MinPayBack, [Interpolate], [DCountDisc])Discounted Payback Date for Annual DCF
DurationA (NPVDate, DiscountRateAPR, AnnualCashFlows, StartAnnDate, [DCountDisc])Macaulay Duration
AvLifeA (Start, AnnualCashFlows, StartAnnDate, [DCountDisc])Average Life
IRRAX (AnnualCashFlows, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt])Internal Rate of Return of an annual stream of cashflows, extended version
DCF Even Intervals Family
Calculate Discounted Cashflow Analysis on cashflows that are occur at even intervals over time, but not necessarily annual.
The Business Functions library caters for annual cashflows in the DCF Annual family, and where annual cashflows are not the case the ÅDCF SpecificÅ family caters for cash flows occurring at any time. The ÅDCF EvenÅ family, described here, deals with the common case where the cashflows are not annual, but they are even, for example quarterly or monthly. The two main functions are úIRREvenú and úNPVEvenú
PVE (NPVDate, DiscountRateAPR, EvenCashFlows, EvenTimeStart, EvenTimeInterval)NPV for an evenly spaced (not necessarily annual) series of cashflows
IRRE (EvenCashFlows, EvenTimeInterval, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt])IRR for an evenly spaced (not necessarily annual) series of cashflows
PaybackE (EvenCashFlows, EvenTimeStart, EvenTimeInterval, MinPayBack, [Interpolate])Payback Date
PayBackDiscE (DiscountRateAPR, EvenCashFlows, EvenTimeStart, EvenTimeInterval, MinPayBack, [Interpolate])Discounted Payback Date
DurationE (NPVDate, DiscountRateAPR, EvenCashFlows, EvenTimeStart, EvenTimeInterval)Macaulay Duration
AvLifeE (Start, EvenCashFlows, EvenTimeStart, EvenTimeInterval)Average Life
IRREX (EvenCashFlows, EvenTimeInterval, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt])IRR for an evenly spaced (not necessarily annual) series of cashflows, extended version
DCF Specific Times Family
Calculate Discounted Cashflow Analysis on cashflows that occur at irregular times.
The DCF Specific Times family is the most versatile of the NPV families and therefore is highly useful. The basic inputs of these functions are Cashflows and Dates.
  • Excel provides two functions that help in this area, XIRR and XNPV. If your problem is sufficiently simple, these functions are very useful, and fast.
  • Where the DCF Specific Times family really helps is in the extra functionality provided by the various options.
  • The two basic functions are PVT and IRRT. úPVT allows you to specify a particular NPVDate.
  • These functions also allow you to specify DayCount and Periods, so that you can closely control the method by which the time difference between the cash flow date and the NPV date is determined. This is useful: Excels XNPV assumes ACT/365 - if you need something different then PVT is the answer.
PVT (NPVDate, DiscountRateAPR, Dates, Cashflows, [DayCountDisc], [PeriodsDisc])Present Value of cashflows at specific Dates
IRRT (Dates, Cashflows, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt], [DayCountDisc], [PeriodsDisc])Internal Rate of Return of cashflows at specific times
PayBackT (Dates, Cashflows, MinPayBack, [Interpolate], [DayCountDisc], [PeriodsDisc])Payback Date
PayBackDiscT (DiscountRateAPR, Dates, Cashflows, MinPayBack, [Interpolate], [DayCountDisc], [PeriodsDisc])Discounted Payback Date
DurationT (NPVDate, DiscountRateAPR, Dates, Cashflows, [DayCountDisc], [PeriodsDisc])Macaulay Duration
AvLifeT (Start, Dates, Cashflows, [DayCountDisc], [PeriodsDisc])Average Life
IRRTX (Dates, Cashflows, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt], [DayCountDisc], [PeriodsDisc])Internal Rate of Return of cashflows at specific times, extended version
DCF Dispersed Annual Family
NPVM (NPVDate, DiscountRateAPR, AnnualCashFlows, StartAnnDate, PmtsPerYear)Present Value of annual cash flows that are annually specified that are assumed to take place at intervals over the year
IRRM (AnnualCashFlows, PmtsPerYear, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt])IRR for cash flows that are annually specified that are assumed to take place at intervals over the year
CorrectionM (DiscountRateAPR, PmtsPerYear)Correction Factor to multiply an NPV for annually spaced cashflows to convert to cashflows spaced over the year
CorrectionCont (DiscountRateAPR)Correction Factor to multiply an NPV for annually spaced cashflows to convert to cashflows spaced continuously throughout the year
CorrectionDC (NPVDate, DiscountRateAPR, [DayCount], [CashBasis])NPV Correction Factor for DayCount
DurationM (NPVDate, DiscountRateAPR, AnnualCashFlows, StartAnnDate, PmtsPerYear)Macaulay Duration
AvLifeM (Start, AnnualCashFlows, StartAnnDate, PmtsPerYear)Average Life
EffTimeM (DiscountRateAPR, PmtsPerYear)Effective Time when cashflow is dispersed over the year
IRRMX (AnnualCashFlows, PmtsPerYear, [IRRMinOpt], [IRRMaxOpt], [IRRAccOpt])IRR for cash flows that are annually specified that are assumed to take place at intervals over the year, extended version
Projections NPV Category
Calculate Present Value's of the Projections functions

The Projections NPV category calculates the Present Values of some of the Projections functions. Conventionally, when using Business Functions to do DCF, you first or all prepare a full cash flow using projections functions such as FStep, Con, or AnnGrow and calculate the Net Present Value of the result. If you are confident of your input data, there is of course no need to do this - the function has sufficient information to calculate the NPV directly. This category of functions takes the same input data as you might use in the corresponding projections function, and prepares a full cash flow internally within Business Functions, which it then discounts. The effect is exactly the same as doing the full cashflow in your spreadsheet, but only using one cell and one function call, instead of a whole cash flow of calculations.

NPV Constant Rate Family
Calculates the NPV of certain of the constant rate functions.

Functions in the Projections NPV families are of two basic types. They are either basic PV... functions or they are PV....Q functions, where Q stands for 'quick'.

The reason for the difference is that in most simple projections functions there are two basic ways of calculating the Present Value of cashflow stream.
  • Direct Annuity Math. There are formulae for various kinds of annuity, and these can be applied to the more simple of the projections functions, but they require you to assume evenly spaced cashflows, which means that different daycount methods cannot be used. Instead, a PmtsPerYear parameter is used, which is like a simplified daycount. You can specify payments in advance or arrear on an 'n' payments-per-year basis, but you can't specify uneven periods or business days. Functions using this method are designated the PV...Q functions.
  • Full Discounted CashFlow. In these functions the cashflow is re-created internally, then complete assumptions about daycount can be used, and so DayCount and CashBasis can be used in all their options. Functions designated PV... ( ie without the ...Q suffix) use this more exact but longwinded method.
PVCon (NPVDate, DiscountRateAPR, Start, Finish, AnnualRate, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of a Con Function
PVAnnGrow (NPVDate, DiscountRateAPR, Start, Finish, AnnualRate, GrowthDates, GrowthRates, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of an AnnGrow function
PVConGrow (NPVDate, DiscountRateAPR, Start, Finish, AnnualRate, GrowthDates, GrowthRates, [RevMonthsOpt], [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of a ConGrow function
PVConGrowQ (NPVDate, DiscountRateAPR, Start, Finish, AnnualRate, GrowFrom, GrowthRatePA, ReviewMonths, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of a ConGrowQ
PVConQ (NPVDate, DiscountRateAPR, Start, Finish, AnnualRate, [PmtsPerYearOpt])Present Value of a Constant Rate (Quick Version)
NPV Stepped Rate Family
Calculates the NPV of certain of the stepped rate functions.

Functions in the Projections NPV families are of two basic types. They are either basic PV... functions or they are PV....Q functions, where Q stands for 'quick'.

The reason for the difference is that in most simple projections functions there are two basic ways of calculating the Present Value of cashflow stream.
  • Direct Annuity Math. There are formulae for various kinds of annuity, and these can be applied to the more simple of the projections functions, but they require you to assume evenly spaced cashflows, which means that different daycount methods cannot be used. Instead, a PmtsPerYear parameter is used, which is like a simplified daycount. You can specify payments in advance or arrear on an 'n' payments-per-year basis, but you can't specify uneven periods or business days. Functions using this method are designated the PV...Q functions.
  • Full Discounted CashFlow. In these functions the cashflow is re-created internally, then complete assumptions about daycount can be used, and so DayCount and CashBasis can be used in all their options. Functions designated PV... ( ie without the ...Q suffix) use this more exact but longwinded method.
PVFStep (NPVDate, DiscountRateAPR, Finish, FromDates, AnnualRates, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of an FStep function
PVTStep (NPVDate, DiscountRateAPR, Start, ToDates, AnnualRates, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of a TStep function
PVFStepQ (NPVDate, DiscountRateAPR, Finish, FromDates, AnnualRates, [PmtsPerYearOpt])PVFStep, quick version
PVTStepQ (NPVDate, DiscountRateAPR, Start, ToDates, AnnualRates, [PmtsPerYearOpt])Present Value of a stream of payments described by TStep, quick version
NPV Payments Family
Calculates the NPV of certain of the Discrete Amount functions.
PVMkPmtsSF (NPVDate, DiscountRateAPR, Start, Finish, PmtDates, Pmts, [DayCountDisc], [PeriodsDisc])NPV of a MkPmtsSF function
PVMkPmts (NPVDate, DiscountRateAPR, PmtDates, Pmts, [DayCountDisc], [PeriodsDisc])NPV of a MkPmts function
NPV Time Spread Family
Calculates the Present Value of functions with the Time Spread family.

Functions in the Projections NPV families are of two basic types. They are either basic PV... functions or they are PV....Q functions, where Q stands for 'quick'.

The reason for the difference is that in most simple projections functions there are two basic ways of calculating the Present Value of cashflow stream.
  • Direct Annuity Math. There are formulae for various kinds of annuity, and these can be applied to the more simple of the projections functions, but they require you to assume evenly spaced cashflows, which means that different daycount methods cannot be used. Instead, a PmtsPerYear parameter is used, which is like a simplified daycount. You can specify payments in advance or arrear on an 'n' payments-per-year basis, but you can't specify uneven periods or business days. Functions using this method are designated the PV...Q functions.
  • Full Discounted CashFlow. In these functions the cashflow is re-created internally, then complete assumptions about daycount can be used, and so DayCount and CashBasis can be used in all their options. Functions designated PV... ( ie without the ...Q suffix) use this more exact but longwinded method.
PVUniSpread (NPVDate, DiscountRateAPR, Start, Finish, Total, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of a UniSpread
Mathematical Calculations Category
Do mathematical calculations
Statistics Family
Do statistical analysis
LinReg (LRSwitch, YValues, XValues)Linear Regression
BestPoly (YValues, XValues)Find the best degree for regressing a polynomial
RSqrdPoly (Coefficients, YValues, XValues)R Squared For a Polynomial
PolyReg (Degree, YValues, XValues, [MinRSqrdOpt])Polynomial Regression
Range Operations Category
Do calculations on one or more ranges.

The Range Operations category is about doing calculations on ranges.

Range Lookup Family
Do various forms of Lookup using input values and range
LookupNum (InputRange, OutputRange, InputValue, [LookupSwitch])Do a general Lookup using numbers
SeekNum (InputRange, OutputRange, InputValue)Seek a number in a list of numbers and look up a corresponding number in another list
PickNum (WhichOne, OutputRange)Pick an numeric item from a list of numeric items
SortPick (WhichOne, Values, [Direction])Pick a number from a list of numbers after sorting the list first
Interp (InputRange, OutputRange, InputValue)Interpolate
InterpCollar (InputRange, OutputRange, InputValue, [MinimumOpt], [MaximumOpt])Interpolate keeping the result within bounds
InterpExtrap (InputRange, OutputRange, InputValue)Interpolate, but also extrapolate if input outside the range of the lookup.
Interpolate (InputRange, OutputRange, InputValue, [Extrap], [MinimumOpt], [MaximumOpt])Do a lookup that interpolates between values
InterpDate (InputDateRange, OutputRange, InputDateValue, [DayCount], [Periods])Interpolate for Date inputs.
InterpDateCollar (InputDateRange, OutputRange, InputDateValue, [MinimumOpt], [MaximumOpt], [DayCount], [Periods])Interpolate for Date inputs., restraining the result within bounds
InterpDateExtrap (InputDateRange, OutputRange, InputDateValue, [DayCount], [Periods])Interpolate for Date inputs., and extrapolate if required
InterpolateDate (InputDateRange, OutputData, InputDateValue, [Extrap], [MinimumOpt], [MaximumOpt], [DayCount], [Periods])Do a lookup that interpolates between date values
PickMatrix (OutputMatrix, i, j)PickMatrix
LookUpNum2D (OutputMatrix, AxisRangeX, AxisRangeY, InputValueX, InputValueY, [LookupSwitch])Lookup2D
SortNum (Values, [Direction])Sort a range of numbers
Range Sum Family
Various ways or summing the items in a range
Summing elements in a range in specific ways is covered by Excel's SUMIF function, but you may want to look at using one of our, very direct, functions that fulfil many common needs:
  • SumNeg and SumPos sum just the negative or positive elements respectively.
  • SumCat is one of our most useful functions and adds all the elements that correspond to certain categories. This function is great for analysing lists or accounting entries.
  • A related function SumCats, the plural form, allows you to specify more than one category to add. Categories are specified by integer numbers.
SumCat (Values, Categories, ChosenCategory)Sum a range by Category number
SumCats (Values, Categories, ChosenCategories)Sum by more than one Category number
SumPos (Values)Sums the positive numbers in a range
SumNeg (Values)Sums the negative values in a range
SumDiv (Values1, Values2)Sum of one range divided by another
SumBounds (Minimum, Maximum, Values)Sum within Bounds
SumSubRange (Values, iStart, iFinish)Sum a subrange within a range
Range Reference Family
Do calculations that determine or manipulate cell references
Coordinates (RangeRef)Get the cell co-ordinates given a reference to a cell
WorkSheetName (RangeRef)Get the sheet name given a reference to a cell
RefPlus (RangeRef, Rows, Cols)Take a cell reference and return a text reference plus rows and columns
GetRef (RangeRef)Get a Cell Reference as text, given a reference to a cell
Range Calcs Family
Special calculations using ranges
MinBetween (Minimum, Maximum, Values)Determine the minimum value providing that value is within a certain minimum and a maximum
MaxBetween (Minimum, Maximum, Values)Determine the maximum value providing that value is within a certain minimum and a maximum
MeanNZ (Values)determine the Mean of all non zero items
WeightAv (Values, Weights)Weighted Average
Text Category
Do text manipulations

The Text category is about manipulating strings of text.

Numbers To Text Family
Functions that are about converting numbers to text.
MoneyText (Amount, [Currency], [Language])Describe a monetary value as text
Real Estate Category
Project business forecasts for the Property Industry, such as Rent.

Real Estate Projections is a category that has specialised projections functions for the analysis of commercial property. Although the functions contained are often very like functions in other categories, particularly like the Stepped Rate Projections functions such as TStep and TStepGrowMkt, the Real Estate functions are particularly designed for situations that that feature a review to market. Some features, like the Net Effective Rate Discount mechanism, these are unavailable in the generic Stepped Rate functions.

There are 6 families in this category:

  • The Rent family has the basic rent functions that don't have any need for a stepped rent profile (eg RentGrow) as well as the functions for determining the NextReview and LastReview.
  • The Rent From family has functions for calculating stepped rents, either without any market review (eg FStepRent) or the more usual case where market reviews start at the end of the stepped rent period (eg FStepRentGrow). Each stepped rent runs from the corresponding from date.
  • The Rent To family is just like the Rent From family, but where the dates governing the steps are 'To' dates, ie the corresponding rent runs until the to Date.
  • The Rent Reletting family has version of the Rent From and Rent To family that relet, with void periods, rent-free etc.
  • The NPV Rent Projections family calculates net present values of certain rent functions (eg PVTStepRentFcst calculates the NPV of PVTStepRent).
  • The Valuation family which has functions for calculating property yields and valuations using the conventional form of property math used in the UK.
  • The Valuation DCF family which has functions for calculating property yields and valuations using our own DCF-derived math, guaranteed to be consistent with conventional financial annuity mathematics.

Rent Family
Make rental projections
The Rent family has basic and straightforward functions for rent projections that don't involve the complexities of stepped rents or reletting.

It has two basic rent functions, depending on whether you want to specify your market rent forecast using GrowthRates or FcstVals, these being RentGrow and RentFcst.

It's also useful know when the last or next review occurs, using NextReview and úPrevReviewú.

A note on a couple of the variables used:
  • ReviewMonths is for establishing the length of time between market reviews (eg 5 years = 60 months).
  • ReviewDiscount does a couple of jobs. Firstly it determines the extent of any discount that should be applied to the market rent at market review (but not of course to the InitialRent. Now, this variable can be positive or negative, and the discount applied is the same (ie both 10% and -10% cause a 10% reduction to the market rent). The difference is that if ReviewDiscount is negative, reviews are upwards only. A zero or positive ReviewDiscount will mean that reviews are upwards or downwards. If you want no discount but upwards only, put in a small negative number (eg 1E-9).
  • ProjMode is 0 to calculate how much rent accrues in the time period, and 1 to calculated how much is paid in the time period. Both of these settings assume the default Business Functions assumption that the finish date ie ExpiryOrBreak has no rent accruing on that day. Since property leases usually do have rent accruing on the last day of the lease, you may want to use ProjMode=2 (accruals, including finish date) or 3 (cash, including finish date).
RentGrow (Time, Base, RentStartDate, ExpiryOrBreak, FirstRevDate, MktAnnRent, InitialRent, GrowthDates, GrowthRates, ReviewMonths, ReviewDiscount, [DayCount], [Periods], [ProjMode])Rent with Growth
RentFcst (Time, Base, RentStartDate, ExpiryOrBreak, FirstRevDate, InitialRent, FcstVals, StartFcst, FcstBase, ReviewMonths, ReviewDiscount, [DayCount], [Periods], [ProjMode])Rent using a rental forecast at reviews
LastReview (TheDate, FirstRevDate, ReviewMonths, [RoundDown])Determine the date of the last rent review
NextReview (TheDate, FirstRevDate, ReviewMonths, [RoundDown])Determine the date of the next rent review
Rent From Family
The Rent From family is for calculating stepped rents where the stepped rents from from each corresponding date in FromDates.*

The basic function that does not have market rent reviews and just the stepped rents in FStepRent.

For market reviews, it depends whether you want to specify your rent forecast using GrowthRates or FcstVals, you can use FStepRentGrow or FStepRentFcst.

Variations to the above functions include FStepRentFcstCollar, where a cap and floor can be put against each rent review.


* Note: with 'From' dates the stepped rent runs from and including the 'from' date. With 'To' dates, however, the stepped runs up until but excluding the 'To' date.
FStepRent (Time, Base, ExpiryOrBreak, FromDates, AnnRents, [DayCount], [Periods], [ProjMode])Stepped Rent using review dates that specify when rents change, where the dates are FromDates rather than 'up until' dates
FStepRentGrow (Time, Base, ExpiryOrBreak, MktAnnRent, FromDates, AnnRents, GrowthDates, GrowthRates, ReviewMonths, [RevDiscountOpt], [DayCount], [Periods], [ProjMode])Stepped Rent using review dates that specify when rents change, where the dates are FromDates rather than 'up until' dates
FStepRentFcst (Time, Base, ExpiryOrBreak, FromDates, AnnRents, FcstVals, StartFcst, FcstBase, ReviewMonths, [RevDiscountOpt], [DayCount], [Periods], [ProjMode])Stepped Rent using a supplied forecast, using FromDates
FStepRentFcstCollar (Time, Base, ExpiryOrBreak, FromDates, AnnRents, FcstVals, StartFcst, FcstBase, AnnRentCaps, AnnRentFloors, ReviewMonths, [RevDiscountOpt], [DayCount], [Periods], [ProjMode])Stepped Rent using a supplied forecast, using FromDates where the rent is 'collared' between caps and floors
Rent To Family
The Rent To family is for calculating stepped rents where the stepped rents from To each corresponding date in ToDates.*

The basic function that does not have market rent reviews and just the stepped rents in TStepRent.

For market reviews, it depends whether you want to specify your rent forecast using GrowthRates or FcstVals, you can use TStepRentGrow or TStepRentFcst.

Variations to the above functions include TStepRentFcstCollar, where a cap and floor can be put against each rent review.

* Note: with 'From' dates the stepped rent runs from and including the 'from' date. With 'To' dates, however, the stepped runs up until but excluding the 'To' date.
TStepRent (Time, Base, RentStartDate, ReviewDates, AnnRents, [DayCount], [Periods], [ProjMode])Stepped Rent using review dates that specify when rents change
TStepRentGrow (Time, Base, RentStartDate, ExpiryOrBreak, MktAnnRent, ReviewDates, AnnRents, GrowthDates, GrowthRates, ReviewMonths, [RevDiscountOpt], [DayCount], [Periods], [ProjMode])Stepped Rent, using a forecast for rental growth at reviews
TStepRentFcst (Time, Base, RentStartDate, ExpiryOrBreak, ReviewDates, AnnRents, FcstVals, StartFcst, FcstBase, ReviewMonths, [RevDiscountOpt], [DayCount], [Periods], [ProjMode])Stepped Rent using a rental forecast at reviews
TStepRentGrowCollar (Time, Base, RentStartDate, ExpiryOrBreak, MktAnnRent, ReviewDates, AnnRents, AnnRentCaps, AnnRentFloors, GrowthDates, GrowthRates, ReviewMonths, [RevDiscountOpt], [DayCount], [Periods], [ProjMode])Stepped Rent using a forecast for rental growth at reviews, keeping the rental level within a collar
TStepRentFcstCollar (Time, Base, RentStartDate, ExpiryOrBreak, ReviewDates, AnnRents, FcstVals, StartFcst, FcstBase, AnnRentCaps, AnnRentFloors, ReviewMonths, [RevDiscountOpt], [DayCount], [Periods], [ProjMode])Stepped Rent using a rental forecast at reviews, keeping the rental level within a collar
Rent Reletting Family
The rent reletting family has variations of the standard rental functions that permit the function to relet itself after a void and rentfree period. This makes them useful for occasions where you are modelling the space that is under lease rather than just the lease itself, for example when assessing the long-term value of a building.
The Rent Reletting family has functions that calculate the rent of a main lease, just like the Rent From and Rent To families, and then keeps reletting after the ExpiryOrBreak of the main lease. The way the rent reletting functions work is:
  • The main lease is calculated just as in the corresponding single lease rent function, for example the main lease of FStepRentGrowR is simply a FStepRentGrow.
  • After a void period of ReletVoid months where no rent is received, the property is assumed relet.
  • Rent commences after a further ReletRF months, the rent be determined as the market rent at the commencement of this second lease (without any ReviewDiscount applied).
  • The first rent review of the second lease occurs ReviewMonths after the commencement of the second lease, and market rent reviews continue at intervals of ReviewMonths, to the market rent, less any ReviewDiscount applied.
  • The second lease expires after ReletTerm years after the commencement of the second lease, then a ReletVoid is applied, then a third lease commences, and so on.
The main functions of this group are the rental functions, which are:
For modelling properties that relet, you will probably want to calculate void costs, refitting costs and fees. It may be that we will include something specific in the future on this, but for now there plenty of general purpose functions in the library that can help:
RentFcstR (Time, Base, RentStartDate, ExpiryOrBreak, FirstRevDate, InitialRent, FcstVals, StartFcst, FcstBase, ReviewMonths, ReviewDiscount, [ReletVoid], [ReletRF], [ReletTerm], [DayCount], [Periods], [ProjMode])RentFcst with reletting
RentGrowR (Time, Base, RentStartDate, ExpiryOrBreak, FirstRevDate, MktAnnRent, InitialRent, GrowthDates, GrowthRates, ReviewMonths, ReviewDiscount, [ReletVoid], [ReletRF], [ReletTerm], [DayCount], [Periods], [ProjMode])Rent Grow with Reletting
FStepRentGrowR (Time, Base, ExpiryOrBreak, MktAnnRent, FromDates, AnnRents, GrowthDates, GrowthRates, ReviewMonths, ReviewDiscount, [ReletVoid], [ReletRF], [ReletTerm], [DayCount], [Periods], [ProjMode])Stepped Rent using 'from' dates and growth rates with reletting
FStepRentFcstR (Time, Base, ExpiryOrBreak, FromDates, AnnRents, FcstVals, StartFcst, FcstBase, ReviewMonths, ReviewDiscount, [ReletVoid], [ReletRF], [ReletTerm], [DayCount], [Periods], [ProjMode])Stepped Rent using 'from' dates and a market forecast with reletting
TStepRentGrowR (Time, Base, RentStartDate, ExpiryOrBreak, MktAnnRent, ReviewDates, AnnRents, GrowthDates, GrowthRates, ReviewMonths, ReviewDiscount, [ReletVoid], [ReletRF], [ReletTerm], [DayCount], [Periods], [ProjMode])Stepped Rent using 'to' dates and a market forecast with reletting
TStepRentFcstR (Time, Base, RentStartDate, ExpiryOrBreak, ReviewDates, AnnRents, FcstVals, StartFcst, FcstBase, ReviewMonths, ReviewDiscount, [ReletVoid], [ReletRF], [ReletTerm], [DayCount], [Periods], [ProjMode])TStepRentFcst with reletting
LastReviewR (TheDate, ExpiryOrBreak, FirstRevDate, ReviewMonths, [ReletVoid], [ReletTerm], [RoundDown])Like LastReview but with reletting
NextReviewR (TheDate, ExpiryOrBreak, FirstRevDate, ReviewMonths, [ReletVoid], [ReletTerm], [RoundDown])Like NextReview but with reletting
ReletOccurs (Time, Base, ExpiryOrBreak, [ReletVoid], [ReletRF], [ReletTerm])Determine whether a relet occurs
RentDateInfo (TheDate, LeaseStartDate, RentStartDate, ExpiryOrBreak, Reviews, ReviewMonths, RentDateSwitch, [ReletVoid], [ReletRF], [ReletTerm], [RoundDown])Rent Review Information
NPV Rent Projections Family
PVTStepRent (NPVDate, DiscountRateAPR, RentStartDate, ReviewDates, AnnRents, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of the TStepRent function
PVTStepRentFcst (NPVDate, DiscountRateAPR, RentStartDate, ExpiryOrBreak, ReviewDates, AnnRents, FcstVals, StartFcst, FcstBase, ReviewMonths, [RevDiscountOpt], [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of the TStepRentFcst function
PVTStepRentGrow (NPVDate, DiscountRateAPR, RentStartDate, ExpiryOrBreak, MktAnnRent, ReviewDates, AnnRents, GrowthDates, GrowthRates, ReviewMonths, [RevDiscountOpt], [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of the úStepRentGrowú function
PVRentGrow (NPVDate, DiscountRateAPR, RentStartDate, ExpiryOrBreak, FirstRevDate, MktAnnRent, InitialRent, GrowthDates, GrowthRates, ReviewMonths, ReviewDiscount, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of the RentGrow function
PVRentFcst (NPVDate, DiscountRateAPR, RentStartDate, ExpiryOrBreak, FirstRevDate, InitialRent, FcstVals, StartFcst, FcstBase, ReviewMonths, ReviewDiscount, [DayCount], [CashBasis], [DayCountDisc], [PeriodsDisc])Present Value of the RentFcst function
Valuation Family
Specialist functions for property valuation
The Valuation family has functions for calculating exit capitalisation values and equivalent yields. This family is pretty much orientated to the UK approach to real estate mathematics. There is another family, the Valuation DCF family, that uses own own derivation of property math which you should also consider. We are interested in adding functions for the US - please get in touch with us if you have an idea. Our understanding is that the US uses DCF fairly exclusively.

The functions in this family fall into 2 main groups:
  • There are functions that calculate capitalised values and equivalent yields, where which one you use depends on the form of your input data (they all use the same underlying math). If you have a passing rent and a market rent, with possibly a rent free period, then EqYield and CapValue will do this simple job rather well, either for Nominal or True APR Yields. If you have a number of stepped rents for which you want to use the conventional property math, there is the generalised form EqYieldG and CapValueG. Finally, if you want to use dates rather than deferrals in months, you can use the date versions EqYieldGD and CapValueGD. we advise that you fully understand the math before using a large number of rental steps since the math does make some approximations that get larger with time deferred.
  • There are utility functions that use the same underlying math to convert True to Nominal Yields and vice-versa (NomToTrue and TrueToNom), for any specification of rental frequency. And there is the well-know standard YPPDef, the 'deferred Years Purchase of a Perpetuity' formula.

This family has a strong technical element to it and we have written a paper, available online, that details our approach:

Just a Note: Be Consistent With Your Yields and Rental Frequency
The functions work fine with the all the permutations of Nominal and True Yields and different payments per year, because the Math is designed to cope with all eventualities. However, please try to be consistent - a practise we have seen often, for example, is to take a value obtained by the assumption of rents annually in arrear (CapValue, CapValueG) and then use that value to calculate an equivalent yield (EqYield,EqYieldG) assuming payments quarterly. We don't think that calculating a value based on incorrect assumptions can be right in any circumstances, and it would be much better to, say use a Nominal Yield with the appropriate rental frequency to get the right value, and then proceed to whatever you want to do from there. We therefore find it hard to endorse this approach (but we're are willing to listen if you think WE've got it wrong!).
CapValue (Yield, MktAnnRent, PassingAnnRent, MonthsNextRev, [RentFreeMonths], [CostsPerc], [PmtsPerYearOpt], [YieldTypeOpt])Cap Value (UK Real Estate Conventional Math)
EqYield (Valuation, MktAnnRent, PassingAnnRent, MonthsNextRev, [RentFreeMonths], [CostsPerc], [PmtsPerYearOpt], [YieldTypeOpt])Equivalent Yield (UK Real Estate Conventional Math)
CapValueG (Yield, AnnRents, DeferralMonths, [CostsPerc], [PmtsPerYearOpt], [YieldTypeOpt])Cap Value (UK Real Estate Conventional Math), extended version
EqYieldG (Valuation, AnnRents, DeferralMonths, [CostsPerc], [PmtsPerYearOpt], [YieldTypeOpt])Equivalent Yield (UK Real Estate Conventional Math), extended version
CapValueGD (ValuationDate, Yield, FromDates, AnnRents, [CostsPerc], [PmtsPerYearOpt], [YieldTypeOpt])Cap Value (UK Real Estate Conventional Math), extended version, extended version
EqYieldGD (ValuationDate, Valuation, FromDates, AnnRents, [CostsPerc], [PmtsPerYearOpt], [YieldTypeOpt])Equivalent Yield (UK Real Estate Conventional Math), extended version, extended version
NomToTrue (NomYield, PmtsPerYear)Convert a Nominal (Simple) Yield to a True (APR) Yield
TrueToNom (TrueYield, PmtsPerYear)Convert a True (APR) Yield to a Nominal (Simple) Yield
YPPDef (Yield, [MonthsDeferred], [PmtsPerYearOpt], [YieldTypeOpt])Years Purchase of a Perpetuity
Valuation DCF Family
Specialist functions for property valuation using DCF Math
The Valuation DCF family has functions for calculating exit capitalisation values and equivalent yields. This family uses our own derived property math, which we call DCF math because it adheres as strictly as it could do the laws of DCF. There is another family, the Valuation family, that uses the type of math conventional used in the UK. We are interested in adding functions for the US - please get in touch with us if you have an idea. Our understanding is that the US uses DCF fairly exclusively.

The functions in this family fall into 2 main groups:
  • There are functions that calculate capitalised values and equivalent yields, where which one you use depends on the form of your input data (they all use the same underlying math). If you have a passing rent and a market rent, with possibly a rent free period, then EqYieldDCF and CapValueDCF will do this simple job rather well, either for Nominal or True APR Yields. If you have a number of stepped rents there is the generalised form EqYieldDCFG and CapValueDCFG. Finally, if you want to use dates rather than deferrals in months, you can use the date versions EqYieldDCFGD and CapValueDCFGD. Unlike the conventional math used in the Valuation family, we guarantee that our DCF math is consistent with how financial assets (eg annuities) are normally valued, and as such the stepped versions work just fine without any inaccurracy.
  • There are utility functions that use the same underlying math to convert True to Nominal Yields and vice-versa (NomToTrueDCF and TrueToNomDCF), for any specification of rental frequency. And there is the well-know standard YPPDefDCF, the 'deferred Years Purchase of a Perpetuity' formula. To this we add the YPADefDCF, which is the annuity version. There are other functions also that derive from the same approach to present valuing rental streams, such as TheorYield (theoretical yield), ImpGrowth, ImpDiscRate and PVRent.

This family has a strong technical element to it and we have written a paper, available online, that details our approach:
CapValueDCF (Yield, DiscountRateAPR, PmtsPerYear, MktAnnRent, PassingAnnRent, ReviewMonths, MonthsNextRev, [RentFreeMonths], [CostsPerc], [YieldTypeOpt])Cap Value (DCF Math)
EqYieldDCF (Valuation, DiscountRateAPR, PmtsPerYear, MktAnnRent, PassingAnnRent, ReviewMonths, MonthsNextRev, [RentFreeMonths], [CostsPerc], [YieldTypeOpt])Equivalent Yield (DCF Math)
CapValueDCFG (Yield, DiscountRateAPR, PmtsPerYear, AnnRents, DeferralMonths, ReviewMonths, [CostsPerc], [YieldTypeOpt])Cap Value (DCF Math)
EqYieldDCFG (Valuation, DiscountRateAPR, PmtsPerYear, AnnRents, DeferralMonths, ReviewMonths, [CostsPerc], [YieldTypeOpt])Equivalent Yield (DCF Math)
CapValueDCFGD (ValuationDate, Yield, DiscountRateAPR, PmtsPerYear, FromDates, AnnRents, ReviewMonths, [CostsPerc], [YieldTypeOpt])Cap Value (DCF Math)
EqYieldDCFGD (ValuationDate, Valuation, DiscountRateAPR, PmtsPerYear, FromDates, AnnRents, ReviewMonths, [CostsPerc], [YieldTypeOpt])Equivalent Yield (DCF Math)
NomToTrueDCF (NomYield, PmtsPerYear, DiscountAPR)Convert a Nominal (Simple) Yield to a True (APR) Yield, DCF Math
TrueToNomDCF (TrueYield, PmtsPerYear, DiscountAPR)Convert a True (APR) Yield to a Nominal (Simple) Yield, DCF Math
YPADefDCF (DiscountRateAPR, [MonthsDeferred], AnnuityEndMonths, [PmtsPerYearOpt])Years Purchase of a Annuity (using DCF Math)
YPPDefDCF (Yield, DiscountRateAPR, [MonthsDeferred], [RevMonths], [PmtsPerYearOpt], [YieldTypeOpt])Years Purchase of a Perpetuity (using DCF Math)
TheorYield (DiscountRateAPR, GrowthRatePA, [RevMonths], [PmtsPerYearOpt], [YieldTypeOpt])Theoretical Yield
ImpGrowth (Yield, DiscountRateAPR, [RevMonths], [PmtsPerYearOpt], [YieldTypeOpt])Growth implied by a yield and a discount rate
ImpDiscRate (Yield, GrowthRatePA, [RevMonths], [PmtsPerYearOpt], [YieldTypeOpt])Implied Discount Rate APR pa
PVRent (DiscountRateAPR, GrowthRatePA, [RevMonths], [PmtsPerYearOpt])PV of $1 per year in rent (exponentially growing perpetuity with periodic reviews)
Simulation Modelling Category
Simulation Modelling

The Simulation Modelling Category principally consists of the Monte Carlo Simulation Category.

Monte Carlo Family
Functions for use in Monte Carlo simulation models
  • This family has the distribution functions you need for a Monte-Carlo simulation, such as DistUniform and DistNormalú, and the random number functions you may need as well, such as Random.
  • There are types of function for accessing a distribution. You can use functions specific for a distribution like úDistTriangleú, or you can also use a 'do everything' function like Distribution. Distribution is quite handy because if you want to change the type of distribution you have, you just change one parameter not the whole function.
Distribution (DistMin, DistMid, DistMax, DistType, [RandOpt])Generate a value from a probability distribution
PartialDependency (AxisRangeX, InputValueX, InputValuesMin, InputValuesMid, InputValuesMax, DistType, [RandOpt])Generate a value from a probability distribution where there is a graphical partial dependency
DistCumulative (CumProbs, Values, [CumSwitch], [RandOpt])Generate a value from a specified cumulative probability distribution
DistUniform (DistMin, DistMax, [RandOpt])Generate a value from a uniform probability distribution
DistTriangular (DistMin, DistMid, DistMax, [RandOpt])Generate a value from a triangular probability distribution
DistDoubleTri (DistMin, DistMid, DistMax, [RandOpt])Generate a value from a double triangular distribution
DistNormal (StanDev, Mean, [RandOpt])Generate a value from a normal probability distribution
DistLogNormal (StanDev, Mean, [RandOpt])Generate a value from a log-normal probability distribution
RandNum ()Compute a semi random number
NRandom ()Number of Random Number in Sequence
PickRandom (WhichOne)Pick a specific random number from a set of random numbers
DescribeDistType (DistType)Describes the value used for DistType
CumProb (Deviate, DistMin, DistMid, DistMax, DistType, [ClipTailLevel])Find the cumulative probability level for a given deviate value from a distribution
Random ()Compute a semi random number
Treasury Category
Treasury

The Treasury Category contains functions used for Treasury management and analysis.

Yield Curve Family
Calculating aspects of the interest rate yield curve
ZeroCoupon (CalculationDate, ParBaseDate, ParDates, ParYields, ParCoupFreqs, CurrentPrices, [OutputRate], [DCount], [BusDaySwitch])Determines the Zero Coupon Discount factor using the Bootstrap method
FutureToSpot (FromDate, ToDate, BaseDate, ToDates, FutureRatesAPR, [OutputRate], [DCount])Calculate a Spot Rate given a series of Future Rates
SpotToSpot (FromDate, ToDate, BaseDate, ToDates, SpotRatesAPR, [OutputRate], [DCount])Calculate a FutureRate given a series of SpotRates.
ZeroCouponDF (CalculationDate, ParBaseDate, ParDates, ParYields, ParCoupFreqs, CurrentPrices, [DCount], [BusDaySwitch])Determines the Zero Coupon Discount factor using the Bootstrap method
ZeroCouponAPR (CalculationDate, ParBaseDate, ParDates, ParYields, ParCoupFreqs, CurrentPrices, [DCount], [BusDaySwitch])Determines the Zero Coupon Discount Rate as an APR using the Bootstrap method
ZeroCouponCont (CalculationDate, ParBaseDate, ParDates, ParYields, ParCoupFreqs, CurrentPrices, [DCount], [BusDaySwitch])Determines the Zero Coupon rate as a Continuous Rate using the Bootstrap method
Accounting And Tax Category
Accounting and Tax
Accounting Family
Functions used in Accounting and Tax.
DepreciatedValue (TheDate, InitialDate, StartDep, LifeOrDecRate, DepType, InitialValue, [FinalValue], [DayCount], [Periods])Calculate the Depreciated Value
Depreciate (Time, Base, InitialDate, StartDep, LifeOrDecRate, DepType, InitialValue, [FinalValue], [DayCount], [Periods])Depreciate
Tax Family
Do calculations of tax
FAvRate (Amount, MarginalRates, FromThresholds)Calculates the average tax rate given a series of marginal tax rates effective FROM, or ABOVE certain thresholds
TAvRate (Amount, MarginalRates, ToThresholds)Calculates the average tax rate given a series of marginal tax rates effective UP TO certain thresholds
FAvRateTD (Amount, MarginalRates, FromThresholds, FromDates, InputDateValue)Average Rate (Time Dependent). Calculates the average tax rate given a series of marginal tax rates effective FROM certain dates and FROM, or ABOVE certain thresholds
TAvRateTD (Amount, MarginalRates, ToThresholds, ToDates, InputDateValue)Average Rate (Time Dependent). Calculates the average tax rate given a series of marginal tax rates effective UP TO certain dates and UP TO certain thresholds.
AverageTaxRate (Amount, MarginalRates, ToThresholds)Calculates the average tax rate given a series of marginal tax rates effective UP TO certain thresholds
Banking Category
Do calculations on financial loans
Interest Family
Loan Sign Convention

+ve-ve
Loan Balances:You OweYou are Owed
Cash Balances:NAYou have Cash
InterestYou ReceiveYou Pay
PrincipalYou DrawYou Repay
Interest (Time, Base, Start, Finish, InterestSimple, Balance, [DayCount], [PeriodsInt], [ProjMode])Calculate Interest for a timeperiod for a Balance that has Start and Finish dates.
FInterest (Time, Base, FromDatesInt, IntRatesSimple, Finish, FromDates, Balances, [DayCount], [PeriodsInt], [ProjMode])Calculate Interest using 'From' Dates
TInterest (Time, Base, FromDatesInt, IntRatesSimple, Start, ToDates, Balances, [DayCount], [PeriodsInt], [ProjMode])Calculate Interest using 'To' dates
TotInterestSF (Start, Finish, InterestSimple, Balance, [DayCount], [PeriodsInt])The total interest accrued between two dates
Loan Family
Loan Sign Convention

+ve-ve
Loan Balances:You OweYou are Owed
Cash Balances:NAYou have Cash
InterestYou ReceiveYou Pay
PrincipalYou DrawYou Repay
LoanX (Time, Base, FromDatesInt, IntRatesSimple, AdvanceDates, NetAdvances, RepaymentDate, [RollTo], [LoanOutput], [DayCount], [PeriodsInt], [ProjMode])Calculate a loan (interest, principal and balance), extended version
Loan (Time, Base, FromDatesInt, IntRatesSimple, AdvanceDates, NetAdvances, RepaymentDate, [LoanOutput], [DayCount], [PeriodsInt], [ProjMode])Calculate a loan (interest, principal and balance)
Residual Cash Family
These functions are of the bottom line of a cash flow where you want to deal with cash balances and surpluses. Interest is accrued on a daily basis using an APR interest rate. Because these functions use a preceding balance you may not get exactly the same result with a quarterly or yearly cash flow (time discretisation error), but we've done everything possible within the arithmetical framework to make the results similar. Because these functions, although tremendously useful, deal in an area where accuracy is necessariliy compromised, we don't use DayCount/Periods options because it's not justified, and instead use the more approximate, theoretically based, PmtsPerYear mechanism.
Loan Sign Convention

+ve-ve
Loan Balances:You OweYou are Owed
Cash Balances:NAYou have Cash
InterestYou ReceiveYou Pay
PrincipalYou DrawYou Repay
Overdraft (Time, Base, BFBalance, CashFlow, IntAPRDebt, PmtsPerYear, [LoanOutput])Calculates the interest, principal etc for an overdraft
CashAcct (Time, Base, BFBalance, CashFlow, IntAPRCash, PmtsPerYear, [LoanOutput])Calculates the interest, principal etc for a cash surplus
FlexAcct (Time, Base, BFBalance, CashFlow, InterestAPR, PmtsPerYear, [LoanOutput])Calculates the interest, principal etc for both an overdraft and a cash surplus, where the interest on cash is the same as the interest in debt
CurrentAcct (Time, Base, BFBalance, CashFlow, IntAPRDebt, IntAPRCash, PmtsPerYear, [LoanOutput])Calculates the interest, principal etc for both an overdraft and a cash surplus, where there are different interest rates for debt and cash surplus
Equity Trading Family
Calculating monthly equity returns, etc
BetaIndex (Values, [IndexValues])Beta of a stock, given Index data
BetaReturns (Returns, IndexReturns)The Beta value of a Stock, given data in the form of periodic returns
DescribeReturn (ReturnSwitch)Describe the Return Switch in the TotalReturn function
TotalReturn (HistoryPeriod, CalculationDate, Dates, Values, [CashFlowDates], [Cash_Flows], [ReturnSwitch], [IndexValues], [IndexCashDates], [IndexCashFlows], [ExchangeRates])Total return Analysis
Oil and Gas Category
Oil and Gas

The Oil and Gas Category has functions specifically aimed the oil and gas exploration and production industry.

Decline Curve Family
ProdProf (Time, Base, Reserves, FromDates, AnnualRates, DeclinePoint, AbandonRate, [DeclineParam], [DayCount], [Periods], [ProjMode])Generate an oil production profile based on pre-determined production rates and a decline curve
AbandonDate (Reserves, FromDates, AnnualRates, DeclinePoint, AbandonRate, [DeclineParam], [DayCount], [Periods])Calculate the Abandonment Date given an oil production profile based on pre-determined production rates and a decline curve
DescribeDecline (DeclineParam)Describe in a text string the DeclineParam setting
Decline (Yrs, Reserves, PlateauRate, AbandonRate, [DeclineParam])Cumulative production in decline up to a certain time
StartDecline (Reserves, FromDates, AnnualRates, DeclinePoint, [DayCount], [Periods])Start of Decline Period
Miscellaneous Category
Miscellaneous functions

The Miscellaneous Category is for familes of functions that don't easily include elsewhere. most important of which is the Library Information Category, containing functions that explain some of the key library variables and their values.

Special Arithmetic Family
Do specific arithmetic operations which won't return errors or unusual (but simple) arithmetic
DiffGaps (Value, Value2, GapStarts, GapEnds)Difference taking into account gaps
AddGaps (Value, Increment, GapStarts, GapEnds)Add two numbers allowing for gaps in the addition
Safe (Input, OnErr, OnNum, OnNA, OnName, OnRef, OnValue, OnDiv0, OnNull)Safely handle Calculations that may generate errors
Rounding Family
Do rounding of numbers or dates
SRound (Value, Nearest, [StartingFromOpt])Special Round
SFloor (Value, Nearest, [StartingFromOpt])Special Floor
SCeiling (Value, Nearest, [StartingFromOpt])Special Ceiling
RoundWeekDay (TheDate, DayInWeek, [RoundMethod])Round to nearest Weekday
RoundMonthDay (TheDate, DayInMonth, [RoundMethod])Round to nearest day of month
RoundMD (TheDate, MonthInYear, DayInMonth, [RoundMethod])Round to nearest month and day in month
RoundDateSeq (TheDate, AnnDateSeq, [RoundMethod])Round Date Sequence
DateRound (TheDate, AnnDateSeq)Round a date
DateFloor (TheDate, AnnDateSeq)Rounds a date to a floor
DateCeiling (TheDate, AnnDateSeq)Rounds a date to a ceiling
Library Information Family
Provides textual information on various functions and variables in the library
The Library Information family provides information, as a text string, on various functions and variables in the library.
Advice
  • Use these functions with your input cells in a spreadsheet so that when printed out you can see what settings have been used. It's very easy to forget and its a common source of errors.
DescribeCombineSwitch (CombineSwitch)Describe CombineSwitch
DescribePmtsPerYear (PmtsPerYearOpt)Describe the PmtsPerYear setting
DescribeLevelSwitch (LevelSwitch)Describe in a text string the LevelSwitch setting
DescribeBD (BusDaySwitch, [AnnHols], [HolDates])Describe Business Day convention
DescribeDayCount (DayCount)Describe DayCount Method
DescribePeriods (Periods)Describe Periods used in Daycount Calculation
DescribeLookup (LookupSwitch)Describe in a text string the LookupSwitch setting
DescribeRounding (RoundMethod)Describe the rounding method used
DescribeLoanOutput (LoanOutput)Describe Loan Output
Flow Of Control Family
Functions that involve dictating which value is used for subsequent calculations.
This family adds a few functions to the commomly use IF and other logical functions, drawn from conventional programming constructs, principally 'If...else' and 'Switch'.

Three suffixes are used in this family:
  • 'R' suffix means that ranges are used as inputs, instead of individual cells.
  • 'T' means the function is deisgned for text inputs and outputs, rather than numerical.
  • 'RT' is a combination of the above, ie using text ranges.
Switch (SwitchVar, Default, Case1, Res1, [Case2], [Res2], [Case3], [Res3], [Case4], [Res4], [Case5], [Res5], [Case6], [Res6], [Case7], [Res7], [Case8], [Res8], [Case9], [Res9], [Case10], [Res10], [Case11], [Res11], [Case12], [Res12])Switch (Select Case)
SwitchR (SwitchVar, Default, Cases, Results)Switch with Ranges
SwitchT (SwitchVar, DefaultText, Case1, ResText1, [Case2], [ResText2], [Case3], [ResText3], [Case4], [ResText4], [Case5], [ResText5], [Case6], [ResText6], [Case7], [ResText7], [Case8], [ResText8], [Case9], [ResText9], [Case10], [ResText10])Switch (Select Case) for Text
SwitchRT (SwitchVar, DefaultTxt, Cases, ResultsText)Switch (Select Case) for Text Ranges
IfElse (Default, Cond1, Res1, [Cond2], [Res2], [Cond3], [Res3], [Cond4], [Res4], [Cond5], [Res5], [Cond6], [Res6], [Cond7], [Res7], [Cond8], [Res8], [Cond9], [Res9], [Cond10], [Res10], [Cond11], [Res11], [Cond12], [Res12])If...ElseIf...Else
IfElseR (Default, Conds, Results)If...ElseIf...Else with Ranges
IfElseT (DefaultText, Cond1, ResText1, [Cond2], [ResText2], [Cond3], [ResText3], [Cond4], [ResText4], [Cond5], [ResText5], [Cond6], [ResText6], [Cond7], [ResText7], [Cond8], [ResText8], [Cond9], [ResText9], [Cond10], [ResText10])If...ElseIf...Else for Text
IfElseRT (DefaultTxt, Conds, ResultsText)If...ElseIf...Else with Ranges for text
Comparisons Family
CollarDate (TheDate, Date1, Date2)Collar Date
InBetween (Amt, Minimum, Maximum, [CompareOpt])Is a value Inbetween
Collar (Amt, Minimum, Maximum)Collars a number