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.
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
Using Excel's Xirr and Xnpv Add-in Functions
Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.
