Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    Rate Projections Functions Walkthrough
    Discussion of the logic of the Rate Projections functions
    The central core of the projections section of the library revolves around two categories of functions:
    Why so many families? Why so many functions? Whats the logical progression? Lets find out.

    Constant Rate

    The Constant Rate family is about projecting a single constant rate. It does not have to be a flat rate however, it can be a growing rate. Hence, as well as Con you also get AnnGrow and ConGrow, functions that take a rate and grow it at regular intervals. You might use these functions for growing a production rate, sales, etc. A very common function that is a simplification of ConGrow is AnnGrow, which does a constant rate that grows on an annual basis, in other words holding a rate constant for a year and then changing it for the next year.

    Then there is RePhaseCon, which is really two Cons, which together take an amount from one place in the cash flow and put in somewhere else - in this way it rephases an amount. What"s noteworthy about this function is that it does not create or destroy cash flow - the total amount is shifted and the rate at the new location altered accordingly to keep the total amount of cash in balance.

    There are many ways of growing a rate. ConGrow, for example, uses a set of GrowthRates and GrowthDates. Another way is to refer the rate every so often to a background market rate. This is what the functions in the Constant Rate Market family do. And because of these two ways of specifying a market forecast, there are two main functions here, ConGrowMkt and ConFcstMkt. This gives a level of indirection to the determination of the rate, which can be determined with respect to a forecast. These functions are very suitable for Real Estate work, because you can set a starting rent and have the rent refer to a market forecast. The result can be multiplied by area to give the rent.

    One of the most common tasks is to take a rate and multiply it by a prevailing price to get Revenue. This is what the functions of the Constant Rate Revenue family do. All these functions actually do is take a function from the Constant Rate Market and multiply by an annual rate. However, they make revenue projection much easier because if you were to have price and rate as separate lines in your model and multiplying together, then, depending on how you have done it, there are no guarantees that if you run the model on a different timebase you will get consistent results. With the functions in the Revenue families you can have confidence that a different time base will be taken in the functions stride.

    Stepped Rate

    Moving on from a constant rate to a stepped rate, stepped rate projections come in two main forms, depending on in the input data. If you want to specify rates from certain dates, use the Stepped Rate From family. For specifying rates up to certain dates, use the Stepped Rate To family. The main function in the Stepped Rate To family is clearly TStep, which simply projects a stepped rate using a set of ToDates. Moving on from there, TStepGrow does a stepped rate where you don"t have to provide all the steps - the function will create them from GrowthRates. What you have to provide is an InitialAnnRate and of course the GrowthRates. A similar function is TStepFcst, where instead of GrowthRates a range of FcstVals is used. This function is quite suited to Real Estate work because future rents are generated of a forecast.

    TStepGrown is like a straight TStep except each rate is grown - so it is like forecasting rates in real terms and having GrowthRates inflate them.

    The Stepped Rate From family is similar to the Stepped Rate To family, so all that has been said previously applies here too. The only difference is that the steps are specified in terms of FromDates instead of ToDates.

    The Stepped Rate Market family deals with rates that refer at times to a market rate. It actually works a little differently to the Constant Rate Market family, where a constant rate was periodically reviewed to a market rate. What the Stepped Rate Market family does is look for gaps in the steps you provided (i.e. you provided a date but not a rate), and where a gap exists (i.e. a blank or zero rate), it will supply a rate read from it"s market forecast.

    Finally, the Stepped Rate Revenue family forecasts revenue based on multiplying a price by a rate. See what was said above for the Constant Rate Revenue family, because the same applies here. The thing to remember is that the rate part is just a TStep or FStep and that in, say FStepRevGrow the GrowthRates apply to the MktPrice not the rate. What the function is doing, by the way, is a series of ConRevGrows for each step. In this family there are 4 key functions, 2 for the using FromDates and ToDates to specify the rate changes and 2 for the difference between using GrowthRates/MktPrice and FcstVals to describe the market price forecast.

    The same kinds of idea in these projections also apply in other areas. Throughout the library wherever a market price forecast is required, you"ll see we use both the GrowthRates and FcstVals approach, to give rise to a ...Fcst... as well as a ...Grow... function. The specialist categories of Real Estate and Petroleum Economics also use these techniques.

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure