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.










Wednesday, March 15, 2017

Student Debt and The Rise of Private For-Profit Schools


Student Debt and The Rise of Private For-Profit Schools

Question One:  The table below contains information on share of students in three types of institutions – public universities, private non-profit universities, and private for-profit universities for both 2004 and 2012. 

The table also contains information on the percent of students taking out federal loans in 2012 and the average loan balance per student in 2012.


Note that the share of students in both public universities and private non-for profit schools fell and the share of students in private for-profit schools rose.  The increased share of students in for-profit schools is of concern because around 70 percent of students in this sector take out federal student loans.

Based on this information:

What is the best estimate of total loans in dollars for each institution type for 10,000 students representative of all three sectors?

What is the best estimate in total loans in dollars for 10,000 students at each institution type if the share of students across institution types were at their 2004 levels and lending patterns were at 2012 levels?

How did the increase in the share of students attending private for-profit schools impact the issuance of total federal student loans?


Institution Type and Federal Student Loans
Institution Share 2004
Institution Share 2012
% of Students taking out federal   loan in 2012
Average   federal Loan in $ 2012
  Public
76.96%
73.42%
30.96%
$6,012
  Private not-for-profit
14.99%
13.05%
60.00%
$7,095
  Private for-profit
8.05%
13.53%
71.14%
$7,030
The data was obtained from the 2004 and 2012 NPSAS databases accessed from https://nces.ed.gov/datalab/powerstats/default.aspx


Analysis:


10,000 students multiplied by share of institution at each institution is the number of students out of 10,000 students going to each type of institution.  Multiply number of students at each institution type by percent of students taking out loans to get number of borrowers (from 10,000 students) at each institution type.  Multiply total number of borrowers at each institution type by average federal loan per borrower to get total amount borrowed at each institution type for 10,000 students.

Total borrowed for 10,000 students is calculated using 2012 and 2004 institution shares below.



Impact of Change in Institution Share on Loans In $
Total Loans In Dollars 2012 for 10000 Students
Total Loans In Dollars in 2012 for 10,000 Students at 2004 Institution Shares
Change in Dollars (2012 - 2012 at 2004 Institution Share)
Public
$13,668,740
$14,327,789
-$659,048
Private not-for-profit
$5,555,560
$6,381,444
-$825,884
Private for-profit
$6,766,687
$4,026,004
$2,740,683
Total
$25,990,987
$24,735,236
$1,255,751

Observations:

The increase in federal student debt incurred from 10,000 students due to the increased share of students in private for-profit schools is around 2.7 million, which is 10.5% of total debt for 10,000 students.   After netting out the decrease in public school and private non-profit student I get 4.8 percent of the total.

A more apt comparison involves the change in debt due to increase share of students attending for-profit schools to total change in debt.  Again the analysis is based on 10,000 students.  I found that 10,000 students in 2004 would have accumulated around 14.5 million in debt, around $10 million less than debt incurred by 10,000 students in 2012.  After accounting for decreased share of public and private not-for profit schools it appears as though the shift toward private for profit schools is responsible for around 12 percent of the increase in total student debt.


Concluding Thoughts:  The for-profit sector represents around 13 percent of students in 2012 up from 8 percent in 2004.   The shift towards this sector increased federal student debt by around 12 percent between 2004 and 2012.

People interested in a broader overview of changes in three forms of student debt between 2004 and 2012 should read the following post.

Student Debt Overview: