Calculating Present Values With Microsoft Excel

Business Articles | May 15, 2008

Making present value calculations, such as for loan analysis or investment planning? Microsoft Excel can help. Excel's PV, or present value, function lets you easily calculate the present, current day, value of a future cash flow or of a regular payment stream.

The PV function calculates the present value of an annuity, or future value, given the periodic rate, number of periods, payment, future value (or balloon payment), and, optionally,the type-of-annuity switch.

Sometimes, why you would use this function seems strange. But it's often useful when working with loans or with investments. For example, if you want to know the current day value of a stream of future payments (such an annuity), you can use the PV function. Or if you want to estimate the value of an investment that pays a regular payment for a certain number of months or years, you can use the PV function. Typically, the function uses the following syntax:

PV (rate, nper, pmt, fv, type)

For example, if you want to estimate the outstanding balance on a mortgage loan that charges 8%, requires two hundred more $1,000-a-month payments, and also requires a $10,000 balloon payment, you can use the following formula:

=PV (.08/12,200,-1000,-10000)

The function returns the value $112,932.75.

TIP: If you have Excel running on the computer you're reading this article on, you can copy the above formula to a cell in an Excel workbook and make the actual present value calculation yourself.

As mentioned, the PV function includes a type-of-annuity switch you use to specify whether the payments occur at the beginning of the period (start of the month or year) or at the end of the period (end of the month or year). If you set the type-of-annuity switch to 1, Excel assumes payments occur at the beginning of the period, following the annuity due convention. If you set the annuity switch to 0 or you omit the argument, Excel assumes payments occur at the end of the period following the ordinary annuity convention.

And a final note: You must include either the payment (or pmt) argument or the future value (or fv) argument in order to calculate the present value. The PV function, predictably, needs something—either a payment stream or a future value—to calculate the present value of.

Article Tags: Present Value, Payments Occur

Source: Free Articles from ArticlesFactory.com

ABOUT THE AUTHOR

Seattle accountant and bestselling computer book author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself kits that businesses and investors can use for setting up a Limited Liability Company, S Corporation or Incorporation.