Excel offers a few simple formulas for determining the value of projects over time. In this article, I will cover PV and FV. For most financial applications, some subset of the formulas listed above will be more than enough to determine the value or rate of return of a particular project.

The PV and FV formulas are very similar in construct, but give the opposite answers. PV, which is present value, will tell you what the value of a string of future payments is today. Conversely, FV, which is future value, tells you what the value of your current holdings will be in the future.

As a brief primer, the time value of money is an important concept in finance. The simplest way to explain this concept is to understand that if you had $1 dollar today, it is worth more then $1 in a year for any interest rate greater than 0%. Let us assume that the current annual interest rate is 3%. If you invested the $1 you have today, it would be worth $1.03 at the end of the year (annual payment, no continuous compounding). Clearly, that is more than merely $1.00. Now, if you were to take this example to the next level and assume that the interest rate of 3% holds for five periods, the future value is now $1.16. Notice in this simple example, the future value is $1.16 and not $1.15, indicating the power of compounding. For an example that has a much larger difference, consider $10,000 today with an annual interest rate over 10 years of 8%. The future value is $22,589.25 versus the non-compounded total of $18,000.00 – a difference of $3,589.25. This is a bit of a digression and will be explained in further articles, but the summary of all of this is that the time value of money is a fundamental concept in financial theory and practice, and Excel makes it easy to calculate.

The formula for PV is =PV(rate,nper,pmt,[fv],[type]), where rate is the interest rate per period, nper is the total number of period in the calculation, pmt is the periodic payment, if any, future value of the project (optional) and type of payment stream (optional). For type of payment stream, use 1 for beginning of period payments and 0 or blank for end of period payments. In virtually all financial applications I have done, I have dealt with end of period payments, so the "type" is left blank. The reason the "fv" is optional is that for a project producing periodic payments, the fv may be zero and the calculation is just for determining the present value of periodic payments over some time period. For example, if there is an annual interest rate of 6%, periodic payments of $10.00 over 30 periods, the present value is $137.65. When there is no specific future value identified, fv = 0.

Similar to PV, the formula for FV is =FV(rate,nper,pmt,[pv],[type]). The same explanations hold for FV as in PV, and PV in this formula is optional. For example, in the case where there is no identifiable present value, pv is equal to zero. Assume that the pv equals zero, the annual interest rate is 6%, there are 30 periods and the payment is $10.00 per period. If you plug this into an Excel formula, you will get a future value equal to ($790.58). Why would you have a negative future value? To understand the answer to this question, it is important to consider what you are trying to determine. Present value calculations return negative values (under normal circumstances) because that is the "cost" of achieving a stated future value. The cost, in this case, is really the investment needed to generate the future value calculated using the formulas. If you think back to the first example, investing $1.00 is what it takes to get to $1.03 one year later. You do not have access to that dollar once it is invested, so from your perspective, it is a cash outflow, or a negative cash event to you. Coming back to future value, when you have positive payments, the formula interprets that as you paying out that amount, not receiving, over the defined period of years (this should be reasonably intuitive, since you must enter a present value, if known, as a negative number for purposes of calculating future value). When you input ($10.00) for the payment, the future value is correctly calculated as $790.58.

In summary, the PV and FV formulas save time and effort when using spreadsheets that require many calculations for present and future values. In the next article, I will discuss NPV and IRR, two formulas critical in evaluating projects. For now, just remember that cash in your hands today trumps the same amount of cash in the future because you can take the money now and invest.