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
    Annual Equivalent Rates
    How BF uses what are also known as Annual Effective Rates
    Throughout the library, when quoting interest rates or discount rates, we use the annualised rate or AER (Annual Equivalent Rate). This is also often known as the Annual Effective Rate (AER) or Effective Annual Rate (EAR). They are also the same as what previously used to be known as APR"s (Annual Percentage Rates) in the UK, but note that this is not the same as APR"s in the USA and elsewhere, which are not annualised but actually adjusted simple interest rates.

    A very good detailed explanation is provided by the British Bankers Association.

    Our definition is that the AER is a notional interest rate, compounded annually in arrears that is equivalent to the specified simple interest rate at a specified compounding frequency. "Equivalent" means that if you invested an amount of money at the notional AER compounded annually for a period of time the final capital amount would be identical to that compounded at the simple interest rate at the specified compounding frequency, assuming that in both cases the interest were re-invested.

    The AER does not specifically include fees, costs etc.
    It specifically only applies to compounding annually in arrear.

    If you have an simple interest rate compounded quarterly, say 10% per annum, this means 2.5% per quarter. This is not 10% AER, it is actually more like 10.4% AER (or (1+0.025)^4-1).

    If you see an AER in Business Functions, it is intended as an annual rate, compounded once, annually, in arrears. If your rate is not an AER, convert it first to an AER using SimpleToAER.

    Note on Nomenclature

    This is a confusing area, where there are differences between countries. In the UK the definition of the APR as an annualised rate started with the Consumer Credit Act in 1974 and form 2000, when the Financial Services and Markets Act was passed, the APR was dropped as benchmark for financial products and replaced by the similar AER, more information about which can be found at the Financial Services Authority, Bank of England and the British Bankers Association.

    In the USA the APR, as defined in the "Truth In Lending" legislation (Regulation Z), and by the Federal Reserve Board, means something different. It is the simple interest rate adjusted for any fees, costs of the financial product. So you always need to know the payment frequency as well as the APR - for theu ses the APR is put to, it is usually has monthly payments. The method for adjusting is to add the fees to the loan amount, and recompute the interest rate required to amortise the loan. In other words, it is the simple interest rate that is exactly equivalent in time value of money (NPV) terms to the loan when fees are included.

    Now that the UK has changed its confusing APR term to the AER for savings products at least (borrowing products still use the APR),and this brings all nations into agreement over the term AER used for annualised rate adjusted for compounding frequency, although in the UK it is called the Annual Equivalent Rate and elsewhere it is called the Annual Effective Rate, or Effective Annual Rate (EAR).

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