Advanced Excel for Finance

Finance Excel Classes NYC

Advanced Excel for Finance is a unique course covers Excel techniques commonly used by finance professionals, including investment banking, private equity, hedge funds, real estate, and asset management. You'll walk away with the Excel tricks and features you need in the field of finance.

  • Learn financial functions for NPV, IRR, Yields, Payments and more
  • Master Excel shortcuts to speed up workflow, including navigation, formula writing, and working without the mouse
  • Conquer advanced functions: Nested functions, VLOOKUP-MATCH, INDEX-MATCH
  • Get started with cash flow modeling and projections in Excel

Prerequisites: Excel proficiency equivalent to Intermediate Excel for Business is required. For Excel training, see our full offering of Excel courses.

Learn Advanced Excel & Financial Modeling: Take this course with our Financial Modeling Bootcamp or Financial Analyst Training Program to achieve the skills you need to become a top financial analyst.

Advanced Excel for Finance

$299 7 Hours 185 Madison Avenue, NYC In-person classroom training

Register for a class

Dec 17 Mon, 10am–5pm
Jan 29 Tue, 10am–5pm
Mar 4 Mon, 10am–5pm
Apr 15 Mon, 10am–5pm
May 29 Wed, 10am–5pm
Jun 24 Mon, 10am–5pm

Corporate training available

Call 212-658-1918 or email us

What You'll Learn

  • Financial functions: NPV, IRR, XNPV, and XIRR
  • Advanced database functions: VLOOKUP-MATCH, & INDEX-MATCH
  • Create Data Tables to show outputs based on various scenarios
  • Use Goal Seek's powerful calculation tools
  • Create financial projections based on historical data
  • Learn Hot Keys to work in Excel without the mouse
  • Master Excel with a variety of shortcuts finance professionals need
  • icon for small classesSmall classes
  • icon for small classesComputer provided
  • icon for small classesBook included
  • icon for small classesFree retake

Full Course Syllabus

Download PDF Outline

Financial Functions

Net Present Value

  • PV and NPV functions to value investments
  • XNPV functions to value investments based on investment dates

Internal Rate of Return

  • IRR function to calculate the return on an investment
  • XIRR function for returns based on investment dates

Mortgage Payments

  • PMT function to calculate the monthly payment on a mortgage

Bond Yield

  • YLD function to calculate the yield on a bond or other investment

Advanced Techniques to Expedite Workflow

Advanced Cell Locking

  • Create powerful formulas by locking either the column or the row

Hot Keys

  • Transform the ribbon into a visual listing of pre-assigned shortcuts

Windows

  • Techniques and keyboard shortcuts to allow editing of active windows

Cell Auditing

  • Observe the relationship between formulas and cells

Special Formatting

Date functions

  • Calculate dates with a variety of functions

Conditional Formatting-Formulas

  • Create custom rules for Conditional Formatting with formulas

Advanced Functions

Nested IF statements

  • Nested "IF" statements allow for more than just two possibilities in a single cell

IF statements with AND/OR

  • Expand the functionality of the IF function by adding an AND / OR criteria

Advanced Analytical Tools

Goal Seek

  • Find the desired result by adjusting an input value

Data Tables

  • Data Tables show the range of effects of one or two different variables on a formula

Conditional SumProduct

  • Use SumProduct with conditions to exclude data that does not meet certain criteria

Pivot Table-Calculations

  • Create calculated rows or columns in a Pivot Table that go beyond the source data

Pivot Charts

  • Create dynamic, graphical representations of Pivot Table data

Advanced Database Functions

MATCH function

  • Return the relative position (column or row number) of a lookup value

VLOOKUP-MATCH

  • Create a more accurate VLookUp by enhancing the determination of the Column Index number

INDEX-MATCH

  • Efficiently return a value or reference from a cell at the intersection of the row and column

INDEX-Double MATCH

  • Use a second Match function to create a powerful, two-way lookup tool

Introduction to Macros

Recording Macros

  • Record Macros that involve formatting and calculations

Relative Macros

  • Record relative reference macros for ad-hoc reporting

Project I: Cash Flow Modeling

Projecting Cash Flows

  • Use advanced Excel techniques to project cash flows
  • Set up model scenarios with dropdown menus and CHOOSE function
  • Create a data table to show a range of outputs
  • Use Goal Seek to back into a target value

Advanced Modeling Techniques

  • Auditing the model
  • Find & Replace
  • Go To Special

Project II: Loan Amortization Model

PMT Function

  • Use the Payment function to calculate the monthly payment on a mortgage

Loan Table

  • Use various Excel tricks to quickly populate the loan table

Advanced SUMIFs

  • Extract data from the loan table

Learn about our other Excel & Finance Courses

Excel Classes: Excel is perhaps the top skill required for finance professionals today. At NYIM, you can get started with the basics or learn comprehensively in the Excel for Business Bootcamp.

Financial Modeling Bootcamp: Learn Excel, finance, accounting, and corporate valuations skills in our Financial Modeling Bootcamp in New York City. The first day of the course is this Advanced Excel for Finance training. On day two, you will learn finance principles including enterprise value, comparable companies analysis, corporate valuation principles, and financial accounting concepts. On the last day, you will build a comprehensive valuation model on a publicly-traded restaurant company using real financial data. You will research the company to determine the model drivers, and derive a value per share using a DCF model.

Corporate Finance: In our corporate finance training, you will learn the core finance concepts every finance professional should know, including stocks, bonds, options, NPV, IRR, M&A, LBO transactions, DCF modeling, and more. 

Financial Accounting: In our Financial Accounting courses, you will learn to analyze financial statements, including the income statement, balance sheet, cash flow statement, and financial statement footnotes. We'll cover the basics of dual entry accounting and other accounting principles.