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:
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.