Excel 2016: Financial Functions in Depth

Introduction

• Welcome
• Disclaimer
• Using the exercise files

1. Analyzing Loans, Payments, and Interest

• PMT: Calculating a loan payment
• PPMT and IPMT: Calculating the principal and interest per loan payment
• CUMPRINC and CUMIPMT: Calculating cumulative principal and interest paid between periods
• ISPMT: Calculating the interest paid during a specific period
• EFFECT and NOMINAL: Finding nominal and effective interest rates
• ACCRINT and ACCRINTM: Calculating accrued interest for investments
• RATE: Discovering the interest rate of an annuity
• PDURATION: Calculating the number of periods to reach a specified value
• NPER: Calculating the number of periods in an investment

2. Calculating Depreciation

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

3. Determining Values and Rates of Return

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

4. Calculating Bond Coupon Dates and Security Durations

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

5. Calculating Security Prices and Yields

• DOLLARDE and DOLLARFR: Converting between fractional prices and decimal prices
• INTRATE: Calculating the interest rate of a fully invested security
• RECEIVED: Calculating the value at maturity of a fully invested security
• PRICE: Calculating the price of a security that pays periodic interest
• PRICEDISC: Calculating the price of a discounted security
• PRICEMAT: Calculating the price of a security that pays interest at maturity
• TBILLEQ: Calculating the bond-equivalent yield for a Treasury bill
• TBILLPRICE: Calculating the price for a Treasury bill
• TBILLYIELD: Calculating the yield of a Treasury bill
• YIELD: Calculating the yield of a security that pays periodic interest
• YIELDDISC: Calculating the annual yield for a discounted security
• YIELDMAT: Calculating the annual yield of a security that pays interest at maturity

6. Calculating Prices and Yields of Securities with Odd Periods

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

Conclusion

• Additional resources