Microsoft Office Tutorials and References
In Depth Information
The MIRR function
Suppose you agree to buy an income property for $350,000 and rent it. Over the next ten
years, you expect to receive net rental income starting at $40,000 the first year,
increasing by $1,000 per year. You can set up a simple worksheet that contains your investment
and income information. Type the 11 values, starting with the initial investment amount,
in cells A1:A11 on the worksheet. (Be sure to type the initial $350,000 investment in cell A1
as a negative value.) Then the formula =IRR(A1:A11) returns the internal rate of return of
4.46 percent. If the hurdle rate is 3.5 percent, you can consider this property to be a good
The MIRR function
The MIRR function calculates the modified internal rate of return of an investment. The
difference from the IRR function is that MIRR takes into account the cost of the money you
borrow to finance the investment. MIRR assumes that you’ll reinvest the cash the
investment generates and that transactions occur at the end of a period. It then returns the
equivalent interest rate for that period’s length.
The MIRR function takes the arguments values , finance rate , and reinvestment rate . (For
definitions of these arguments, see Table 16-1.) The values argument must be an array or
a reference to a range of cells that contain numbers. This argument represents a series of
payments and income occurring at regular periods. You must include at least one positive
value and one negative value in the values argument.
Suppose you borrow $120,000 at 3 percent interest to acquire an investment that will
return increasing amounts of income over five years. If cells A1 through A6 contain the
values –120000, 22000, 24000, 28000, 31000, and 33000, representing the initial investment
(as a negative value) and the subsequent cash inflows from that investment, the formula
=MIRR(A1:A6, 3%, 2%) returns a modified internal rate of return of 4 percent.
Depreciation has an enormous effect on the bottom line of any business, and accurately
calculating depreciation is crucial if you want to avoid triggering a detailed scrutiny of your
financial records. These functions help you precisely determine the depreciation of an asset
for a specific period. Table 16-2 lists the common arguments used in these functions. All of
the functions for calculating depreciation are available on the Formulas tab, in the Function
Library group’s Financial drop-down list.