Microsoft Office Tutorials and References

In Depth Information

**The Basic Excel Financial Functions**

You may have noticed that in the preceding formula, the interest rate (12%) appeared out of thin

air. The PV function is usually used to determine how much a specific future amount is worth

today. A specific interest rate is not available in those situations.

There are a lot of opinions on what discount rate you should use, and which one you

choose depends a lot on your personality. Some say that you should use the interest

rate you would get from a bank if you borrowed the money with no collateral. Others

say that you should use the interest rate you would receive if you made a risk-free

investment, like in a U.S. Treasury bill. In this example, I use the rate of return you

would make if you invested the money in the stock market.

By choosing 12% in this example, I’m saying that you can take the $6,780, invest it so that you

make a 12% return, and you’ll be in the same financial position as if you had just waited for the

$1,200 payments. If the payer offers you $7,000, you can invest that and be in a better position.

Now let’s turn the tables and say that you have an obligation to pay someone $1,200 per year for

ten years. That formula looks like this:

=PV(12%,10,–1200,0,0)

Instead of a positive cash flow, this formula shows a negative cash flow. The result, $6,780.27, is

also oppositely signed from the previous result. In both examples, the sum total of the payments

constitutes the entire transaction, so there is no future value. Also, the default value of zero for

the type argument is included. Both the fv argument and the type argument are optional, but

they are included here for clarity. Figure 11-1 shows these examples in a workbook.

For simplicity, the formulas presented in this chapter use literal values for function

arguments. In most cases, you’ll use cell references for the arguments.

Figure 11-1:
Some present value calculations.

Present value of a lump sum

The previous examples dealt with a series of future cash flows, but sometimes there’s just one

large future cash flow — a lump sum.