MIRR
The MIRR function returns the modified internal rate of return for an investment that is based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals. The rate earned on positive cash flows and the rate paid to finance negative cash flows can differ.
MIRR(flows-range, finance-rate, reinvest-rate)
flows-range: A collection that contains the cash flow values. flows-range must contain number values. Income (a cash inflow) is specified as a positive number, and an expenditure (a cash outflow) is specified as a negative number. There must be at least one positive and one negative value included within the collection. Cash flows must be specified in chronological order and equally spaced in time (for example, each month). If a period does not have a cash flow, use 0 for that period.
finance-rate: A number value representing the interest rate paid on negative cash flows (outflows). finance-rate is 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 flows) can be financed. For example, a company’s cost of capital might be used.
reinvest-rate: A number value representing the interest rate at which positive cash flows (inflows) can be reinvested. reinvest-rate is entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%). For example, a company’s short-term investment rate might be used.
Example |
|---|
Suppose 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 first and second years, respectively. Assume you place these cash outflows, as negative numbers, in cells B2 through D2. In the third year the partnership expects to be self-funding but not return any cash to investors (0 in E2). In the fourth and fifth years, investors are projected to receive $10,000 and $30,000, respectively (as positive numbers in F2 and G2). At the end of the sixth year, the company expects to sell and investors are projected to receive $100,000 (as a positive number in H2). flows-range is B2:H2. Assume that you can currently borrow money at 9.00% (finance-rate) and can earn 4.25% on short-term savings (reinvest-rate). =MIRR(B2:H2, 0.09, 0.0425) returns 9.74693238643805%, the compound annual interest rate earned (the internal rate of return) assuming all cash flows happen as scheduled, given the differing borrowing and investment rates. |