Principles of Financial Modelling

CHAPTER 19Mathematical Functions

INTRODUCTION

This chapter covers a wide set of Excel functions that relate to arithmetic and mathematical calculations. We focus on those that are in Excel's Math&Trig category, which are generally the most frequently used in practical financial modelling applications. (We do not repeat the discussion for those functions covered in Chapter 17.) We also mention a few other functions, to give a hint of the types of possibilities that exist more generally; readers may choose to review the full set of functions within this category to identify whether others may be useful to them in their own contexts.

The examples provided include uses of:

• LN, EXP.
• ABS, SIGN.
• INT, ROUND, ROUNDUP, ROUNDDOWN, TRUNC.
• MROUND, CEILING.MATH, FLOOR.MATH.
• MOD.
• SQRT, POWER.
• FACT, COMBIN.
• SINE, ASIN, COS, ACOS, TAN, ATAN.
• DEGREES, PI, SQRTPI.
• BASE, DECIMAL.
• ARABIC, ROMAN.

PRACTICAL APPLICATIONS

Example: EXP and LN

The EXP function calculates the natural exponent of any number, i.e. for any input x it calculates y, where:

The LN function is the inverse to this, i.e. it calculates the natural logarithm of a number, y, which is the x-value that would solve the above equation for x when y is the input value, so that x=LN(y).

Note that the logarithm of the product of two numbers is the sum of their individual logarithms. That is:

This can be seen easily, for if

and