It seems obvious that receiving $10,000 today would be of greater value than receiving $10,000 in ten years’ time.

The question is, “Why?”

The reason is that money possesses what is called “earning capacity”. You could take the $10,000 received today and invest the money over 10 years to make more money. Also, money loses value as prices increase over time. This is known as Inflation.

There is also the unknown risk of Time. Will the person make good on their promise and actually pay you what is owed? There is an uncertainty factor at play that holds many possibilities for risk. Markets decline, people die (*sorry to get morbid, but it’s true*), wars start, and sometimes even peace prevails.

The combination of these factors defines the Time Value of Money, and it’s usually expressed as a percentage.

The Discount Rate is always specific to your situation. It all depends on how you believe the above three factors will impact you over the coming years.

- Are your
**Opportunity Costs**high because you know of a lucrative investment you could participate in instead? - Do you believe that
**Inflation**will increase over the life of the agreement? - Do you believe that the
**Risk**of not getting paid is high or low?

Also, the further into the future the payments are, the greater the opportunity costs, inflation, and risk you will encounter. This takes away from the value.

Featured Course

## Fundamentals of Financial Analysis

## Revisiting the Proposed Scenario

Let’s return to our opening question. Is it better to receive $10,000 today, or $10,800 over 4 years?

In order to answer this question, we need to compare the value of both options against the same point in time; today. We need to calculate what is known as the **Present Value** of the future payments of the $10,800.

## Running the Numbers in Excel

Switching to Excel, let’s examine our two options.

**Option 1** represents the receipt of the $10,000 today, while **Option 2** represents the undiscounted receipt of 4 equal payments of $2,700.

Columns **E** through **H** represents the years of payment (*1 ^{st} through 4^{th} year*).

At first glance, the undiscounted $10,800 of **Option 2** is clearly greater than the $10,000 of **Option 1**. By “undiscounted” we mean that the **Time Value of Money** is not being taken into consideration.

Because payments in the future have less value of equal payments today, we need to discount them; reduce them to get the present value of these payments. In other words, what are each of these payments of $2,700 worth to us today?

We will assume that our **Discount Rate** is equal to 5.0%. The Discount Rate implies that in each successive year, the same amount of money will be worth 5.0% less than in the previous year. It’s like the opposite of earning interest on a deposit. Instead of *increasing* the value, it’s *decreasing* the value.

Featured Course

## Excel Essentials for the Real World

## Creating a Manual “Present Value” Calculation

We’ll perform our first calculation manually. This way we can understand the logic that makes up each of the formula components. Later, we’ll see a built-in Excel function that will perform this calculation for us with much less effort on our part.

To find out what the 1^{st} payment’s value (**E11**) is worth today, we will divide the value (**E7**) by **1 **plus the **Discount Rate** (**D9**).

`=E7 / (1 + D9)`

If you’re wondering why we are dividing this instead of multiplying it, it’s because we are moving backward in time, from the future back to the present. Remember, it’s like the opposite of earning interest. If we were to invest $2,571 earning a 5.0% return { =E7*(1+D9) }, we would see our money grow to $2,700 in one year. It’s just the reverse logic.

If you are moving backward from the future, you ** divide**. If you are moving forward into the future, you

**.**

__multiply__### Calculating the Remaining Years

To calculate the remaining year’s payments, we need to make a small adjustment to the formula. We need to examine the previous year’s basis value for each successive year. **Year 2**’s payment (**F11**) needs to examine **Year 1**’s result (**E11**) to base its calculation.

`=E11 / (1 + $D$9)`

Fill the calculation over to the remaining years (*G11** and H11*).

## The Logic of the Present Value (PV) Formula

Below is a breakdown of the **Present Value** (PV) formula.

We could manually create this formula (*starting in cell E11*) as follows:

`=E7 / (1 + $D$9) ^ E3`

The caret symbol can be found above the number 6 when using the numbers across the top of the keyboard.

You can also write the formula using the POWER function.

`=E7 / POWER((1 + $D$9), E3)`

## Is this Profitable?

If we use the **SUM** function to add the value of cells **E11** through **H11**, we see that the total value over 4 years comes to $9,574. This is less than our original $10,000 which tells us that this is not a wise investment.

## Using Excel’s Net Present Value (NPV) Function

To make life easier, Excel comes equipped with an NPV function that can calculate these results for us.

The NPV function syntax is as follows:

**NPV( rate, value1, [value2] )**

** rate** – Required. The rate of the discount over the length of the period.

**value1**, **value2**, … – Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income

- Value1, value2, … must be equally spaced in time and occur at the end of each period.
- NPV uses the order of value1, value2, … to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
- Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.
- If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.

Using the NPV function (*starting in cell D13*), we write the following formula:

`=NPV(D9, E7:H7)`

If we modify the formula to deduct the result of the **NPV** function from the original **Option 1** value, we can see if this is a wise investment opportunity.

`=NPV(D9, E7:H7) – D5`

The result is a loss of $426 dollars over the life of the loan. Clearly a poor investment decision.

## Revisiting the Discount Rate

All these results are based on a **Discount Rate** of 5.0%. Suppose the **Discount Rate** was to be valued at 2.0?

With this adjustment, we end up with a positive result which tells us that we would make money over the payment life assuming the 2.0% holds.

## What is the correct discount rate?

Calculating the **Discount Rate** depends on how you assess the three factors that impact the **Time Value of Money**.

For companies, it’s related to how they obtain their funds. Companies use a discount rate that is usually an average rate of return their investors expect and the cost of borrowing money. This is known as WACC (**W**eighted **A**verage **C**ost of **C**apital).

Any project that calculates to a positive NPV would be considered a potentially wise investment.

Featured Course

## Power Excel Bundle

## Example of Machinery Purchase

Let’s look at another example of this using the idea of purchasing a piece of machinery.

Here are the facts of the example:

- The new machine costs $50,000
- The new machine is expected to produce $15,000 in productivity savings over the next 4 years

Ignoring the return percentage for the moment, the formulas to calculate cash flows are:

Years 1 through 4 (*cells E9 through H9*

**)**

`=$D$4`

Year 0 (*cell D9*

**)**

`=D3`

A quick **SUM** of the values (**D9:H9**) yields a profit of $10,000. A $10,000 return sounds like a great investment.

But here’s another factor for our decision making; The Shareholders expect a return of at least 8% on their investment, so we only want to invest in opportunities that yield at least this return.

### Playing the “What If” Game at 8%

We’ll place a value of 8.0% in our spreadsheet (*cell D5*). Using Excel’s NPV function, we create the following formula in cell

**D11**:

`=NPV(D5, E9:H9)`

This gives us a result of $49,681.90.

Now we’ll modify the formula to deduct the original amount invested (*cell D9*).

*NOTE: Since the value in cell*

**D9**is negative, we’ll add it to the result in cell**D11**.`=NPV(D5, E9:H9) + D9`

This gives us a result of -$318.10. We know that the project will fail to return the minimum investment that The Shareholders expect.

What if we lower the expected return to 6.0%?

Now we see a return of $1,976.58. The **Net Present Value** is positive which makes this an attractive offer.

## 2 Key Takeaways

- Always consider the timing of when payments are done. The further in the future the payment is made, the less valuable it will be.
- Think of the
**Discount Rate**as a hurdle. The higher the hurdle, the more difficult it will be to overcome; the more difficult it will be to obtain a high**Present Value**.

### Download Workbook

Feel free to Download the Workbook HERE.

### Leila Gharani

I'm a 5x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.