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

# Net Present Value for different Scenarios in Tableau?

Let'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.189 284.74 2 Project 1 20 80 96 105.6 112.992 158.189 284.74 3 Project 1 30 80 96 105.6 112.992 158.189 284.74 4 Project 1 10 80 96 105.6 112.992 158.189 284.74 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.

• ###### 1. Re: Net Present Value for different Scenarios in Tableau?

Excel is a matrix based application whereas Tableau treats each column separately, which is what will make this tough. (I am assuming each of the years is a new column). What you could do is pivot the dates so that you create a new column, then SUM on that (you may also need a discount column)

• ###### 2. Re: Net Present Value for different Scenarios in Tableau?

Hi Will,

I definitely understand the difference between Excel and Tableau, and if you'll see in my example workbook, I did exactly what you're mentioning -- pivoted the columns and used that to create my table calculation.

As I've mentioned above, I've successfully got it summing the right numbers, but the end result is wrong.  My concern is that this particular NPV formula ( Net Present Value  ) may not work in this particular scenario, or that I'm missing something in my calculation.

What is this you mention about a 'discount' column?

• ###### 3. Re: Net Present Value for different Scenarios in Tableau?

ok,

When are you discounting to? I assumed you are discounting to 2019 - but the excel seems different

this is what I am getting:

• ###### 4. Re: Net Present Value for different Scenarios in Tableau?

NVM figured it out,

you are discounting to the year before 2019 (or start from end or whatever) essentially 2019 gets discounted and year, 2020 2 years etc:

calcs:

Years since 2018

Discount Rate

1/((1+[Annual Interest Rate] )^ [Year since 2018])

NPV2

[Discount Rate]*[Value]

NPV2 is what you are looking for

1 of 1 people found this helpful
• ###### 5. Re: Net Present Value for different Scenarios in Tableau?

This is it!  Thanks for the diligent and thoughtful answer!

• ###### 6. Re: Net Present Value for different Scenarios in Tableau?

Hi John,

Not sure what you're trying to do, but ultimately Will's answer is correct.  I'm not sure yours really answers my question, but thanks for the help!

• ###### 7. Re: Net Present Value for different Scenarios in Tableau?

Hi Nathan,

Looks like I wasn't looking at the right place for your 'desired result!'  Sorry about that - I'm going to remove my earlier post so I don't send anyone down the wrong path!

Thanks,

John