The PV, FV and PDURATION Excel Functions Tutorial



The PV Function, FV Function, and PDURATION Function are all extremely useful financial functions.

The Present Value (PV) Function is used to calculate the present value of a loan or an investment using a constant interest rate. This is basically the amount at the beginning of the loan, or investment’s lifespan.

The syntax of the PV Function is:

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

where rate is a required value and refers to the interest rate per period.

nper is a required value and refers to the total number of payment periods.

pmt is a required value and refers to the actual payment made each period. This is a constant amount over the duration of the investment/loan and cannot change.

fv is an optional value and refers to a future value or balance that you want to have in your bank, after the last payment is made. If the fv is not included, Excel assumes fv is 0.

type is an optional value that denotes when a payment is actually made. If the type argument is 0, this denotes that payment is made at the end of the period, whereas if the type argument is 1, this denotes that payment is made at the beginning of the period. If the type value is left out, Excel assumes that it is 0.

A handy tip to remember when using financial functions and trying to figure out whether a number should be positive or negative is: if its money being paid by you, for example payment of a loan, then this number should be negative, whereas if its money you are receiving for example the returns from an investment you made, then the number should be positive.

The Future Value (FV) Function is used to calculate the value of an investment or loan at some time in the future, based on a constant interest rate.

The syntax of the FV function is:

=FV (rate, nper, pmt, [pv], [type])

where rate is a required value and refers to the interest rate per period.

nper is a required value and refers to the total number of payment periods.

pmt is a required value and refers to the actual payment made each period.

pv is optional and refers to the present value that an investment is worth. If PV is omitted, Excel assumes that it is 0.

type is an optional value that denotes when a payment is actually made. If the type argument is 0, this denotes that payment is made at the end of the period, whereas if the type argument is 1, this denotes that payment is made at the beginning of the period. If the type value is left out, Excel assumes that it is 0.

The PDURATION Function is a new financial function that was introduced in Excel 2013 and it calculates the number of periods required for an investment to reach a designated future value, at a constant interest rate.

The syntax of the PDURATION Function is:

=PDURATION(rate, pv, fv)

where rate is a required value and refers to the interest rate per period.

pv is the present value of the investment.

fv is the required future value of the investment.

So, let’s get started with a few simple examples to illustrate how PV, FV, and PDURATION are utilized.

Calculating PV

We want to calculate the present value of an annuity that pays out $350 on a monthly basis, over a period of 7 years, at a 5 % interest rate.

PV Function in Excel

1) In order to calculate the PV, in Cell B7 we input the following formula:

=PV (B4/12, B5*12, B6, , 0)

PV Function in Excel Image 2

We divide the interest rate which is an annual interest rate by 12 in order to get the monthly equivalent and we multiply the 7 years by 12 in order again to convert years to months, this is because payments are monthly.



2) Upon pressing CTRL-ENTER, we get a value of -$24 763.

PV Function in Excel Image 3

3) We expect this initial amount to be negative, since its money paid out of our pockets into the annuity initially.

Calculating FV

We now want to calculate the future value of an investment, in which we are required to deposit a monthly $250, over a period of 8 years at an interest rate of 5%.

FV Function in Excel Image 1

1) In order to calculate the FV, in Cell B7 we input the following formula:

=FV (B4/12, B5*12, -250)

FV Function in Excel Image 2

Again we divide the interest rate which is an annual interest rate by 12, in order to get the monthly equivalent and we multiply the 8 years by 12, in order again to convert years to months. This is because payments are made monthly, and it’s a negative value of $250 since we are paying into the investment.

2) Upon pressing CTRL-ENTER, we get a value of $29 435.

FV Function in Excel Image 3

3) We expect this future amount to be positive, since its money that will be paid out from the investment into our pockets.

Calculating PDURATION

We want to calculate the number of months, needed by an investment of $2500, earning 3.7% interest annually, to reach $3500.

PDURATION Function in Excel Image 1

1) In order to calculate the PDURATION, in Cell B7 we input the following formula:

=PDURATION (B4/12, B5, B6)

PDURATION Function in Excel Image 2

Again we divide the interest rate which is an annual interest rate by 12, since we want to ultimately obtain the monthly periods. Note however, that all values for PDURATION are positive.

2) Upon pressing CTRL-ENTER, we get a value of 109.3 months.

PDURATION Function in Excel Image 3

3) This value means that it would take 109.3 months, for an investment with an initial value of $2500 to reach a future value of $3500.

And there you have it.

Download working file

PV-FV-PDURATION-Tutorial

Conclusion

Excel provides many useful financial functions such as Present Value, Future Value, and PDURATION – all of which evaluate inputs at a fixed interest rate.

Please feel free to tell us if you use the PV Function, FV Function or the PDURATION Function in your financial calculations.

Useful Links

The PV Function

The FV Function

The PDURATION Function

Microsoft Excel Financial Functions

The Definition of Present Value

The Definition of Future Value

Calculating the Present and Future Values of Annuities

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.