Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

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

The FVSCHEDULE function can be used to convert a series of monthly returns into an annual return. Figure 12-20 shows the monthly returns for a mutual fund.

Figure 12-20: Monthly returns for a mutual fund.

On the CD 

The example in this section can be found on the companion CD-ROM in a workbook named  FVSCHEDULE.xlsx.

For the year, this fund returned 37.83%. The formula to calculate the annual return is

=FVSCHEDULE(1,B5:B16)-1

A principal of 1 is used because I'm interested only in the rate of the return, not the actual balance of the mutual fund. The principal is subtracted from the end, so the result is the increase for only the year.

Caution 

Note that the FVSCHEDULE function does not follow the sign convention. It returns a future value with the same sign as the present value.

Related

Категории