Net Present Value for different Scenarios in Tableau?
Nathan Jones Jul 28, 2019 8:02 PMLet's say I have some investment data, divided out by project and scenario. It looks something like this:
Scenario  Project  2019  2020  2021  2022  2023  2024  2025 
1  Project 1  30  80  96  105.6  112.992  158.1888  284.7398 
2  Project 1  20  80  96  105.6  112.992  158.1888  284.7398 
3  Project 1  30  80  96  105.6  112.992  158.1888  284.7398 
4  Project 1  10  80  96  105.6  112.992  158.1888  284.7398 
1  Project 3  80  50  60  66  70.62  98.868  177.962 
2  Project 3  160  50  60  66  70.62  98.868  177.962 
3  Project 3  80  50  60  66  70.62  98.868  177.962 
4  Project 3  80  50  60  66  70.62  98.868  177.962 
Each year represents the value of that project in different investment scenarios.
I want to find the Net Present Value for each of those Project + Scenarios in Tableau. This is easy in Excel, you just take
NPV(.08, C2:I2)
NPV(.08, C3:I3)
NPV(.08, C4:I4)...
and so on and so forth.
You come up with some NPVs that look like this:
Scenario  Project 1  Project 3 
1  $592.92  ($427.29) 
2  $583.66  ($501.36) 
3  $592.92  ($427.29) 
4  ($574.40)  ($427.29) 
I've attached an Excel here that showcases this in a very simple fashion.
In Tableau, though, it seems a bit harder. I think what you would do is bring in the data, pivot it, and then use this particular formula:
RUNNING_SUM(SUM([Payments])/(1+[Annual Interest Rate Parameter])^((MAX([Date])LOOKUP(MAX([Date]),FIRST()))/365))
From Net Present Value , but I can't seem to get it to work. I think I've got the table calculation correct, and I'm setting it to restart every project, but my numbers are still wacky and I'm not entirely sure why. I've checked and it's pulling the right values for the NPV calculation, so I think the above NPV calculation may not work for data like this?
Here's the values I end up with:
Scenario  Project 1  Project 3 
1  868  603 
2  1,725  1,287 
3  2,593  1,890 
4  3,440  2,494 
Which are pretty wildly different than those above. Any ideas?
I've attached the Excel with the ideal outputs and my quick attempt at a twbx.

NPV_Attempt.twbx 19.7 KB

npv_chart.xlsx 10.5 KB