Forum Discussion
calculating NPV
I want to compute XNP for two projects, each with its own discount rate.
I have two tables:
- tblCashFlows with clomns: date | project | values
- bplRate with two column: project | rate
Formula should be
VPL = XNPV(tblCashFlows,tblCashFlows[Values],tblCashFlows[Date],XXXXX)
XXXXX should bring the discount value from tblRate.
What to write in XXXXX?
tblCashFlows
| Date | Project | Value |
| 31-01-17 | Project1 | 100 |
| 02-03-17 | Project1 | 100 |
| 01-04-17 | Project1 | 100 |
| 01-05-17 | Project1 | 100 |
| 31-05-17 | Project1 | 100 |
| 30-06-17 | Project1 | 100 |
| 30-07-17 | Project1 | 100 |
| 29-08-17 | Project1 | 100 |
| 28-09-17 | Project1 | 100 |
| 28-10-17 | Project2 | 100 |
| 27-11-17 | Project2 | 100 |
| 27-12-17 | Project2 | 100 |
| 26-01-18 | Project2 | 100 |
| 25-02-18 | Project2 | 100 |
| 27-03-18 | Project2 | 100 |
| 26-04-18 | Project2 | 100 |
| 26-05-18 | Project2 | 100 |
| 25-06-18 | Project2 | 100 |
Hi difrenkel
Try one of these to get the rate
Calculate(values(tblRate[Rate]),Filter(tblRate,tblRate[Project]=SELECTEDVALUE(tblCashFlows[Project])))
OR
LOOKUPVALUE(tblRate[Rate],tblRate[Project],SELECTEDVALUE(tblCashFlows[Project]))
1 Reply
Hi difrenkel
Try one of these to get the rate
Calculate(values(tblRate[Rate]),Filter(tblRate,tblRate[Project]=SELECTEDVALUE(tblCashFlows[Project])))
OR
LOOKUPVALUE(tblRate[Rate],tblRate[Project],SELECTEDVALUE(tblCashFlows[Project]))