ETF Returns for
Nonperiodic Transaction Dates
Question: A person invests $1,000 per month for 12
consecutive months starting on the first market day of April 2007 in
Vanguard Small Cap ETF (VB). The investor starts selling the VB shares on
the first market day of April 2016. One
twelfth of the shares are sold on the first market day of the month for 12
consecutive days.
What is the internal rate of return from the investment and
sale of VB?
What is the NPV of the investment for a person with a 5.0 percent
cost of capital?
Discussion of Problem: The
typical IRR or NPV problem presented in introductory finance courses involves
periodic cash flow, typically one inflow or outflow per month or year. In many real word situations cash flows are
nonperiodic. In this case, the person
makes 12 share purchases leaves the funds untouched for a decade and then takes
out the funds in 12 equal installments.
Typically, mutual funds or ETFs
will present 1, 3, 5, or 10 year returns.
The published returns involve the investment of a lump sum with all
proceeds invested on one day and sold on another. These return statistics will vary
substantially over time especially when the market makes large moves.
Note, also that the ending period
date on fund return is often the same for the 1, 3, 5, and 10year
returns. This results in a market
outcome of one recent day having a large impact on all reported return
measures.
A return measure based on multiple
purchase and sale dates is more robust and more sensible than return statistics
based on a single purchase date and the most recent endofperiod value. It is also a more accurate reflection of the
experience of many investors who buy and sell shares on multiple days
throughout their life.
Methodology: The XIRR
function in Excel and the XNPV function in Excel allow for calculation of the
future value of a series of cash flows when dates are not periodic. These functions require the input of a date
value for each cash flow and the cash flow figure. The XIRR function may require an initial
guess of the IRR. The XNPV function will
require an assumption on interest rates.
Analysis: The cash flow
figures for the 12 monthly purchases of VB
and the 12 monthly sales of VB stocks are presented below.
NonPeriodic Cash Flow
Calculation for
Purchases and Sales of VB


Date

Price of VB

Cash outflow or inflow

Shares Purchased

Shares Sold

4/2/07

62.1

$1,000

16.1


5/1/07

64.9

$1,000

15.4


6/1/07

64.0

$1,000

15.6


7/2/07

60.0

$1,000

16.7


8/1/07

61.0

$1,000

16.4


9/4/07

62.4

$1,000

16.0


10/1/07

63.9

$1,000

15.6


11/1/07

59.7

$1,000

16.8


12/3/07

59.2

$1,000

16.9


1/2/08

55.5

$1,000

18.0


2/1/08

54.1

$1,000

18.5


3/3/08

53.7

$1,000

18.6


4/1/16

111.6

$1,866

16.7


5/2/16

113.7

$1,901

16.7


6/1/16

114.1

$1,908

16.7


7/1/16

119.8

$2,003

16.7


8/1/16

120.6

$2,017

16.7


9/1/16

121.1

$2,024

16.7


10/3/16

116.2

$1,942

16.7


11/1/16

126.1

$2,109

16.7


12/1/16

128.5

$2,149

16.7


1/3/17

130.5

$2,182

16.7


2/1/17

133.5

$2,232

16.7


3/1/17

133.3

$2,228

16.7

Comments on chart above:
The cash outflows are $1,000 per
month for 12 months.
Each month from 04/2007 to 03/2008
the investor buys $1,000/$PVB shares of stock were PVB is the price of VB.
In total 200.6 shares of VB are
purchased in this time period.
16.76 (200.6/12) shares are sold
per month in the 12 months starting at 4/12/2016.
The amount received each month is
16.7*$PVB.
The Internal Rate of Return, Net Present Value, and Future Value of
Cash Flow Calculations:
The cash flows are in the third
column of the table and the date of the cash flow are in the first column. Using these vectors as inputs in the XIRR
function gives us an internal rate of return of 8.3 percent.
Plugging in an assumed interest
rate of 5 percent and the cash flow and date vectors into the XNPV function
results in the net present value estimate of $3,730.
Concluding Thoughts: Mutual
funds and ETFs advertise rates of return based on lump sum investors. The published returns are not very useful
when evaluating for an investor that has multiple purchase and sale dates. This post demonstrates that XIRR and XNPV
can be used to provide meaningful estimates of asset return when an investor
has multiple sale and purchase dates.
In the near future I will write a
longer paper using this technique to estimate fund performance for multiple
funds over multiple time horizons.
Previous Posts on Investment Performance Measurement Issues:
I have written several other posts
on measuring investment returns. Here
are two of my favorites:
Investment Fund Performance:
This post considers two funds with 12 possible purchase dates in 2007
and 12 possible sale dates in 2009, a very bleak investment scenario. I estimate the 144 possible return outcomes
for the two funds and compare. My return
measure is final value over initial value.
I don’t normalize for the difference in holding period lengths because
this is a really complex calculation in STATA.
Also, since I conduct a paireddifference comparison between the two
funds I did not consider it essential to annualize return estimates.
The Timing of the Bull Market:
This post looks at how the timing of bull and bear markets over the
lifetime of a worker can alter financial returns and retirement security. The example is simple and designed to show
that all else equal it is better to start your career with a bear market and
end with a bull market than the other way around.