Syntax. PV(Rate,Nper,Pmt,Fv,Type)
Definition. The PV() function calculates the cash value of a regular payment flow, taking into consideration possible single payments at the end of the period in question, according to the finance mathematical benefit principle:
Payment of the debtor + payment of the creditor = 0
Arguments
Rate (required) Specifies the (constant) period interest rate as an annuity interest rate.
Nper (required) Specifies the number of interest periods. It is assumed that possible regular payments (in other words, that the Pmt argument is greater than zero) take place at the end or the start of the interest period.
Pmt (required/optional, see Note) Informs you about the amount of the regular payments and can be interpreted as an annuity.
Fv (optional/required, see Note) The account balance you want to have at the end of the process (for example, a residual balance from disbursement plans or a final repayment in the amount of the residual debt for loans).
Type (optional) Specifies whether regular payments take place at the end of the periods (Type = 0 or not specified) or at the start of the periods (Type = 1).
Note
If the Pmt argument is omitted, Fv must be specified. If Pmt is present, Fv can be omitted. Omitting the arguments has the same effect as specifying them as zero.
Important
The preceding finance mathematical benefit principle means that you need to differentiate between disbursements and deposits in regard to the leading sign (borrowing and repayment, investment and disinvestment). Here Excel differs from the benefit principle used in finance mathematical literature:
Payment of the debtor = Payment of the creditor
Background. The five functions PV() = cash value, FV() = future value, PMT() = regular payment, NPER() = interest or payment periods, and RATE() = interest rate, have the following relationship when the benefit principle is implemented:
where M is the Type (timing of payments).
The cash value interest is compounded, and so are the regular payments. Finally, the sum is compared to the future value.
Using one of these functions is equal to the respective basic finance mathematical task: calculation of an unknown variable from the equation when the other variables are known. The functions thus solve the equation for each of its members. For RATE, an approximation calculation is performed.
Note
In practice, the interest rate is almost always specified as an annual interest rate. The functions mentioned here work correctly only if the periods match the interest rate. With intra-annual interest yield, the annual interest rate is usually distributed equally over the periods: 12 months at one-twelfth of the interest rate; 3 months at a quarter, and half a year at half.
Examples. The headings of the following examples reflect the general usage of finance mathematical terms.
Compound Interest Calculation. Assume that an investor wants to have a small financial cushion for retirement and decides to invest a newly received inheritance of $10,000.00 for 15 years at a fixed interest rate of 5 percent. He hopes to get a disbursement of at least $25,000.00. Will the plan succeed?
A calculation with
=PV(5%,15,,25000)
returns –$12,025.43. This is the amount he would have to invest (thus the inclusion of the minus sign) in order to get the accrued $25,000.00 after 15 years. Therefore, the plan will not succeed.
Annuity Calculation. A 60-year-old person has saved $100,000.00 and would like to get paid an additional monthly annuity of $750.00. Is the saved money enough, if 4.5 percent p.a. is paid on top of the existing balance and payments are planned for 15 years?
You determine the cash value of the annuity with
=PV(4.5%/12,15*12,750)
and get –$98,040.08. Therefore, there is even a small amount left over after 15 years, because the sum is larger. The minus sign should be interpreted as waiving immediate disbursement.
The calculation is based on compound interest within one year.
Repayment Calculation (Annuity Repayment). Assume that a debtor is able to pay back $1,000.00 per month on a debt (repayment plus interest). How high could this debt be, if the duration is 30 years at a fixed interest rate of 5.5 percent p.a.?
The cash value formula can be used here as well (in this case, the repayment calculation is the annuity calculation). You are using the following to get to a result of $176,121.76:
=PV(5.5%/12,30*12,-1000)
Unlike for a savings account, a mortgage loan actually uses a monthly interest of a twelfth of the agreed-upon yearly interest rate (nominal interest).
Important
Using formulas might be different from keeping detailed notes of an account history with deposits and withdrawals because of rounding errors. This happens because the numbers for a savings account are rounded to two decimal places. If you are re-creating such an account with Excel, you should use the ROUND() function for intermediary steps that involve money. Simply limiting the display of the cell value to two decimal places often leads to incorrect results.
Investment Calculation. The capital value method of the investment calculation determines the cash value of future income surpluses and compares them to the purchase cost. Normally an interest rate of 10 percent is used for the calculation. If the income surpluses are due as regular payments and if the investment asset has a residual value after the disinvestment duration, the capital value can be determined by using the following pattern:
–PV(10%,number of years,annual income surplus,residual value)–purchase cost
The investment makes sense from an economic standpoint, if the calculated amount is positive.
Price Calculation. You can also find ways of using the PV() function in the examples for RATE() and YIELD().