English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 2h 35m | 308 MB
Analyzing financial data can seem intimidating, but Microsoft Excel has a wide range of functions to perform these calculations quickly and easily. This course shows users how and when to use each of the financial functions available in Excel. Author Curt Frye covers evaluating loan payments; calculating depreciation; determining rates of return, bond coupon dates, and security durations; calculating prices and yields; and more.
Note: This course was recorded in Office 365, but anyone using a recent version of Excel—including 2019, 2016, and some earlier versions—will be able to follow along.
Topics include:
- Analyzing loans, payments, and interest
- Calculating depreciation
- Determining values and rates of return
- Calculating bond coupon dates and security durations
- Calculating security prices and yields
- Calculating security yields
- Evaluating the yields of securities with odd periods
Table of Contents
1 Perform financial analysis in Excel
2 What you should know
3 Disclaimer
4 PMT Calculate a loan payment
5 PPMT and IPMT Calculate the principal and interest per loan payment
6 CUMPRINC and CUMIPMT Calculate cumulative principal and interest paid between periods
7 ISPMT Calculate the interest paid during a specific period
8 EFFECT and NOMINAL Find nominal and effective interest rates
9 ACCRINT and ACCRINTM Calculate accrued interest for investments
10 RATE Discover the interest rate of an annuity
11 PDURATION Calculate the number of periods to reach a specified value
12 NPER Calculate the number of periods in an investment
13 SLN Calculate depreciation using the straight line method
14 DB Calculate depreciation using the declining balance method
15 DDB Calculate depreciation using the double-declining balance method
16 SYD Calculate depreciation for a specified period
17 VDB Calculate declining balance depreciation for a partial period
18 AMORDEGRC Calculate depreciation using a depreciation coefficient
19 AMORLINC Calculate depreciation for each accounting period
20 FV Calculate the future value of an investment
21 FVSCHEDULE Calculate the future value of an investment with variable returns
22 PV Calculate the present value of an investment
23 NPV Calculate the net present value of an investment
24 XNPV Calculate net present value given irregular inputs
25 IRR Calculate internal rate of return
26 XIRR Calculate internal rate of return for irregular cash flows
27 MIRR Calculate internal rate of return for mixed cash flows
28 RRI Calculate the interest rate for the growth of an investment
29 COUPDAYBS Calculate total days between coupon beginning and settlement
30 COUPDAYS Calculate days in the settlement date’s coupon period
31 COUPDAYSNC Calculate days from the settlement date to the next coupon date
32 COUPNCD Calculate the next coupon date after the settlement date
33 COUPNUM Calculate the number of coupons between settlement and maturity
34 COUPPCD Calculate the coupon date due immediately before settlement
35 DURATION Calculate the annual duration of a security
36 MDURATION Calculate the duration of a security using the Macaulay method
37 DOLLARDE and DOLLARFR Convert between fractional prices and decimal prices
38 INTRATE Calculate the interest rate of a fully invested security
39 RECEIVED Calculate the value of a fully invested security at maturity
40 PRICE Calculate the price of a security that pays periodic interest
41 DISC Calculate the discount rate of a security
42 PRICEDISC Calculate the price of a discounted security
43 PRICEMAT Calculate the price of a security that pays interest at maturity
44 TBILLEQ Calculate the bond-equivalent yield for a Treasury bill
45 TBILLPRICE Calculate the price for a Treasury bill
46 TBILLYIELD Calculate the yield of a Treasury bill
47 YIELD Calculate the yield of a security that pays periodic interest
48 YIELDDISC Calculate the annual yield for a discounted security
49 YIELDMAT Calculate the annual yield of a security that pays interest at maturity
50 ODDFPRICE Calculate the price of a security with an odd first period
51 ODDFYIELD Calculate the yield of a security with an odd first period
52 ODDLPRICE Calculate the price of a security with an odd last period
53 ODDLYIELD Calculate the yield of a security with an odd last period
54 Next steps
Resolve the captcha to access the links!