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
    Calculating Interest
    Ins and Outs of interest calculation in Business Functions
    This discussion overlaps with Using Daycount and Examples of DayCount, but because it deals with a specific type of cash flow, namely interest, you may find it more relevant and accessible.

    Interest arises in Business Functions within the Banking category where there are functions that use the variables DayCount and PrdsInt.

    The Interest functions use functions in the rest of the library internally so everything said about DayCount and Periods applies here too. In fact the only difference is that the Interest function variable PrdsInt has a default of quarterly in arrear instead of quarterly in advance.

    The most basic function, Interest, is no more than a Con in disguise. Con calculates a constant annual rate. The two jobs it does are:
    • It calculates what fraction of a year lies between of Start and Finish and lies inside the time period defined by Time and Base
    • It Applies a simple interest rate to the balance, call that the annual rate of interest accrual, and multiply that by the fraction of the year.
    This is pretty soundly based and unlikely to give unexpected answers providing you have used DayCount and PrdsInt correctly. For DayCount options 1 to 5, 8 and 9, standard methods of daycount are used. DayCount option 6 allows for the use of PrdsInt and the ACT/ACT (in period) method (see Using Daycount for details).

    For functions such as Interest running in cash mode, the usual library methodology applies. Payments can be determined as advance or arrears using PrdsInt (see Using Daycount for details).

    Interest is calculated "simple" in the above functions. This is departure from some other areas of the library, where discount and interest rates are quoted on a consistent, AER (annual equivalent rate) basis. However, in the Interest functions, it is more practical to use simple interest (Interest Accrued = Fraction of Year x Interest Rate), because the fraction of the year will depend very greatly on the DayCount/Periods combination used.

    Calculating interest involves more work where the interest rate and/or balance changes during a time period, as so often happens. Functions like FInterest accept an interest rate profile, as specified by FromDatesInt and IntSimple, as well as a profile of balances, as specified in FromDates and Balances. What these functions do is look at each balance in turn, establish the start date and end date of that balance and then apply an interest rate. The interest rate comes from a time-average of the interest rates over the period of that balance. The combination of the average interest rate and the balance, taking into account as well the length of the particular time period, gives you the interest paid FOR THAT BALANCE. Looping through each balance accumulates the interest for each balance.

    The result is an interest amount that uses an average balance and an average interest rate. The method of determining the averages is important, as this gives rise to the different methods of calculation banks and companies have. Functions like FInterest use the DayCount/PrdsInt combination to determine the fractions of year between balance changes and interest changes, as well as for the length of time period itself. The following examples describe typical interest "regimes":


    DayCountPrdsInt
    (Accruals)    		PrdsInt
    (Cash)
    Quarterly in arrear, calendar quarters, ACT/365Omitted or 3Omitted or -4 or 4
    or {1.01,4.01,7.01,10.01}
    or {-1.01,-4.01,-7.01,-10.01}    		 Omitted or -4
    or {-1.01,-7.01,-10.01,-11.01}
    Quarterly in arrear, particular dates, like bonds6.03{1.23,4.23,7.23,10.23}
    or {-1.23,-4.23,-7.23,-10.23}    		{-1.23,-4.23,-7.23,-10.23}
    Monthly in arrear, calendar months, 30/3600-12 or 1212


    Note that the accrual period for in advance and arrears is the same and only the sign is different. However in advance the payment is made at the start of the period on the first day, and in arrears its made on the day AFTER the end of the period.

    Finish Dates
    In a BF interest function, Interest runs until the day before the final finish date, on the grounds that interest is not paid on the day that a loan is repaid. So if the Start date was 1 Jan 2005 and the Finish date was 1 Jul 2005, interest would be calculated from 1 Jan 2005 to 30 Jun 2005 inclusive. This is also consistent with BF"s usual rules about Inclusive and Exclusive Dates. However, there is a way you can specify that interest is paid on the finish date, using ProjMode - for details see ProjMode and Inclusive Dates.

Business Functions Ltd, London, UK Website Design: Webpure