Formulas and Functions

MIRR
The MIRR function returns the modied internal rate of return for an investment that
is based on a series of potentially irregular cash ows (payments that do not need to
be a constant amount) that occur at regular time intervals. The rate earned on positive
cash ows and the rate paid to nance negative cash ows can dier.
MIRR(ows-range, nance-rate, reinvest-rate)
 ows-range:A collection that contains the cash ow values. ows-range is a
collection containing number values. Income (a cash inow) is specied as a positive
number, and an expenditure (a cash outow) is specied as a negative number.
There must be at least one positive and one negative value included within the
collection. Cash ows must be specied in chronological order and equally spaced
in time (for example, each month). If a period does not have a cash ow, use 0 for
that period.
 nance-rate:Interest rate paid on negative cash ows (outows). nance-rate
is a number value and is either entered as a decimal (for example, 0.08) or with
a percent sign (for example, 8%) and represents the rate at which the amounts
invested (negative cash ows) can be nanced. For example, a company’s cost of
capital might be used.
 reinvest-rate: Rate at which positive cash ows (inows) can be reinvested. reinvest-
rate is a number value and is either entered as a decimal (for example, 0.08) or with
a percent sign (for example, 8%) and represents the rate at which the amounts
received (positive cash ows) can be reinvested. For example, a company’s short-
term investment rate might be used.
Usage Notes
Cash ows must be equally spaced in time. If there is no cash ow in a particular Â
time period, use 0.
Example 1
Assume you are presented with the opportunity to invest in a partnership. The initial investment
required is $50,000. Because the partnership is still developing its product, an additional $25,000 and
$10,000 must be invested at the end of the rst and second years, respectively. In the third year the
partnership expects to be self-funding but not return any cash to investors. In the fourth and fth years,
investors are projected to receive $10,000 and $30,000, respectively. At the end of the sixth year, the
company expects to sell and investors are projected to receive $100,000. Assume that you can currently
borrow money at 9.00% (nance-rate) and can earn 4.25% on short-term savings (reinvest-rate).
Using the IRR function, you can determine the expected rate of return on this investment. Based on
the assumptions given, the rate would be approximately 9.75%.
12 8 Chapter 6 Financial Functions