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

investment.

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.

Calculating depreciation

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.