Principles of Financial Modelling

CHAPTER 20Financial Functions

INTRODUCTION

This chapter focuses on providing examples of a wide set of functions within Excel's Financial category, relating to:

• Annuity and general growth or discounting calculations.
• Mortgage and capital repayment calculations.
• General investment valuation and analysis.
• Depreciation calculations.
• Yields, bond, general securities and specific financial calculations.

PRACTICAL APPLICATIONS

Several points are worth bearing in mind:

• Many of the functions return negative figures by default, especially those that relate to calculating required payments or cash outflows. One needs to be particularly careful in their use, especially when they are embedded in logical statements such as those involving IF, MIN or MAX formulae.
• The use of an IF or IFERROR statement around such functions is frequently required. For example, some functions will return error values for a time period that is outside a valid limit (such as the lifetime of a mortgage). As covered in Chapter 17, it is generally better to exclude specific conditions in which error values are known to occur but are valid (by using an IF statement applied to this specific situation), rather than over-riding all errors by using IFERROR.
• Many of the functions are formatted in the local currency (i.e. that of the Excel version being used), which is sometimes inconvenient, especially when working on international projects; one may find it more convenient to switch to a general format.
• Where the ...