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:
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.
About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained and LLCs Explained websites.
Want to read more about MS Excel tips and tutorials? Visit Hot Excel.