7 Replies Latest reply on Jul 29, 2019 9:15 AM by John Sarantos

    Net Present Value for different Scenarios in Tableau?

    Nathan Jones

      Let's say I have some investment data, divided out by project and scenario.  It looks something like this:

       

      ScenarioProject2019202020212022202320242025
      1Project 1308096105.6112.992158.1888284.7398
      2Project 1208096105.6112.992158.1888284.7398
      3Project 1308096105.6112.992158.1888284.7398
      4Project 1108096105.6112.992158.1888284.7398
      1Project 3-80-50-60-66-70.62-98.868-177.962
      2Project 3-160-50-60-66-70.62-98.868-177.962
      3Project 3-80-50-60-66-70.62-98.868-177.962
      4Project 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:

       

      ScenarioProject 1Project 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:

       

      ScenarioProject 1Project 3
      1868-603
      21,725-1,287
      32,593-1,890
      43,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.