Nashville State Community College



How to Calculate Present Value & Future Value Using Microsoft ExcelExcel FunctionsA function in Excel is a built-in formula. All Excel functions begin with the = sign. The function arguments (specifications) are enclosed in parentheses. Always enter amounts in Excel formulas without any formatting. For example, $10,000 should be entered as 10000. Percents should be converted to decimals for use in Excel formulas. For example, 15% should be entered as .15.Time Value of Money FunctionsPresent Value Function (PV)Returns the current value of a future sum of money or stream of payments. The function syntax is as follows:=PV(rate,nper,pmt,[fv],[type])[The items shown in brackets are not required for certain types of PV calculations.]rate: the interest rate. Remember that the interest rate must be converted appropriately. For example, an investment with an APR of 12% compounded monthly would use an interest rate of 1% (12%/12).nper: number of periods or payments. If calculating PV for a single amount (lump sum), nper = 0. Remember that the number of payments for annuities must be converted appropriately. For example, a 5 year payout for an annuity with monthly payments would have 60 payments (5 years X 12 monthly payments). nper = 60.pmt: the amount of the annuity payment made each period. Amounts paid out should be entered as negative amounts while amounts received are entered as positive amounts. If you make monthly payments of $500, pmt would be entered as -500. If the PV function is being used to calculate the present value of a single amount there will be no payments, pmt =0.fv: the desired future value. Enter this amount when you are calculating the present value of a future single sum. Omit this amount if you are calculating an annuity by placing a single comma in place of data.type: indicates whether the payment is an ordinary annuity or an annuity due. Enter 0 for an ordinary annuity or 1 for an annuity due. Omit this argument if you are not calculating an annuity.How to Enter the Function Arguments In the PV function – Single PaymentDetermine the present value of $5,000 to be received in 4 years assuming an annual interest rate of 8%.=PV(rate,nper,pmt,[fv],[type])=PV(.08,4,0,5000)How to Enter the Function Arguments In the PV function – Ordinary AnnuityDetermine the present value of a note requiring 6 equal payments of $1,500 payable at the end of the first year assuming an annual interest rate of 8%.=PV(rate,nper,pmt,[fv],[type]) =PV(.08,6,1500,,0)How to Enter the Function Arguments In the PV function – Annuity DueDetermine the present value of a note requiring monthly payments of $500 for 3 years payable at the beginning of the first year assuming an interest rate of 6%.=PV(rate,nper,pmt,[fv],[type]) =PV(.02,36,500,,1) Future Value Function (FV)Returns the value that a sum of money or stream of payments will be worth at a future point in time. The function syntax is as follows:=FV(rate,nper,pmt,[pv],[type])The components for the FV function are the same as those described for the PV function except that the amount entered for pv in the formula represents the current value of the investment.How to Enter the Function Arguments In the FV function – Single PaymentDetermine the future value of $5,000 invested for 6 years assuming an annual interest rate of 8%.=FV(rate,nper,pmt,[pv],[type]) =FV(.08,6,0,5000) How to Enter the Function Arguments In the FV function – Ordinary AnnuityDetermine the future value when 4 equal annual payments are invested at the end of the first year earning an annual interest rate of 8%.=FV(rate,nper,pmt,[fv],[type]) =FV(.08,4,1500,,0) How to Enter the Function Arguments In the FV function – Annuity DueDetermine the future value of annual payments of $500 for 3 years invested at the beginning of the first year assuming an interest rate of 6%.=FV(rate,nper,pmt,[fv],[type]) =FV(.06,3,500,,1) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download