Sunday, August 20, 2017

Investment Returns from 60 deposits and 36 disbursements

This Post looks at XIRR:

Question Six:   A person invests $1,000 per month in investment fund VFIAX starting on 1/2/02 for sixty months.   After the sixty month period the person sells (1/36) of the shares in the account at the date of the last deposit.  What is the internal rate of return from this investment.

Bonus Problem:   A different person invests starts the same process of sixty monthly contributions in VFIAX starting in 1/02/2003  followed by 36 monthly disbursements where 1/36 of shares at the date of the last deposit are sold each month.  Collect data and set up the spread sheet to calculate the internal rate of return for this second investor.



What are returns for the second investor?   Comment on risk of this investment over these holding periods?


Analysis:   The spreadsheet used to generate the cashflow from the investments and disbursement from fund VFIAX in this time period are presented in the table below.

The return from the investment can be calculated using the XIRR function.  The XIRR function requires two inputs -- (1) the entire range of cash flows (column C) and (2) the dates corresponding to each cash flow (column A).

XIRR(C1:C96,A1:A96) = 

0.120457667

Around 12.0%.   

Extra Credit:  Place 0.120457667 into XNPV for these cash flows.   Observe the answer is 0 or very close due to rounding.  I got -0.0004.

An Important Note:   This problem involves a contribution pattern that started in January of 2002 and a disbursement pattern that started in January of 2007.   Results are much less rosy for the the investor who starts contributions in January 2003 and starts disbursements in January 2008.

Answer to Bonus Problem Here:
http://www.dailymathproblem.com/2017/04/measuring-returns-for-different.html


The Tutorial on Excel Finance Functions
http://www.dailymathproblem.com/p/excel-finance-functions.html

Cash Flows from this investment and disbursement pattern






DATE
PVFIAX
Cash Flow
Shares Purchased
A
B
C  
D
1
1/2/02
24.9
-1000
40.1
2
2/1/02
23.5
-1000
42.5
3
3/1/02
23.8
-1000
42
4
4/1/02
21.6
-1000
46.4
5
5/1/02
21.5
-1000
46.4
6
6/3/02
19.2
-1000
52.1
7
7/1/02
17.5
-1000
57.1
8
8/1/02
18
-1000
55.5
9
9/3/02
16.5
-1000
60.6
10
10/1/02
17.4
-1000
57.3
11
11/1/02
18.6
-1000
53.7
12
12/2/02
18.4
-1000
54.3
13
1/2/03
17.9
-1000
55.9
14
2/3/03
17
-1000
58.7
15
3/3/03
17.6
-1000
57
16
4/1/03
19.5
-1000
51.4
17
5/1/03
21.3
-1000
46.9
18
6/2/03
21.7
-1000
46.1
19
7/1/03
20.6
-1000
48.6
20
8/1/03
20.8
-1000
48.2
21
9/2/03
20.8
-1000
48.1
22
10/1/03
22.2
-1000
44.9
23
11/3/03
22
-1000
45.4
24
12/1/03
23.3
-1000
43
25
1/2/04
23.5
-1000
42.6
26
2/2/04
24
-1000
41.7
27
3/1/04
23.7
-1000
42.1
28
4/1/04
23.8
-1000
42
29
5/3/04
23.7
-1000
42.2
30
6/1/04
24.4
-1000
41
31
7/1/04
24.7
-1000
40.5
32
8/2/04
25.1
-1000
39.8
33
9/1/04
25.9
-1000
38.6
34
10/1/04
27.2
-1000
36.8
35
11/1/04
28.2
-1000
35.5
36
12/1/04
28.9
-1000
34.6
37
1/3/05
28.2
-1000
35.4
38
2/1/05
28.8
-1000
34.8
39
3/1/05
28.7
-1000
34.8
40
4/1/05
29
-1000
34.5
41
5/2/05
29.4
-1000
34
42
6/1/05
30.6
-1000
32.7
43
7/1/05
31.6
-1000
31.6
44
8/1/05
31.8
-1000
31.5
45
9/1/05
32.3
-1000
30.9
46
10/3/05
31.1
-1000
32.2
47
11/1/05
31.9
-1000
31.3
48
12/1/05
31.6
-1000
31.6
49
1/3/06
32.9
-1000
30.4
50
2/1/06
34.1
-1000
29.3
51
3/1/06
34.3
-1000
29.2
52
4/3/06
34.1
-1000
29.3
53
5/1/06
33.8
-1000
29.5
54
6/1/06
35.1
-1000
28.5
55
7/3/06
36.5
-1000
27.4
56
8/1/06
37.2
-1000
26.9
57
9/1/06
37.7
-1000
26.5
58
10/2/06
39.4
-1000
25.4
59
11/1/06
40.8
-1000
24.5
60
12/1/06
41.1
-1000
24.3
61
1/3/07
41.2
2751.5
2406
62
2/1/07
43.3
2891.4
63
3/1/07
45.2
3021.4
64
4/2/07
47.1
3144.6
65
5/1/07
47.9
3199.8
66
6/1/07
45.5
3039.2
67
7/2/07
43.2
2888.9
68
8/1/07
43.9
2931.7
69
9/4/07
45.7
3052.1
70
10/1/07
49.1
3283.4
71
11/1/07
48.5
3241.1
72
12/3/07
48.6
3248.3
73
1/2/08
44.9
3003.7
74
2/1/08
43.2
2885.4
75
3/3/08
43.4
2897.5
76
4/1/08
45.9
3070.4
77
5/1/08
47.4
3166.9
78
6/2/08
47.1
3145.2
79
7/1/08
42.8
2859.6
80
8/1/08
41.1
2749.4
81
9/2/08
34.8
2322.9
82
10/1/08
30.5
2036.8
83
11/3/08
31.8
2123.7
84
12/1/08
31.1
2080.1
85
1/2/09
30.8
2061.5
86
2/2/09
27
1805.2
87
3/2/09
27.5
1834.7
88
4/1/09
28.1
1876.6
89
5/1/09
29.4
1964.9
90
6/1/09
30.8
2056.8
91
7/1/09
32.5
2172.1
92
8/3/09
33.4
2231.8
93
9/1/09
33.7
2252.1
94
10/1/09
32.2
2153.9
95
11/2/09
33.5
2239.6
96
12/1/09
35.6
2379.4

No comments:

Post a Comment