Finance Functions

cnper(rate, pv, fv) Returns the number of compounding periods for an investment to yield a specified future value.

crate(nper, pv, fv) Returns the fixed interest rate per period for an investment to yield a specified future value.

cumint(rate, nper, pv, start, end, [type]) Returns the cumulative interest paid on a loan between a starting period and an ending period given a fixed interest rate, number of compounding periods, and present value of the loan.

cumprn(rate, nper, pv, start, end, [type]) Returns the cumulative principal paid on a loan between a starting period and an ending period given a fixed interest rate, number of compounding periods, and present value of the loan.

eff(nrate, npery) Returns the effective APR.

fv(rate, nper, pmt, [[pv], [type]]) Returns the future value of an investment. If omitted, pv = 0. If omitted, type = 0.

fvadj(prin, sched) Returns the future value of initial principal after applying a series of compound interest rates.

fvc(rate, cash) Returns the future value of a series of cash flows occurring at regular intervals earning a specified interest rate.

ipmt(rate, per, nper, pv, [[fv], [type]]) Returns the interest payment of an investment or loan for a given period based on periodic, constant payments over a given number of compounding periods using a fixed interest rate and a specified future value.

irr(cash, [guess]) Returns the internal rate of return for a series of cash flows occurring at regular intervals. If irr cannot find a result that is accurate to within 1×10⁻⁵ percent after 20 iterations, an error is returned. If this happens, try a different guess.

mirr(cash, fin_rate, rein_rate) Returns the modified internal rate of return for a series of cash flows occurring at regular intervals, given a finance rate payable on the cash you borrow and a reinvestment rate earned on the cash you reinvest. There must be at least one positive and one negative number in cash.

nom(apr, npery) Returns the nominal interest rate, given the effective APR and the number of compounding periods per year.

nper(rate, pmt, pv, [[fv], [type]]) Returns the number of periods for an investment or loan based on periodic, constant payments using a fixed interest rate and a specified present value. If pmt > 0, rate and pv must be opposite signs.

npv(rate, cash) Returns the net present value of an investment given a discount rate and a series of cash flows occurring at regular intervals. npv assumes that the payment is made at the end of the period. The npv investment begins one period before the date of the first cash flow and ends with the last cash flow in the list.

pmt(rate, nper, pv, [[fv], [type]]) Returns the payment for an investment or loan based on periodic, constant payments over a given number of compounding periods using a fixed interest rate and a specified present value.

ppmt(rate, per, nper, pv, [[fv], [type]]) Returns the payment on the principal of an investment or loan for a given period based on periodic, constant payments over a given number of compounding periods using a fixed interest rate and a specified future value.

pv(rate, nper, pmt, [[fv], [type]]) Returns the present value of an investment or loan based on periodic, constant payments over a given number of compounding periods using a fixed interest rate and a specified payment.

rate(nper, pmt, pv, [[fv], [type], [guess]]) Returns the interest rate per period of an investment or loan over a specified number of compounding periods given a periodic, constant payment and a specified present value. If rate cannot find a result that is accurate to within 1×10⁻⁷ percent after 20 iterations, an error is returned. If this happens, try a different guess value.

Arguments:

• rate is the real, positive, scalar fixed interest rate per period. Typically, 0 ≤ rate < 1.
• nrate is the real, positive, scalar nominal annual rate, which, when compounded multiple times per year, yields the APR.
• apr is the real, positive, scalar Annual Percentage Rate. It equals the nominal annual rate if compounding once a year.
• fin_rate and rein_rate are the real, scalar finance and reinvestment rates.
• nper is the positive integer number of compounding periods.
• npery is the positive integer number of compounding periods per year.
• per is the positive integer period to find interest. per ≤ nper.
• pv and fv are the real, positive present value and future values. If omitted, fv = 0.
• start and end are the positive integer starting and ending periods of accumulation. start ≤ end ≤ nper.
• pmt is the real payment made each period. It usually includes principal and interest but no other taxes and fees.
• prin is the real, positive initial principal.
• sched is a vector of interest rates, each of which are applied over the same amount of time.
• cash is a vector of cash flows assigned at regular intervals. Payments are entered as negative numbers and income as positive numbers.
• type (optional) is 0 for a payment made at the end of the period or 1 for the beginning. If omitted, type = 0.
• guess (optional) is a numeric value which approximates the answer, used as a starting point for the algorithm. If omitted, guess = 0.1 (10%).

Notes:

• Interest rates are expressed as decimals, such as .0825, or as percents, such as 8.25%. You may need to calculate the interest rate per period. The rate equals the nominal annual rate divided by compounding periods in a year. For example, to find the interest rate per month based on the nominal annual interest rate: nomrate = ann_rate/12.
• Payments you make, such as deposits in a savings account or payments toward a loan, must be entered as negative numbers. Cash you receive, such as dividend checks, must be entered as positive numbers.