Excel: Financial Functions in Depth (2022)

Course details

Analyzing financial data can seem intimidating, but Microsoft Excel has a wide range of functions to perform these calculations quickly and easily. In this course, Microsoft MVP Danielle Stein Fairhurst shows you how and when to use each of the financial functions available in Excel. Danielle covers evaluating loans, payments, and interest; calculating depreciation; determining rates of return; calculating prices and yields as well as applying some of the new features of Excel for Microsoft 365 to financial data. Plus, she guides you through combining these functions to perform financial analysis.

Concepts

0. Introduction

01 - Perform financial analysis in Excel
02 - What you should know
03 - Disclaimer

1. Analyzing Loans, Payments, and Interest

04 - PMT - Calculate a loan payment
05 - PPMT and IPMT - Calculate the principal and interest payments
06 - Splitting interest and principal payments
07 - CUMPRINC and CUMIPMT - Cumulative principal and interest
08 - EFFECT and NOMINAL - Nominal and effective interest rates
09 - ACCRINT and ACCRINTM - Calculate accrued investments interest
10 - RATE - Discover the interest rate of an annuity
11 - PDURATION - Calculate the periods to reach a goal
12 - NPER - Calculate the number of periods in an investment

2. Calculate Depreciation

13 - SLN - Depreciation using the straight line method
14 - DB - Depreciation using the declining balance method
15 - DDB - Depreciation using the double-declining balance method
16 - SYD - Calculate depreciation for a specified period
17 - VDB - Declining balance depreciation for a partial period
18 - Comparing depreciation functions

3. Determining Values and Rates of Return

19 - FV - Future value of an investment
20 - FVSCHEDULE - Future value with variable returns
21 - Escalating with compounding interest or growth rates
22 - PV - Present value of an investment
23 - NPV - Net present value of an investment
24 - XNPV - Net present value given irregular inputs
25 - IRR - Internal rate of return
26 - XIRR - Internal rate of return for irregular cash flows
27 - MIRR - Internal rate of return for mixed cash flows
28 - RRI - The interest rate for the growth of an investment

4. Functions and Data Types in Excel for Microsoft 365

29 - STOCKHISTORY - Get historical stock prices
30 - Live stock prices with Stocks data types
31 - Calculate exchange rates with Currencies data types
32 - FIELDVALUE - Get field data
33 - STOCKHISTORY with data types
34 - Charting STOCKHISTORY with data types

5. Combining Functions Perform Financial Analysis

35 - EOMONTH, EDATE, and timing flags
36 - Calculate pro data rental costs with date functions
37 - IF - Building logical comparisons
38 - Calculating the payback period
39 - Using RATE or RRI for compound annual growth rate (CAGR)
40 - Creating a debt schedule
41 - Using SLN and IF to calculate depreciation
42 - Creating a depreciation schedule
43 - Using dynamic arrays to create a depreciation waterfall
44 - Calculating weighted average cost of capital (WACC)
45 - Using NPV to calculate a discounted cash flow (DCF)

Conclusion

46 - Where to learn more about Excel

Excel: Financial Functions in Depth (2022)

Authors

Danielle Stein Fairhurst