Financial Calculations Using EXCEL™

Overview of computing resources in EXCEL™

• Standard and specialised libraries, loading libraries
• Types and use of functions
• Improvements in EXCEL ™ 2007
• Shortcuts and tricks

EXCEL™ tools and assistants

• Matrix algebra and related functions
• Solver, goalseek
• Conditional formats
• Simple and double entry tables

• Practical workshop

• Construction of a double entry table

Actuarial calculation

• Financial functions: NPV, forward value, …
• Internal Rate of Return (IRR)
• Fixed-income functions: yield to maturity, duration, modified duration
• Linear interpolation with the function « trend »
• Zero-coupon method

• Practical workshop

• Construction of a zero-coupon curve and computation of forward rates

Management of dates and holiday calendars

• Functions for calculating dates and holidays
• Calculation of holidays, Easter dates
• Integration of the main IR calculation conventions (BB, MM, Ex/Ex)

• Practical workshop

• Create a calendar which includes bank holidays, TARGET and IMM dates

Buiding cashflow schedules

• Constant maturity loans
• Amortising, balance of capital
• Complex cashflow schedules: franchises, rounding, ...

• Practical workshop

• Construction of a loan reimbursement cashflow shedule

Swaps pricing

• Construction of the fixed and floating legs
• Fixing and payment dates
• Interpolation of Interest Rates
• Calculation of forward rates
• Using goalseek to price a swap

• Practical workshop

• Build a swap pricer

Statistics

• Data loading from the web and calculations on series of returns
• Variance / Covariance (VCV) matrix

• Practical workshop

• Calculate the correlation matrix using 3 different methods.

Data analysis

• Notions of lists, tables, index and order
• Sorting functions, extremes and rank
• Data extraction from logical criteria
• Functions: sumprod, equiv, vlookup
• Counting functions and conditional sums
• Identification and extraction of duplicates

• Practical workshop

• Work on a table and reconciliate 2 tables