Lesson 10: Model cash flows and inspect results
For loan functions, keep rate and period units consistent: monthly payments require an annual rate divided by 12 and years multiplied by 12. Excel often returns cash paid as a negative value and cash received as positive; use a consistent sign convention.
For a 250,000 loan at 6% annual interest for 20 years, monthly payment is =-PMT(6%/12,20*12,250000), approximately 1,791.08.
Financial, depreciation, and inspection formulas
| Function | Purpose | Example |
|---|---|---|
| PMT | Periodic payment for a constant-rate loan. | =-PMT(6%/12,20*12,250000). |
| PV | Present value of future payments or receipts. | =PV(6%/12,60,-500). |
| FV | Future value of an investment with constant rate/payments. | =FV(5%/12,120,-200). |
| NPER | Number of periods required for a payment plan. | =NPER(6%/12,-500,20000). |
| RATE | Interest rate per period, solved iteratively. | =RATE(60,-500,20000)*12 annualizes a monthly result. |
| IPMT | Interest part of one loan payment period. | =IPMT(6%/12,1,240,250000). |
| PPMT | Principal part of one loan payment period. | =PPMT(6%/12,1,240,250000). |
| CUMIPMT | Cumulative interest across a period range. | =CUMIPMT(6%/12,240,250000,1,12,0). |
| NPV | Net present value for equally spaced future cash flows. | =NPV(10%,B2:B6)+B1; add initial cash flow separately. |
| IRR | Internal rate of return for periodic cash flows. | =IRR(B1:B6) needs at least one positive and one negative value. |
| XNPV | Net present value for irregularly dated cash flows. | =XNPV(10%,B2:B6,A2:A6). |
| XIRR | Internal rate of return for irregular dates. | =XIRR(B2:B6,A2:A6). |
| SLN | Straight-line depreciation per period. | =SLN(10000,1000,5) returns 1,800 per year. |
| DB / DDB | Fixed-declining or double-declining depreciation. | =DDB(10000,1000,5,1). |
| SYD | Sum-of-years-digits depreciation for a period. | =SYD(10000,1000,5,1). |
| EFFECT / NOMINAL | Converts nominal and effective annual rates. | =EFFECT(12%,12). |
| ISFORMULA | Tests whether a cell contains a formula. | =ISFORMULA(B2). |
| FORMULATEXT | Displays a formula as text for an audit sheet. | =FORMULATEXT(B2). |
| ISREF / ISOMITTED | Tests for a reference or an omitted LAMBDA argument. | =ISREF(A1); ISOMITTED is for LAMBDA parameters. |
| TYPE | Returns a numeric code for a value type. | =TYPE(A1). |
| CELL | Returns information about a reference. | =CELL("address",A1). |
| INFO | Returns information about the current environment. | =INFO("directory"); avoid for portable business logic. |
| ERROR.TYPE | Maps error values to numeric error codes. | =IFERROR(ERROR.TYPE(A2),0). |
| IFERROR / IFNA | Returns a controlled fallback for errors or #N/A. | =IFNA(XLOOKUP(E2,A:A,B:B),"Missing"). |
Copy the monthly payment formula: =-PMT(6%/12,20*12,250000)
Final project: monthly sales report
- Import order rows with date, region, product ID, quantity, and amount.
- Clean text with TRIM and validate required fields with IF and ISBLANK.
- Return product names with XLOOKUP and calculate line totals with ROUND.
- Summarize revenue by month and region using SUMIFS; count orders with COUNTIFS.
- Show unique regions with UNIQUE and filtered high-value orders with FILTER.
- Compare this month with the prior month using date boundaries and a clear percentage-change formula.
- Audit the result with formulas, named ranges, validation checks, and a separate assumptions sheet.
Use Trace Precedents, Evaluate Formula, and error checks before sharing. Protect inputs and document assumptions. Test the workbook with empty, duplicate, boundary, and error cases.
Version and completeness notes
Excel includes hundreds of functions, and availability changes by product, release channel, and locale. This course covers the essential built-in function families used in everyday analysis and modern Microsoft 365 formulas; check Microsoft's function catalog for specialized engineering, cube, web, compatibility, and add-in functions in your target version. Formula names and comma separators here use English-language Excel.
Progress stays in this browser.