Microsoft Office Tutorials and References

In Depth Information

**Using the FVSCHEDULE Function**

Figure 12-19:
The XIRR function works with irregular cash flows.

The formula in B15 is

=XIRR(B4:B13,A4:A13)

The XIRR function has the same problem with multiple rates of return as IRR. It expects

that the cash flow changes signs only once: that is, goes from negative to positive or

from positive to negative. If the sign changes more than once, it is essential that you

plug the XIRR result back into an XNPV function to verify that it returns zero. Figure

12-19 shows such a verification although the sign only changes once in that example.

Using the FVSCHEDULE Function

The FVSCHEDULE function calculates the future value of an initial amount, after applying a series

of varying rates over time. Its syntax is

FVSCHEDULE(principal,schedule)

Calculating an annual return

You can use the FVSCHEDULE function to convert a series of monthly returns into an annual

return. Figure 12-20 shows the monthly returns for a mutual fund.

You can find the example in this section on the companion CD-ROM in a workbook

named
fvschedule.xlsx
.