Tracking the IRR of Periodic Investments

Tracking the IRR of Periodic Investments

Question: A person started periodic investments in Vanguard growth fund (VUG) on 1/1/2015. One thousand dollars is invested every six months.

The stock price on the investment date and on 6/1/2018 are presented in a table below.

What is the value of the fund on each date?

What is the IRR of all investments in the fund on each date?

Why is difficult to track the IRR of sporadic and periodic investments using IRR or XIRR in Excel?   How might these functions be modified to be more useful and user friendly? 

Stock Price of Vanguard Growth Fund
Date	VUG
1/1/15	98.6
7/1/15	106.6
1/1/16	97.2
7/1/16	109.8
1/1/17	113.7
7/1/17	129.1
1/1/18	149.9
6/1/18	153.7

this is question 19 in the Excel finance tutorial.
http://www.dailymathproblem.com/p/excel-finance-functions.html

Analysis:   The value of the investment in the VUG fund is calculated by first obtaining total shares at each date and then by multiplying total shares by share price.   This calculation is presented in the table below.

Value of Investments in Vanguard Growth Fund
Date	VUG	Shares Purchased	Shares Owned	Value of Account
1/1/15	98.6	10.14	10.14	$1,000
7/1/15	106.6	9.38	19.52	$2,081
1/1/16	97.2	10.29	29.81	$2,897
7/1/16	109.8	9.11	38.92	$4,274
1/1/17	113.7	8.79	47.71	$5,426
7/1/17	129.1	7.75	55.46	$7,161
1/1/18	149.9	6.67	62.13	$9,312
6/1/18	153.7	0	68.64	$9,551

The IRR at each contribution date is obtained from the XIRR function.   The cash flows are -1,000 for all contribution dates prior to the current date and -1000 plus account value on the last contribution date. 

The date 6/1/2018 is not a contribution date.  For this date, the last cash flow is simply the value of the account on 6/1/2018.

The IRR is calculated with the XIRR function.  The syntax of the XIRR function is XIRR(values, dates).    The values and date inputs are a range of cells. All cash flow figures and all corresponding dates must be contiguous to be placed in the XIRR function.  This limitations makes it awkward to use Excel to continuously track the IRR from periodic or sporadic investments.

The IRR calculations for each date are laid out below. 

IRR calculations for periodic investments
1/1/15	-1000.0
7/1/15	1081.3
XIRR	17.1%
1/1/15	-1000.0
7/1/15	-1000.0
1/1/16	1896.7
XIRR	-6.8%
1/1/15	-1000.0
7/1/15	-1000.0
1/1/16	-1000.0
7/1/16	3273.7
XIRR	9.1%
1/1/15	-1000.0
7/1/15	-1000.0
1/1/16	-1000.0
7/1/16	-1000.0
1/1/17	4426.3
XIRR	8.3%
1/1/15	-1000.0
7/1/15	-1000.0
1/1/16	-1000.0
7/1/16	-1000.0
1/1/17	-1000.0
7/1/17	6160.6
XIRR	14.6%
1/1/15	-1000.0
7/1/15	-1000.0
1/1/16	-1000.0
7/1/16	-1000.0
1/1/17	-1000.0
7/1/17	-1000.0
1/1/18	8312.5
XIRR	19.6%
1/1/15	-1000.0
7/1/15	-1000.0
1/1/16	-1000.0
7/1/16	-1000.0
1/1/17	-1000.0
7/1/17	-1000.0
1/1/18	-1000.0
6/1/18	8550.9
XIRR	10.7%

The summary of the IRR calculations is presented below.

Summary of IRR Calculations
7/1/15	17.1%
1/1/16	-6.8%
7/1/16	9.1%
1/1/17	8.3%
7/1/17	14.6%
1/1/18	19.6%
6/1/18	10.7%

Discussion of IRR Results for Periodic Investments:

The IRR calculations depend critically on the last stock price because the current stock price determines the value of the entire fund.  An investor that has great returns for years could become quite poor if the market drops when she needs the money.

It is crucial to reallocate assets by moving some assets from the growth fund to safer investments when the price of the growth fund is high.

Discussion of limitations of the XIRR function.

The XIRR function accepts one range for all cash flows and one range for all dates corresponding to each cash flow.  The initial spreadsheet with the investment and value data does not neatly correspond to the inputs of the XIRR function.   I needed to move the value of the fund at valuation date next to expenditures to make a single cash flow range. 

It is possible to rearrange the table by creating a separate column or row for each calculation.   The spreadsheet would get quite larger if the goal was to track the IRR of a 401(K) plan on a daily or weekly basis.  

A spreadsheet that tracks IRR of an asset reallocation strategy – where amount invested in several funds varies over time – would also be quite difficult using the IRR function.

It would be useful if the XIRR function was modified to allow for multiple non-contiguous cash flows and corresponding dates.   This would be a great and possibly profitable project for a software developer.

Authors Note:  I hope to write several posts on asset allocation strategy during the next few months.  

I spent several months writing a book on student debt.   The book documents the magnitude of increased college costs and discusses affordable pragmatic policies, which could improve the current situation.   The book is available on kindle and in paperback through Amazon.

The paperback on Amazon:

 https://www.amazon.com/Innovative-Solutions-College-Debt-Problem/dp/1982999446/ref=tmm_pap_swatch_0?_encoding=UTF8&qid=1527884719&sr=8-1&dpID=51dr8szSTNL&preST=_SX218_BO1,204,203,200_QL40_&dpSrc=detail

The Kindle Version:

https://www.amazon.com/Innovative-Solutions-College-Debt-Problem-ebook/dp/B07D9VV8K7/ref=tmm_kin_swatch_0?_encoding=UTF8&qid=1527884719&sr=8-1