Excel 2016: Financial Functions in Depth

Learn to use Excel functions for financial analysis. Find out how to calculate loan payments, depreciation, rate of return, and more, in Microsoft Excel.

Introduction

• Perform financial analysis in Excel
• What you should know
• Disclaimer

1. Analyzing Loans, Payments, and Interest

• PMT: Calculate a loan payment
• PPMT and IPMT: Calculate the principal and interest payments
• Splitting interest and principal payments
• CUMPRINC and CUMIPMT: Cumulative principal and interest
• EFFECT and NOMINAL: Nominal and effective interest rates
• ACCRINT and ACCRINTM: Calculate accrued investments interest
• RATE: Discover the interest rate of an annuity
• PDURATION: Calculate the periods to reach a goal
• NPER: Calculate the number of periods in an investment

2. Calculate Depreciation

• SLN: Depreciation using the straight line method
• DB: Depreciation using the declining balance method
• DDB: Depreciation using the double-declining balance method
• SYD: Calculate depreciation for a specified period
• VDB: Declining balance depreciation for a partial period
• Comparing depreciation functions

3. Determining Values and Rates of Return

• FV: Future value of an investment
• FVSCHEDULE: Future value with variable returns
• Escalating with compounding interest or growth rates
• PV: Present value of an investment
• NPV: Net present value of an investment
• XNPV: Net present value given irregular inputs
• IRR: Internal rate of return
• XIRR: Internal rate of return for irregular cash flows
• MIRR: Internal rate of return for mixed cash flows
• RRI: The interest rate for the growth of an investment

4. Functions and Data Types in Excel for Microsoft 365

• STOCKHISTORY: Get historical stock prices
• Live stock prices with Stocks data types
• Calculate exchange rates with Currencies data types
• FIELDVALUE: Get field data
• STOCKHISTORY with data types
• Charting STOCKHISTORY with data types

5. Combining Functions Perform Financial Analysis

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

Conclusion

• Where to learn more about Excel