Calculating Present Values With Microsoft Excel
Posted on May 17, 2008
Filed Under Business |
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.
About the author
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.Tags: balloon payment, cash flow, excel workbook, future value, investment planning, investments, loan analysis, loans, microsoft excel, mortgage loan, ordinary annuity, payment stream, periodic rate, periods, present value calculation, present value calculations, present value of an annuity, pv, regular payment, syntax
Related Posts
- Calculating Present Values With Microsoft Excel
- Forecasting Future Values With Microsoft Excel
- Using Microsoft Excel’s Variable Declining Balance Depreciation Function
- Using Microsoft Excel’s Variable Declining Balance Depreciation Function
- Great Plains Customization Overview or how to modify Dynamics GP
Comments
Leave a Reply