BET: Microsoft Excel Functions

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Functions > Financial > PV

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

Returns the present value of an annuity with fixed cash flows.

	rate	The fixed interest rate per period.
	nper	The total number of payments.
	pmt	The fixed payment made each period.
	fv	(Optional) The future value (or cash balance) after all the payments.
	type	(Optional) The number indicating when the payments are due: 0 = the end of the period (default) 1 = the start of the period

REMARKS

		This function allows you to calculate the present value of a series of future fixed payments with or without a future one-off payment.
		Any cash you pay out is represented by a negative number.
		Any cash you receive (start with or end with) is represented by a positive number.
		The "rate" and "nper" MUST be expressed in the same units of time: years, month or days.
		The "rate" can be entered with a percentage sign or as a decimal.
		The "nper" is the number of compounding periods.
		The "pmt" argument typically contains principal and interest but no other fees or taxes.
		The "pmt" is the fixed payment made each period.
		If "pmt" is left blank, then you must include "fv".
		If "pmt" is left blank, then 0 is used.
		If "fv" is left blank, then 0 is used.
		The "type" argument is irrelevant when "pmt" is left blank.
		If "type" = 0, then payments are made in arrears.
		If "type" is left blank, then 0 is used.
		If "fv" is left blank, then you must include "pmt".
		This function can be thought of an the opposite to the FV() function.
		Example 1 - What is the present value of receiving £10,000 in 4 years time if the discount rate is 10% (compounded annually).
		Example 2 - This is checking the answer in Example 1.
		Example 3 - What is the present value of receiving £10,000 in 4 years time if the annual discount rate is 10% (compounded monthly).
		Example 4 - This is checking the answer is Example 3.
		Example 5 - What would my original amount have been if I have £10,000 in my account now and the annual growth rate was 5.6% over the past 2 years (compounded annually).
		Example 6 - What would my original amount have been if I have £10,000 in my account now and the growth rate was 1% a month over the past 2 years (compounded annually).
		Example 7 - How much would my deposit have to be if I wanted to have saved £500,000 after 20 years if I save £1,500 at the end of every month with an annual growth rate of 4.5%.
		Example 8 - What is the price of a 10 year bond with a par value of £100 and a coupon of 10% paid annually. Lets assume the discount rate is 12%. The amount is negative as this is how much you will have to pay.
		Example 9 - What is the price of a 10 year bond with a par value of £100 and a coupon of 10% paid semi annually. Lets assume the discount rate is 12%.
		Example 10 - What is the price of a 10 year bond with a par value of £100 and a coupon of 10% paid monthly.

EXAMPLES

	A
1	=PV(10%,4,,10000) = -£6,830.13
2	=FV(10%,4,0,-6830.135) = £10,000
3	=PV(0.1/12,4*12,,10000) = -£6,714.32
4	=FV(0.1/12,4*12,0,-6714.32) = £10,000
5	=PV(0.056,2,,-10000) = £8,967.52
6	=PV(1%,2*12,,-10000) = £7,875.66
7	=PV(0.045/12,20*12,-1500,500000,0) = £33,470.85
8	=PV(12,10,10*100,100,0) = -£83.33
9	=PV(12/2,102,10/2100,100,0) = -£83.33
10	=PV(12/12,1012,10/12100,100,0) = -£83.33

Functions - P | Index - P | Office Online 2003 | Office Online 2007

Top