Using the XIRR Function

Using the XIRR Function

The XIRR function calculates the internal rate of return for an investment given its cash flows, the dates of

those cash flows, and, optionally, an initial guess as to the MBT Panda, internal rate of return. The function uses the

following syntax:

XIRR (values, dates, guess)

For example, suppose that you want to calculate the internal rate of return for an investment that produces the

following cash flows on the following dates:

1/1/2000 -1000

12/31/2000 -1000

4/15/2001 2000

12/31/2001 1000

To calculate the internal rate of return for this set of cash flows using the XIRR function and using a

starting guess of 20%, you would use the following formula:

=XIRR({-1000,-1000,2000,1000},{"1/1/2000","12/31/2000","4/15/2001","12/ 31/2001"},.2)

The formula returns the value .470251, which is equivalent to 47.0251% annually.If the date values were stored

in the worksheet range A1:A4 and the cash flow values were stored in the worksheet range B1:B4, you could

instead use the following formula:

=XIRR (B1:B4,A1:A4,.2)

Using the XNPV Function

The XNPV function calculates the net present value for an investment given its cash flows, the dates of those

cash flows, and the annual discount rate. The function uses the following syntax:

XNPV (rate, values, dates)

For example, suppose that you want to calculate the net present value for an investment that produces the

following cash flows on the following dates:

Investing in bonds? Analyzing related fixed-income investments? You'll want to know this bit of trivia: Excel

provides six functions that let you make bond coupon date calculations more easily:

COUPDAYBS,

COUPDAYS,

COUPDAYSNC,

COUPNCD,

COUPNUM, and

COUPPCD.

Some Background Info on the Bond Coupon Date Functions

All six coupon date functions use four standard arguments: the MBT Sirima, the maturity date, the

frequency, and the basis.