How to use the PV function
The PV function calculates the net present value for an investment or loan. PV stands for present value.
What is an investment?
An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.
What are periods?
A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.
What is present value?
The present value is the initial amount that will earn interest/dividend.
What is future value?
The compounded amount after the calculated periods based on the given rate. It measures what a current capital (present value) amount will be worth at a designated future date.
What are periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is a constant interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is the number of compounding periods per year?
The number of compounding periods per year refers to how often interest is compounded annually on an investment or loan.
Some common compounding periods:
- Annually - 1 compounding period per year
- Semiannually - 2 compounding periods per year
- Quarterly - 4 compounding periods per year
- Monthly - 12 compounding periods per year
- Weekly - 52 compounding periods per year
- Daily - 365 compounding periods per year
Related functions
| Function | Description |
|---|---|
| RATE(nper, pmt, pv, [fv], [type]) | Returns the interest rate per period of an annuity |
| PV(rate, nper, pmt, [fv], [type]) | Returns the present value of an investment. |
| FV(rate, nper, pmt, [pv], [type]) | Returns the future value of an investment. |
| PDURATION(rate, pv, fv) | Returns the periods needed for an investment to reach a future value. |
What's on this webpage
- PV Function Syntax
- PV Function Arguments
- PV Function example
- What is the formula behind the PV function?
- How is the result of the PV function calculated in detail?
- What amount is needed if I want x in future value, the interest rate is constant, and the number of periods is given?
- What amount is needed if I want x in future value, the interest rate is constant, the payment amount, and the number of periods are given?
- How much can I borrow if I can pay x per month and the interest is y? (Link)
- Get Excel file
1. PV Function Syntax
PV(rate, nper, pmt, [fv], [type])
2. PV Function Arguments
| rate | Required. The interest rate per period. If nper is years then the interest rate is per year. Divide the interest rate with 12 if you use months in the nper argument. |
| nper | Required. The total number of payment periods in an annuity. |
| pmt | Required. The payment amount per period. Interest and principal is often included in this amount but not other fees or taxes. The pmt argument is not used if you use the [fv] argument. |
| [fv] | Optional. The future value. |
| [type] | Optional. The number 0 or 1 and indicates when payments are due. |
3. PV Function example
Formula in cell C7:
4. What is the formula behind the PV function?
Formula:
Formula in cell C7:
5. How is the result of the PV function calculated in detail?
The PV function in cell C6 calculates the present value of an annuity loan or the amount you can borrow when the interest rate is 5% per year and the loan is paid back in ten years. In other words, it calculates how much the loan is based on a given interest rate, how much you pay per period, and the amount you pay per period.
An annuity loan is paid back for a specific time range at a given interest rate with the same amount for all payments.
The table in cell range E2:H12 shows interest and principal for each period based on values in cell range C2:C4. The total principal (or amortization) paid is the same as the present value in cell C6, which is needed to pay back the entire loan.
Formula in cell C6:
The result in cell C6 is the present value of the loan based on the interest rate, periods, and payment amount.
The formula in cell G3 calculates the interest included in each payment.
Formula in cell G3:
The principal is what's left after the interest is paid, it is calculated in cell h3.
Formula in cell H3:
The formulas in cell G15 and H15 adds the amounts and return the total of interest and principal respectively.
Formula in cell G15:
Formula in cell H15:
6. What anount is needed if I want x in future value, the interest rate is constant, and the number of periods is given?
The image above demonstrates how to use the PV function when you want to know the amount needed for an investment when the interest rate is specified, and the number of periods are given.
Formula in cell C7:
Note that the pmt argument can be empty when you specify the fv (future value) argument in the PV function.
The table, shown in the image above, displays the start amount and the interest for each period. The total accumulates the interest (column F) and the fv (future value) is reached after the given period. The table is not needed for the calculation, it is only there to show that the numbers add up and are correct.
See cell F13, it is equal to the future value in cell C5.
Formula in cell F3:
Copy cell F3 and paste cells below.
Formula in cell G3:
Copy cell G3 and paste cells below.
7. What amount is needed if I want x in future value, the interest rate is constant, the payment amount, and the number of periods are given?
The image above shows what amount is needed if you save x amount for each period to reach a future value [fv) given a constant interest rate.
Formula in cell C7:
The table in cell range E2:H13 shows that the numbers add up to the future value, specified in cell C5, after the given time of periods based on a constant interest rate.
The table is not needed to calculate the present value of the investment, the PV function is all that is needed.
Formula in cell F3:
Copy cell F3 and paste to cells below as far as needed.
Formula in cell G4:G13:
Formula in cell H4:
Copy cell H4 and paste to cells below as far as needed.
Useful links
PV function - Microsoft support
Excel Formula Coach - find the present value of a loan
Present Value Calculator
Functions in 'Financial' category
The PV Function function is one of many functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form