## Sunday, April 9, 2017

### ETF Returns for Non-periodic Transaction Dates

ETF Returns for Non-periodic 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 non-periodic.  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 10-year 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 end-of-period 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.

 Non-Periodic 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 paired-difference 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.