Overview
Cash Flow worksheet can effectively handle models with dynamic cash flows. It is a powerful tool to calculate Net Present Value (NPV) or Internal Rate of Return (IRR) if either one is known. Below are the five variables and their definitions respectively.
| Variable | Definition |
|---|---|
| CFn | Cash flow at period n |
| Freqn | Cash flow frequency at period n |
| NPV | Net Present Value |
| IRR | Internal Rate of Return |
| n | Time Period |
Example 1
What is the PV of the below cash flows given the discount rate of 15%?
| Period | 0 | 1 | 2 | 3 | 4 |
| Cash Flow | 0 | 100 | 300 | 500 | 700 |
Steps:
CF ▶ 100 ENTER ▶ ▶
300 ENTER ▶ ▶
500 ENTER ▶ ▶
700 ENTER ▶
NPV 15 ▶ CPT
Result: 1,221.17202268
Note the negative sign means a cash outflow as we assume initially a cash inflow of $100.
Example 2
What is the IRR of the below cash flow stream–assume we invest $2500 initially, get $300 at the end of year 1 and 2, $400 at the end of year 3 and 4, $500 at the end of year 5, 6 and 7?
| Bucket | 0 | 1 | 2 | 3 |
| Frequency | NA | 2 | 2 | 3 |
| Cash Flow | -2500 | 300 | 400 | 500 |
Steps:
CF -2500 ENTER ▶
300 ENTER ▶ 2 ENTER ▶
400 ENTER ▶ 2 ENTER ▶
500 ENTER ▶ 3 ENTER ▶
IRR CPT
Result: 3.4999478
DUDULEARN 