VIEWS: 0 PAGES: 4 POSTED ON: 10/25/2011
Excel Financial Formulas Future Value The FV function returns the future value of an investment based on periodic, constant payments and a constant interest rate. Excel Formula: FV(rate,nper,pmt,pv), where: rate: is the interest rate per period. nper: is the total number of payment periods in an annuity. pmt: is the payment made each period; it cannot change over the life of the annuity. pv: is the present value, or the lump-sum amount that a series of future payments is worth right now. Example: Find the ammount accumulated when $1000 is invested for 5 years at 8% compounded quarterly. Solution: Interest rate: 0.08 Compound Periods: 4 Number of years: 5 rate: 0.02 = Interest rate / compound periods nper: 20 = Number of years * compound periods pmt: 0 pv: -1000 (note negative sign here) FV(rate,nper,pmt,pv) = $1,485.95 Present Value The PV function returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender. Excel Formula: PV(rate, nper, pmt, fv), where rate: is the interest rate per period. nper: is the total number of payment periods in an annuity. pmt: is the payment made each period; it cannot change over the life of the annuity. fv: is the future value, or a cash balance you want to attain after the last payment is made. Example: Find the present value of $1000 due after 3 years if the interest rate is 9% compounded monthly. Solution: Interest rate: 0.09 Compound Periods: 12 Number of years: 3 rate: 0.0075 = Interest rate / compound periods nper: 36 = Number of years * compound periods pmt: 0 fv: 1000 PV(rate, nper, pmt, fv) = ($764.15) Rate The RATE function returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. Excel Formula: RATE(nper,pmt,pv,fv), where nper: is the total number of payment periods in an annuity. pmt: is the payment made each period; it cannot change over the life of the annuity. pv: is the present value, or the lump-sum amount that a series of future payments is worth right now. fv: is the future value, or a cash balance you want to attain after the last payment is made. Example: Find the nominal rate compounded seminnually for an investment of $500 which amounts to $588.38 in three years. Solution: Compound Periods: 2 Number of years: 3 nper: 6 pmt: 0 pv: -500 (note the negative sign here) fv: 588.38 RATE(nper,pmt,pv,fv) = 2.75% Payment The PMT function calculates the payment or a loan based on constant payments and a constant interest rate. Excel Formula: PMT(rate,nper,pv,fv), where rate: is the interest rate for the loan. nper: is the total number of payments for the loan pv: is the present value, or the total amount that a series of future payments is worth now; also known as principal. fv: is the future value, or a cash balance you want to attain after the last payment is made Periods The NPER function teturns the number of periods for an investment based on periodic, constant payments and a constant interest rate. Excel Formula: NPER(rate, pmt, pv, fv), where rate: is the interest rate for the loan. pmt: is the payment made each period; it cannot change over the life pv: is the present value, or the lump-sum amount that a series of future payments is worth right now. fv: is the future value, or a cash balance you want to attain after the last payment is made. Effective Rate You can use the FV (future value) function to compute the 'effective rate' Example: What effective rate is equivalent to the nominal rate of 6% compounded semiannually? Solution: Interest rate: 0.06 Compound periods: 2 rate: 0.03 nper: 2 pmt: 0 pv: -1 (note the negative sign here) FV(rate,nper,pmt,pv)-1 = 6.09% (subtract 1 from FV and format as %) Annuities Examples Example: Find the present value of an annuity of $100 per month for 3 1/2 years at an interest rate of 6% compounded monthly. Solution: We use the PV (present value) function as follows: Interest rate: 0.06 Compound Periods: 12 Number of years: 3.5 rate: 0.005 nper: 42 pmt: -100 (note the negative sign here) fv: 0 PV(rate, nper, pmt, fv) = $3,779.83 Example: Find the monthly payment for a loan of $10000 for 3 years with an interest rate of 8% compounded monthly. Solution: We use the PMT (payment) function as follows: Interest rate: 0.08 Compound Periods: 12 Number of years: 3 rate: 0.006666667 nper: 36 pv: -10000 (note the negative sign here) fv: 0 PMT(rate,nper,pv,fv) = $313.36 Example: Optional: For the previous example, find the payment if the payments are made at the beginning of the month. Solution: The PMT function (like most others) can take another optional input parameter (0 = payment at end, 1 = payment at beginning of month) as follows: rate: 0.006666667 nper: 36 pv: -10000 (note the negative sign) fv: 0 PMT(rate,nper,pv,fv,1) = $311.29 Example: How many months will it take to pay off a debt of $1500 where payments of $75 will be made each month and the interest rate is 12% compounded monthly. Solution: We will use the NPER (Periods) function as follows: Interest rate: 0.12 Compound Periods: 12 Number of years: 3 rate: 0.01 pmt: 75 pv: -1500 (note the negative sign) fv: 0 NPER(rate, pmt, pv, fv) = 22.42574188