What is the PV Function?
The PV function is categorized under Financial functionsFunctionsThe PV function is categorized under Financial functions. It will calculate the present value of an investment or a loan taken at a fixed interest rate. In financial. It will calculate the present value of an investment or a loan taken at a fixed interest rate.
In financial statement analysisAnalysis of Financial StatementsHow to perform Analysis of Financial Statements. This guide will teach you to perform financial statement analysis of the income statement, balance sheet, and cash flow statement including margins, ratios, growth, liquiditiy, leverage, rates of return and profitability. See examples and step-by-step instruction, PV will help calculate the value in today’s dollars of a series of future payments, assuming periodic, fixed payments and a fixed interest rate. The function can be used to calculate the present value with constant payments, or a future value, or the investment goal of the business.
Formula
=PV(rate, nper, pmt, [fv], [type])
The PV function uses the following arguments:
- Rate (required argument) – It is the interest rate per period. For example, if we take a business loan at 12% annual interest rate and make monthly payments, our interest rate per month is 12%/12, or 0.10%. we would enter 12%/12, or 0.10%, or 0.01, into the formula as the rate.
- Nper (required argument) – It is the total number of payment periods in an annuity. For example, for year loan and make monthly payment then the loan would have 60 periods. So NPER to be entered would be 60 months.
- Pmt (required argument) – It is the payment that is made for each period and cannot be changed over the life of the annuity. PMT includes Principal & taxes but excludes fees or taxes if any.
- Fv (optional argument) – It is the Future value of the investment or cash balance which we wish to attain. If fv is omitted, it is assumed to be 0. For example, if we want to save $500,000 to pay for a special project in 18 years, then $500,000 is the future value. We could then make a conservative guess at an interest rate and determine how much we must save each month. If fv is omitted, we must include the pmt argument.
- Type (optional argument) – The number 0 or 1 indicates when payments are due. If type is equal to 0 or omitted, payments are due at the end of the period. If set to 1, payments are due at the start.
Notes
- Annuity is a series of constant cash payments made over a continuous period. For example, a car loan or a mortgage is an annuity.
- In annuity functions, cash that we pay out, such as a deposit to savings, is represented by a negative number; cash that we receive, such as a dividend check, is represented by a positive number.
How to use the PV Function in Excel?
To understand the uses of the PV function, let us consider a few examples:
Example 1
Suppose we are given the following data:
The formula used is:
We get the result below:
A loan comprises four primary components: the loan amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. We can use the PV function to calculate the original loan amount when given the other three components.
Example 2
In the example below, the PV function is used to calculate the present value of an annuity that pays $5,000 per quarter for a period of 5 years. The interest is 10% per year and each payment is made at the start of the quarter.
The formula used is:
We get the result below:
Things to remember about the PV Function
- #VALUE! error – Occurs if any of the given arguments is non-numeric.
- One common error in using the PV function is not converting the annual interest rate into a monthly rate or quarterly rate, or as payments are made. If omitted, it can result in an error.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling and analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Types of Graphs in ExcelTypes of GraphsTop 10 types of graphs for data presentation you must use - examples, tips, formatting, how to use these different graphs for effective communication and in presentations. Download the Excel template with bar chart, line chart, pie chart, histogram, waterfall, scatterplot, combo graph (bar and line), gauge chart,
- All Excel ResourcesExcel ResourcesThe PV function is categorized under Financial functions. It will calculate the present value of an investment or a loan taken at a fixed interest rate. In financial
- Certified Financial AnalystCertified Financial AnalystCFI Financial Modeling & Valuation Analyst program is your path to become a certified financial analyst. With 12 required courses on topics ranging from accounting and finance fundamentals to financial modeling, valuation and advanced Excel skills, the CFI financial analyst certification will help