# How to use the PV function

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

Formula in cell C7:

### 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)
- Download Excel file

## 1. What is the formula behind the PV function?

Formula:

Formula in cell C7:

## 2. 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:

## 3. 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.

## 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?

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.

### Functions in 'Financial'

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

How to use the ACCRINT function

The ACCRINT function calculates the accrued interest for a security that pays periodic interest. Formula in cell C9: =ACCRINT(C2,C3,C4,C5,C6,C7,C8) Excel […]

How to use the ACCRINTM function

The ACCRINTM function calculates the accrued interest for a security that pays interest at maturity. Formula in cell C7: =ACCRINTM(C2,C3,C4,C5,C6) […]

How to use the AMORDEGRC function

The AMORDEGRC function calculates the depreciation for each accounting period. This function is designed for the French accounting system. Formula […]

How to use the AMORLINC function

The AMORLINC function calculates the depreciation for each accounting period. This function is designed for the French accounting system. Formula […]

How to use the CUMIPMT function

The CUMIPMT function calculates the accumulated interest based on a start and end period on a loan. The image above shows […]

How to use the CUMPRINC function

The CUMPRINC function calculates the accumulated principal based on a start and end period on a loan. The image above shows […]

The DB function calculates the depreciation of an asset for a given period using the fixed-declining balance method. Formula in […]

The DDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]

How to use the DOLLARFR function

The DOLLARFR function converts a decimal number to its equivalent in fractional numbers, used in securities denominated in dollars. Excel […]

How to use the EFFECT function

The EFFECT function calculates the effective annual interest rate, given the nominal annual interest rate and the number of compounding […]

The FV function returns the future value of an investment based on a constant interest rate. You can use FV […]

The IPMT function calculates the interest payment for a specific period for an investment based on repeated constant payments and a […]

The ISPMT calculates the interest paid during a specific period of an investment. Formula in cell B3: =ISPMT(0.1/12,1,4*12,10000) Excel Function […]

How to use the NOMINAL function

The NOMINAL function calculates the nominal annual interest rate based on the effective rate and the number of compounding periods […]

How to use the PDURATION function

The PDURATION function calculates how many periods required by an investment to reach a given amount based on a percentage […]

The PMT function returns the payment amount needed for borrowing a fixed sum of money based on constant payments and […]

The PPMT function calculates the principal payment for a specific period for an investment based on repeated constant payments and a […]

How to use the PRICEMAT function

The PRICEMAT function calculates the price per $100 nominal value of a bond that pays interest at maturity. Formula in […]

The PV function calculates the net present value for an investment or loan. PV stands for present value. Formula in […]

The RATE function returns the interest rate per period of an annuity. Formula in cell E3: =RATE(B3,C3,D3) Excel Function Syntax […]

The RRI function calculates the growth of an investment in percent per period. Formula in cell C6: =RRI(C2, C3, C4) […]

The SYD function calculates the yearly asset depreciation of a given year. Formula in cell C6: =SYD(C2,C3,C4,C5) Excel Function Syntax […]

How to use the TBILLPRICE function

The TBILLPRICE function calculates the par amount (face value) for a Treasury bill. Formula in cell C6: =TBILLPRICE(C2,C3,C4) Excel Function […]

How to use the TBILLYIELD function

The TBILLYIELD function calculates the yield for a Treasury bill. Formula in cell C6: =TBILLYIELD(C2,C3,C4) Excel Function Syntax TBILLYIELD(settlement, maturity, […]

The VDB function calculates the depreciation of an asset for a given period using the double-declining balance method or based […]

The XNPV function calculates the net present value for cash flows that may or may not be periodic. Net present […]

The YIELD function calculates the yield for a security that pays interest. The YIELD function is designed to calculate the […]

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse 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 OscarYou can contact me through this contact form