Excel 2010: Financial Functions

Shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010.

In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.

Introduction

• Welcome
• Using the exercise files
• Disclaimer

1. Analyzing Loans, Payments, and Interest

• PMT: Calculating a loan payment
• PPMT and IPMT: Calculating principal and interest per loan payment
• CUMPRINC and CUMIPMT: Calculating cumulative principal and interest paid between periods
• ISPMT: Calculating 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
• 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
• IRR: Calculating internal rate of return
• XNPV: Calculating net present value given irregular inputs
• XIRR: Calculating internal rate of return for irregular cash flows
• MIRR: Calculating internal rate of return for mixed cash flows
• DISC: Calculating the discount rate of a security

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 a coupon due immediately before settlement
• DURATION: Calculating the annual duration of a security
• MDURATION: Calculating the duration of a security using the modified Macauley 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