I. Intro
The PV function returns the present value of a loan.
Note: The present value is the current value of future cash flow discounted at the interest rate. It is the reverse process of the time value of money.
II. About the function
- Formula: =PV(rate,nper,pmt,[fv],[type])
- Parameters:
- rate (required): The interest rate of the loan. If you borrow a loan at an annual interest rate of 12% to buy a house and repay the loan monthly, the monthly interest rate is 12%/12 = 1%.
- nper (required): The total number of payment periods for this loan.
- pmt (required): The amount to be paid per period. This amount remains unchanged throughout the loan period. Pmt usually includes principal and interest, but not taxes and other fees
- [fv] (optional): The future value, or the cash balance you would like to receive after the last payment. If this parameter is omitted, then it's assumed to be 0, meaning the future value of the loan is 0.
- [type] (optional): The numbers 0 and 1 represent whether payments are due at the beginning or at the end of a period respectively.
- Example: =PV(2%,12,100,0,0)
- Note:
- The interest rate can't be 0.
- Please ensure that the units for the interest rate and total payment periods are consistent. For a four-year loan on an interest rate of 12% that is paid monthly, the rate should be 12%/12, and nper should be 4*12. For the same loan that is paid annually, then the rate should be 12%, and nper should be 4.
250px|700px|reset
III. Steps
Use the PV function
- Select a cell and enter =PV.
- Enter the parameters in the cell. For example =PV(2%,12,100,0,0).
- Press Enter to display the result, which is -1057.534122 in this example.
Delete the PV function
Select the cell with the PV function, and press Delete.
IV. Use case
Investment: Calculate the present value of your investment
The present value is a key metric for evaluating the quality of an investment or loan. The PV function offers a quick way to calculate the present value by entering the required parameters.
- Formula used below: =PV(B2/12,B3*12,B4)
- About the parameters:
- Since the loan's interest rate is annual, it needs to be converted into a monthly interest rate to keep the units consistent. Simply divide B2 by 12 (B2/12) to use it as the rate parameter.
- Since the loan period is in years, it needs to be converted into months. Multiple B3 by 12 (B3*12) to get the nper parameter.
- The parameter in cell B4 is already in months, so there is no need for conversion. Just select the cell to add it as a parameter and press enter to get the result.
250px|700px|reset