Vertex42 The Excel Nexus

You may recognize that discounting is the inverse of the familiar idea of compounding. Compoui and forecasts a future value based on an interest rate or capital factor rate. Discounting begins w which a discount factor is applied to obtain a present value of the amount. In the same project, t discount rate are the same rate. Therefore, it is relatively easy to work from the present to the fu present.

Most of us who have had a savings or investment account are familiar with the compounding for compounding as a lead-in to discounting. Let's use "k" as the compounding (or discounting facto end of the compounding period:

Future value (FV) = Present value (PV) * (1 + k)N where N is the number of periods to be compounded. N takes on values from N = 0 to some val To take a simple numerical example, if "k" = 8% and "N" = 3, then we can calculate the future v

Thus, if we had an opportunity to invest $10,000 for three years, we have an expectation that $1. end of three years, given that the compounding factor is 8%. The return on our investment is the return: $12,597 - $10,000 = $2,597.

If we had other opportunities to evaluate, the opportunity costs would be the difference in return! factor 1.2597 is called the future value factor. Future value factors are distinguished by always I than 1. Future value factors are easily calculated in Excel® using the "power" function or they ca finance books. Table 5-6 is an abridged collection of future value factors.

Table 5-6: Future Value Factors

Discount Rate |
Year 0 |
Year 1 |
Year 2 |
Year 3 |

5% |
1 |
1.05 |
1.1025 |
1.157625 |

7% |
1 |
1.07 |
1.1449 |
1.225043 |

9% |
1 |
1.09 |
1.1881 |
1.295029 |

These factors are easily calculated using a spreadsheet. The formula in any particular cell is: F where N is the number of the year, 0 to 4._

In Excel, the "power" function can be used to calculate the Factor equation. There are two argi is (1 + discount rate), and the second is N._

Now, about discounting: discounting begins in the future with forecasted flows; the DCF factor "k We solve for present value using the future value formula given above:

where N is the number of periods to be compounded.

The present value factors are the inverse of the future value factors. For instance, for m = 3, the 1/1.2597, or 0.7938. All present value factors will be numbers less than or equal to 1. Table 5-7 values.

Discount Rate |
Year 0 |
Year 1 |
Year 2 |
Year 3 |

5% |
1 |
0.952381 |
0.907029 |
0.863838 |

7% |
1 |
0.934579 |
0.873439 |
0.816298 |

9% |
1 |
0.917431 |
0.84168 |
0.772183 |

These factors are easily calculated using a spreadsheet. The formula in any particular cell is: F rate)N where N is the number of the year, 0 to 4. | ||||

In Excel, the "power" function can be used to calculate the denominator in the Factor equation in "power": the first is (1 + discount rate), and the second is N. |

Using the 8% factors and m = 3, suppose we had a forecast for future flows due to a present in\

N = 0, $10,000 present-time investment outflow and future forecasted inflows:

The present value of these flows given a discount of 8% is given by:

PV(inflows) = $3,000 * 0.9259 + $6,000 * 0.8573 + $7,000 * 0.7938 = $13,478

NPV of return = -$10,000 + $13,478 = $3,478 at the end of three periods.

Figure 5-3 provides a graphical presentation of the cash flows. It is usually simpler to calculate t "outflows" as negative numbers and all "inflows" as positive numbers. In the figure, outflows are inflows are arrows pointing up. With this convention, the above would be written as:

NPV = PV (all inflows) - PV (all outflows) NPV = PV (all inflows - all outflows)

60 80

Veaft

Prsjecl Cash Fkjms, SOW rtuhoul nsk sdtwstraism. 5 yaars ¡sc'iodc-l

Prsjecl Cash Fkjms, SOW rtuhoul nsk sdtwstraism. 5 yaars ¡sc'iodc-l

u |
l |
2 |
3 |
* |
j |
rotnl | |

-»75 j0 |
sio.!> |
5«i0 |
sioo |
5*0 0 |
ksm |
ncrfylnrev.nu* | |

ira/; |
j47i |
is3j* |
shm |
sai 7 |
§224 |
»130x1 |
net present v-nlur |

1 |
dl889wb |
c.63m-s |
o.tffimj |
0 7472ifl |
tv f&tl* it en |

Figure 5-3: NPV Example. At 8% discount rate:

Figure 5-3: NPV Example. At 8% discount rate:

NPV = -$10,000 + $3,000 * 0.9259 + $6,000 * 0.8573 + $7,000 * 0.7938 = $3,478

NPV is our first risk-adjusted measure of project financial performance. If the outflows are the c; the inflows are the cash received because of the project, then in the case illustrated above it ma because the NPV = $0. If the NPV is negative, then more money is going out of the business th. risk-adjusted basis; it makes no financial sense to do projects of negative NPV:

Decision policy: Only projects with NPV = $0 will be selected for ex

Was this article helpful?

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

## Post a comment