Excel: 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 per loan payment
• CUMPRINC and CUMIPMT: Calculate cumulative principal and interest paid between periods
• ISPMT: Calculate the interest paid during a specific period
• EFFECT and NOMINAL: Find nominal and effective interest rates
• ACCRINT and ACCRINTM: Calculate accrued interest for investments
• RATE: Discover the interest rate of an annuity
• PDURATION: Calculate the number of periods to reach a specified value
• NPER: Calculate the number of periods in an investment

2. Calculate Depreciation

• SLN: Calculate depreciation using the straight line method
• DB: Calculate depreciation using the declining balance method
• DDB: Calculate depreciation using the double-declining balance method
• SYD: Calculate depreciation for a specified period
• VDB: Calculate declining balance depreciation for a partial period
• AMORDEGRC: Calculate depreciation using a depreciation coefficient
• AMORLINC: Calculate depreciation for each accounting period

3. Determining Values and Rates of Return

• FV: Calculate the future value of an investment
• FVSCHEDULE: Calculate the future value of an investment with variable returns
• PV: Calculate the present value of an investment
• NPV: Calculate the net present value of an investment
• XNPV: Calculate net present value given irregular inputs
• IRR: Calculate internal rate of return
• XIRR: Calculate internal rate of return for irregular cash flows
• MIRR: Calculate internal rate of return for mixed cash flows
• RRI: Calculate the interest rate for the growth of an investment

4. Calculate Bond Coupon Dates and Security Durations

• COUPDAYBS: Calculate total days between coupon beginning and settlement
• COUPDAYS: Calculate days in the settlement date’s coupon period
• COUPDAYSNC: Calculate days from the settlement date to the next coupon date
• COUPNCD: Calculate the next coupon date after the settlement date
• COUPNUM: Calculate the number of coupons between settlement and maturity
• COUPPCD: Calculate the coupon date due immediately before settlement
• DURATION: Calculate the annual duration of a security
• MDURATION: Calculate the duration of a security using the Macaulay method

5. Calculate Security Prices and Rates

• DOLLARDE and DOLLARFR: Convert between fractional prices and decimal prices
• INTRATE: Calculate the interest rate of a fully invested security
• RECEIVED: Calculate the value of a fully invested security at maturity
• PRICE: Calculate the price of a security that pays periodic interest
• DISC: Calculate the discount rate of a security
• PRICEDISC: Calculate the price of a discounted security
• PRICEMAT: Calculate the price of a security that pays interest at maturity
• TBILLEQ: Calculate the bond-equivalent yield for a Treasury bill
• TBILLPRICE: Calculate the price for a Treasury bill

6. Calculate Security Yields

• TBILLYIELD: Calculate the yield of a Treasury bill
• YIELD: Calculate the yield of a security that pays periodic interest
• YIELDDISC: Calculate the annual yield for a discounted security
• YIELDMAT: Calculate the annual yield of a security that pays interest at maturity

7. Evaluate Securities with Odd First or Last Periods

• ODDFPRICE: Calculate the price of a security with an odd first period
• ODDFYIELD: Calculate the yield of a security with an odd first period
• ODDLPRICE: Calculate the price of a security with an odd last period
• ODDLYIELD: Calculate the yield of a security with an odd last period

Conclusion

• Next steps