Decision Trees with Discounted Cash Flows

Usually, when you build a decision tree you assume that all events take place close enough together that time value of money is not important. However, to be precise, you should replace each incremental value with a present value. The easiest way to do this is to create your own discounting function and use it in each incremental value entry.

Lets modify the real estate tree to discount all cash flows by 7% per year. Furthermore, lets assume you purchase the property in year zero, and each rezone request takes a year. This means you can sell the property in year one or two depending on if you appeal the zoning boards decision.

You start with the following decision tree:


The first thing you will do is edit the -200000 in the Buy Property node and replace it with Pv(-200000,0). Pv is a function that will be created later. It takes two arguments, the cash flow amount and the year you encounter the cash flow.


Note that Pv now shows up as an undefined node. Select this node and enter the following formula:


Also, enter the following definition for the discount rate:


With a little formatting change on the nodes Pv and Int, the tree now looks like this:


Since you dont want the node Pv showing up every place you use it, you should make it a hidden node. To do this, select Pv and then point to Node in the Format menu and click Hide Node. If you later want to display Pv so that you can change it, just point to Node in the Format menu and click Show Hidden Nodes.

After hiding Pv and using it in every incremental cash flow, you get the following tree:


One interesting thing to note is that applying time value of money to the real estate example changed the outcome values enough that now the best option is to do nothing.

This example uses a custom function to calculate present values. Using a custom function instead of a built in function allows you to enter the discount rate in the definition of Pv rather than have to include it in each incremental value. Building your own discounting function also gives you the opportunity to specify input data in any way you like. For example, instead of using the year values 0, 1, 2, etc., suppose you want to enter actual years such as 2002, 2003, 2004, etc. You can do this simply by changing the definition of Pv from




This will discount all values back to the year 2002.

The following possible definition for Pv accepts a list of annual cash flow values:


With this definition, an expression such as Pv(0,-100,-50) means there is a cash outflow of 100 and 50 in years one and two respectively.

You are free to choose any definition for Pv that best suits your analysis.

Vanguard Software Applications

Our collaborative, web-based forecasting applications unite roles, teams and departments in achieving the most accurate forecasts in the world – bar none.