How to use the PV function

Author: Oscar Cronquist Article last updated on May 05, 2021

The PV function calculates the net present value for an investment or loan. PV stands for present value.

Formula in cell C7:

=PV(C3, C4, C5)

Excel Function Syntax

PV(rate, nper, pmt, [fv], [type])

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.

What's on this webpage

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. What is the formula behind the PV function?

How is the result of the PV function calculated1

Formula:

(-pmt(1+rate*type)*(((1+rate^nper)-1)/rate)-fv)/((1+rate)^nper)

Formula in cell C7:

=-(C5*(1+C3*0)*(((1+C3)^C4-1)/C3)/((1+C3)^C4))

2. How is the result of the PV function calculated in detail?

How is the result of the PV function calculated 1

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:

=PV(C3, C4, C5)

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:

=($C$6-SUM($H$2:H2))*$C$2

The principal is what's left after the interest is paid, it is calculated in cell h3.

Formula in cell H3:

=-(F3+G3)

The formulas in cell G15 and H15 adds the amounts and return the total of interest and principal respectively.

Formula in cell G15:

=SUM(G3:G12)

Formula in cell H15:

=SUM(H3:H12)

3. What anount is needed if I want x in future value, the interest rate is constant, and the number of periods is given?

PV function calculate investment

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:

=PV(C2, C3,,C5)

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:

=-$C$7+SUM($G$2:G2)

Copy cell F3 and paste cells below.

Formula in cell G3:

=$C$2*F3

Copy cell G3 and paste cells below.

4. 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?

PV function investment with payments interest rate and periods

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:

=PV(C2, C3, C4, C5)

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:

=-$C$7+SUM($H$2:H3)+SUM($G$2:G3)

Copy cell F3 and paste to cells below as far as needed.

Formula in cell G4:G13:

=-$C$4

Formula in cell H4:

=$C$2*F3

Copy cell H4 and paste to cells below as far as needed.

Get the Excel file

How-to-use-the-PV-functionv3.xlsx

Functions in 'Financial' category

The PV Function function is one of many functions in the 'Financial' category.

ACCRINT function

Calculates the accrued interest for a security that pays periodic interest.

ACCRINTM function

Calculates the accrued interest for a security that pays interest at maturity.

AMORDEGRC function

Calculates the depreciation for each accounting period. This function is designed for the French accounting system.

AMORLINC function

Calculates the depreciation for each accounting period.

CUMIPMT function

Calculates the accumulated interest based on a start and end period on a loan.

CUMPRINC function

Calculates the accumulated principal based on a start and end period on a loan.

DB function

Calculates the depreciation of an asset for a given period using the fixed-declining balance method.

DDB function

Calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input.

DOLLARFR function

Converts a decimal number to its equivalent in fractional numbers, used in securities denominated in dollars.

EFFECT function

Calculates the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

FV function

Returns the future value of an investment based on a constant interest rate.

IPMT function

Calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate.

ISPMT function

Calculates the interest paid during a specific period of an investment.

NOMINAL function

Calculates the nominal annual interest rate based on the effective rate and the number of compounding periods per year.

PDURATION function

Calculates how many periods required by an investment to reach a given amount based on a rate in percentage.

PMT function

Returns the payment needed for borrowing a fixed sum of money based on constant payments and interest rate.

PPMT function

Calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate.

PRICEMAT function

Calculates the price per $100 nominal value of a bond that pays interest at maturity.

PV function

Calculates the net present value for an investment or loan.

RATE function

Returns the interest rate per period of an annuity.

RRI function

Calculates the growth of an investment in percent per period.

SYD function

Calculates the yearly asset depreciation of a given year.

TBILLPRICE function

Calculates the par amount (face value) for a Treasury bill.

TBILLYIELD function

Calculates the yield for a Treasury bill.

VDB function

Calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input, you may use partial periods in this function.

XNPV function

Calculates net present value for cash flows that may or may not be periodic

YIELD function

Calculates the yield for a security that pays interest. The YIELD function is designed to calculate the bond yield.

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

How to use the PV function

Excel Function Syntax

Arguments

What's on this webpage

1. What is the formula behind the PV function?

2. How is the result of the PV function calculated in detail?

3. What anount is needed if I want x in future value, the interest rate is constant, and the number of periods is given?

4. 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?

Get the Excel file

Functions in 'Financial' category

Leave a Reply

How to comment