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
    Using PmtsPerYear
    Pro"s and Con"s of this short-cut approach to payment frequency
    PmtsPerYear (Payments Per Year) describes how payments are made when calculating Net Present Values.

    There is an obvious similarity with DayCount, Periods and CashBasis, which perform the same role in projections functions and some NPV functions in determining when cash moves.

    The difference is that PmtsPerYear is much narrower in its remit than the daycount variables as explained below.

    PmtsPerYear is used in the DCF Annual, Basic Interest Rate Conversions and Annuity families.

    • Whereas Daycount can be specified with uneven length periods, and even take into account business days, PmtsPerYear is simply a figure specifying how many payments are made in a year assuming equal intervals of payment, that can be in advance or in arrear. This is in practise, accurate enough for a lot of analytical work.
    • PmtsPerYear is used where the internal mathematics make use of annuity adjustment formulae such as CorrectionM.
    • Computationally, using PmtsPerYear is much more direct than a full DayCount calculation, because the annuity formulae can use it directly. NPV functions that use daycount have to determine payment dates more laboriously, and typically have to iterate through a cash flow array to determine NPV. In other words, they do it the long way whereas PmtsPerYear facilitates using off-the-shelf annuity formulae.

    It can be realised that even after the preceding explanation one can be very unclear over how PmtsPerYear and the DayCount variables are different. If you know the annuity formulae, consider this example:
    • The NPV at 10% AER of a 10 year annuity of $10 p.a. payable annually in arrears is $61.45. You can do this in BF with PVM(10,10%,10,-1) with PmtsPerYear or even with Excel"s PV function (PV(10%,10,10)).
    • If the payment changes to quarterly in arrear, the NPV at 10% AER is higher, at $63.70, because it is more onerous to pay quarterly rather than annually. Using a PmtsPerYear of -4 accomplished this (PVM(10,10%,10,-4)). You can achieve the same with Excel"s PV function if you make the payment 2.5, the number of periods 40 and the discount rate equal to (and keeping the AER the same) 1.10^0.025-1.0 = 2.41% per quarter (PV((1+10%)^0.025-1,40,2.5)).
    • If the payment were quarterly in arrear on a calendar basis i.e. (1 April, 1 July etc) the PVM or PV functions are, on reflection, just approximations, because we don"t know that calendar quarter days are equally spaced throughout the year. In fact they are not.
    • If the problem is compounded by saying that the annuity starts midway through such a period i.e. you get your first payment after 6 weeks or so, clearly the annuity formulae in PVM and PV are out of their depth. The assumption that has been broken is that the periods have become unequal and irregular.
    • The answer to the last two parts of the problem, if accuracy is required, will not be found with PV or PVM. Instead you will either have to model the entire cash flow and use BF"s PVT, Excels XNPV functions, BF"s PVBF (2004, 40,(1.1^0.025-1)*4, 2.5, 0,3, {-1.01,-4.01,-7.01,-10.01}) or BF"s PVCon (PVCon(2004,0.1,2004,2014,10,6,{-1.01,-4.01,-7.01,-10.01},3,{-1.01,-4.01,-7.01,-10.01}).

    Which should you use, functions that use DayCount or PmtsPerYear?
    • Sometimes, you don"t have a choice, and often PmtsPerYear is just easier to work with.
    • For project appraisal, using PmtsPerYear is more than sufficient for establishing NPV"s etc. By taking account payment frequency at all you are already streets ahead of the plain old NPV function.
    • For financial engineering, the daycount based functions are perhaps better because you will probably want 100% internal consistency between the timing of payments and the present value of principal amounts, and will perhaps want business day accuracy too. Daycount functions are slower because NPV is determined on a cash flow by cash flow basis, so you may not want sheets and sheets of them, but for NPV functions this is rarely the case.
    • PmtsPerYear can have a value from -999 to +999, but typically might be 4 (for quarterly in advance) or -4 (for quarterly in arrear). A value of 999 (or any large number) would just imply continuous or daily payments.

Business Functions Ltd, London, UK Website Design: Webpure