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
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:
The Kindle
Version:
No comments:
Post a Comment