Excel 2013: Financial Functions in Depth

Master Excel's financial functions. Find out how to calculate loan payments, depreciation, rate of return, and more, in Excel 2013.

Analyzing financial data can seem intimidating, but Excel 2013 has a wide range of functions that you can use to perform these analyses quickly and easily. This course shows users how and when to use each of the functions available to them in Excel 2013. Learn how to evaluate cash flows; calculate depreciation; determine rates of return, bond coupon dates, and security durations; and more.

Introduction

• Welcome
• What you should know before watching this course
• Using the exercise files

1. Analyzing Loans, Payments, and Interest

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

2. Calculating Depreciation

• Calculating depreciation using the straight-line method (SLN)
• Calculating depreciation using the declining balance method (DB)
• Calculating depreciation using the double-declining balance method (DDB)
• Calculating depreciation using sum of years method (SYD)
• Calculating declining balance depreciation for a partial period (VDB)
• Calculating depreciation using a depreciation coefficient (AMORDEGRC)
• Calculating depreciation for each accounting period (AMORLINC)

3. Determining Values and Rates of Return

• Calculating the future value of an investment (FV)
• Calculating the future value of an investment with variable returns (FVSCHEDULE)
• Calculating the present value of an investment (PV)
• Calculating the net present value of an investment (NPV)
• Calculating net present value given irregular inputs (XNPV)
• Calculating internal rate of return (IRR)
• Calculating internal rate of return for irregular cash flows (XIRR)
• Calculating internal rate of return for mixed cash flows (MIRR)
• Calculating the discount rate of a security (DISC)

4. Calculating Bond Coupon Dates and Security Durations

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

5. Calculating Security Prices and Yields

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

6. Analyzing Simulation Results

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

Conclusion

• Next steps