Excel: Financial Functions in Depth
2h 30mIntermediate2025-04-24
Authors
Danielle Stein Fairhurst
Microsoft MVP | Financial Modeller | Author | Corporate Trainer
Course details
Master the art of financial analysis using Excel through this comprehensive course. Join instructor Danielle Stein Fairhurst to explore key concepts like calculating loan payments, principal and interest splits, and depreciation using various methods. Advanced modules cover forecasting investment values, determining rates of return, and building financial models using dynamic arrays and new Excel tools such as LAMBDA functions and STOCKHISTORY. Learn to create schedules, calculate payback periods, and apply techniques like discounted cash flow (DCF) analysis. Check out this course to gain the confidence to apply Excel's powerful functions to real-world financial challenges.
Learning objectives
Define the PMT function in Excel and explain its use in calculating loan repayments.
Demonstrate the process of splitting loan repayments into principal and interest components using IPMT and PPMT functions.
Create a chart in Excel to visually represent how loan interest and principal change over time.
Use the dynamic array functions in Excel to enhance financial calculations and model building.
Apply relevant Excel functions to convert between nominal and effective interest rates.
Illustrate the use of financial functions to calculate depreciation using various methods like straight-line and declining balance.
Use the NPV and XNPV functions to calculate the net present value of a series of cash flows.
Calculate compound annual growth rate using different Excel functions.
Develop a debt schedule using dynamic arrays to automate opening and closing balance calculations.
Compile a financial forecast using the array functions in Excel to dynamically calculate financial metrics.
Learning objectives
Define the PMT function in Excel and explain its use in calculating loan repayments.
Demonstrate the process of splitting loan repayments into principal and interest components using IPMT and PPMT functions.
Create a chart in Excel to visually represent how loan interest and principal change over time.
Use the dynamic array functions in Excel to enhance financial calculations and model building.
Apply relevant Excel functions to convert between nominal and effective interest rates.
Illustrate the use of financial functions to calculate depreciation using various methods like straight-line and declining balance.
Use the NPV and XNPV functions to calculate the net present value of a series of cash flows.
Calculate compound annual growth rate using different Excel functions.
Develop a debt schedule using dynamic arrays to automate opening and closing balance calculations.
Compile a financial forecast using the array functions in Excel to dynamically calculate financial metrics.
Skills covered
Corporate FinanceSpreadsheetsMicrosoft ExcelFinance and AccountingData AnalysisData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftDeep Dive (X:Y)
Concepts
0. Introduction
- 01 - Perform financial analysis in Excel
- 02 - What you should know
- 03 - Disclaimer
1. Analyzing Loans, Payments, and Interest in Excel
- 04 - PMT - Calculate a loan payment
- 05 - PPMT and IPMT - Calculate the principal and interest payments
- 06 - Splitting interest and principal payments
- 07 - Splitting interest and principal payments with dynamic arrays
- 08 - CUMPRINC and CUMIPMT - Cumulative principal and interest
- 09 - EFFECT and NOMINAL - Nominal and effective interest rates
- 10 - RATE - Discover the interest rate of an annuity
- 11 - NPER - Calculate the number of periods in an investment
2. Calculating Depreciation with Excel Functions
- 12 - SLN - Depreciation using the straight-line method
- 13 - DB - Calculate depreciation using the declining balance method
- 14 - DDB - Depreciation using the double-declining balance method
- 15 - SYD - Calculate depreciation for a specified period
- 16 - VDB - Declining balance depreciation for a partial period
- 17 - Comparing depreciation functions
3. Determining Values and Rates of Return in Excel
- 18 - FV - Future value of an investment
- 19 - FVSCHEDULE - Future value with variable returns
- 20 - Escalating with compounding interest or growth rates
- 21 - PV - Present value of an investment
- 22 - NPV - Net present value of an investment
- 23 - XNPV - Net present value given irregular inputs
- 24 - IRR - Internal rate of return
- 25 - XIRR - Internal rate of return for irregular cash flows
- 26 - MIRR - Internal rate of return for mixed cash flows
- 27 - RRI - The interest rate for the growth of an investment
4. New Tools in Excel for MS365
- 28 - STOCKHISTORY - Get historical stock prices
- 29 - Live stock prices with Stocks data types
- 30 - Calculate exchange rates with the Currency data type
- 31 - FIELDVALUE - Get field data
- 32 - STOCKHISTORY with data types
- 33 - Charting STOCKHISTORY with data types
- 34 - Introduction to LAMBDA functions with BYCOL and BYROW
- 35 - Using BYCOL with multiple ranges
- 36 - Applying formatting to dynamic ranges
- 37 - Building a cumulative sum with SCAN
5. Combining Excel Functions Perform Financial Analysis
- 38 - TODAY, EOMONTH, EDATE, and timing flags
- 39 - Calculate pro-rata rental costs with date functions
- 40 - IF - Building logical comparisons
- 41 - Calculating the payback period
- 42 - Using RATE or RRI for compound annual growth rate (CAGR)
- 43 - Creating a debt schedule
- 44 - Using SLN and IF to calculate depreciation
- 45 - Creating a depreciation schedule
- 46 - Using dynamic arrays to create a depreciation waterfall
- 47 - Calcuating weighted average cost of capital (WACC)
- 48 - Using NPV to calculate a discounted cash flow (DCF)
Conclusion
- 49 - Great resources for learning Excel