Purpose

Return value

Syntax

=IRR(values,[guess])
  • values - Array or reference to cells that contain values.
  • guess - [optional] An estimate for expected IRR. Default is .1 (10%).

Using the IRR function

The internal rate of return (IRR) is the interest rate received for an investment with payments and income occurring at regular intervals (i.e. monthly, annual). Payments are expressed as negative values and income as positive values. Amounts can vary, but intervals need to be the same. The first value is negative, since it represents an outflow.

Excel uses iteration to arrive at a result, starting with the guess (if provided) or with .1 (10%) if not. If an accurate IRR can’t be calculated after a fixed number of iterations, the #NUM error is returned. A better guess will prevent this error.

Notes

  • The values array must contain at least one positive value and one negative value.
  • Values should be in chronological order.
  • If IRR returns the #NUM! or an unexpected result, adjust guess .

Purpose

Return value

Syntax

=ISPMT(rate,per,nper,pv)
  • rate - Interest rate.
  • per - Period (starts with zero, not 1).
  • nper - Number of periods.
  • pv - Present value.

Using the ISPMT function

The ISPMT function calculates the amount of interest in a given period of an investment where principal payments are equal. The given period is specified as a zero-based number instead of a 1-based number. For example, to calculate the interest amount in payments for a loan where the amount is $10,000, the interest rate is 10%, and there are 5 periods in which the principal payment is constant (even), you can use:

=ISPMT(10%,0,5,-10000) // interest in period 1
=ISPMT(10%,1,5,-10000) // interest in period 2
=ISPMT(10%,2,5,-10000) // interest in period 3
=ISPMT(10%,3,5,-10000) // interest in period 4
=ISPMT(10%,4,5,-10000) // interest in period 5

In the example shown, the formula in H11, copied down, is:

=ISPMT($C$6,B11-1,$C$7,-$C$5)

Note ISPMT assumes principal amounts are equal, but the payment is variable. For a loan where the payment is a fixed amount, see the IPMT function .

Notes

  1. Be consistent with the units. For a 3 year loan with monthly payments and an annual interest rate of 10%, enter rate as 10%/12. Enter nper as 3*12.
  2. ISPMT uses a zero-based index for period ( per ). Use 0 for period 1, 1 for period 2, etc.
  3. The PPMT function is for loans with even principal payments. For a loan with even periodic payments, use the IPMT function.