Wednesday, August 30, 2017

Use of XIRR and XNPV

Question Seven:  Below are a list of purchases and disbursements of shares of VOE Vanguard Value Mid-Cap fund along with the date of each transaction.   What is the internal rate of return from the investment in VOE?

What is the net present value of these cash flows when the cost of capital is 10 percent?   Solve this problem using XNPV and by creating a spreadsheet with the discount factors for each cash flow.   Show the two methods provide identical results.


Purchases and Disbursement of VOE
Purchases
Date
Shares
Price
Amount
10/1/06
2000
43.9
87800
4/1/09
3000
28.1
84300
8/1/10
1700
38.5
65450
Disbursements
Date
Shares
Price
Amount
6/1/07
300
49.6
14880
10/1/13
2800
72
201600
5/1/16
3600
87
313200


Analysis:   Create a chart with the cash flows and the date keeping in mind that purchase/sale of stock are negative/positive cash flows to the investor.  After setting up the date in cash flows in columns use the XIRR and XNPV functions.


Date
Amount
10/1/06
-$87,800.00
4/1/09
-$84,300.00
8/1/10
-$65,450.00
6/1/07
$14,880.00
10/1/13
$201,600.00
5/1/16
$313,200.00
XIRR
13.5%
XNPV
$43,311.86


The syntax for the XIRR function is XIRR(value range,date range)

The syntax for the XNPV function is XNPV(rate, value range, date range)

We get $43,311.85 for the net present value of the cash flow.



The net present value of the cash flows can be calculated without the use of the XNPV function by discounting each cash flow back to the first period and then adding the discounted cash flows.  

The discount factors are 1/(1.10)t

Where t is the number of years from the initial investment date, which is DIFF DAYS divided by 365.  (Get DIFF DAY by taking the difference between initial date and current date.)




Calculation of  Net Present Value of Cash flow
Date
Cash Flow
Diff Days
Discount  Rate
10/1/06
-$87,800
0
1.0000
4/1/09
-$84,300
913
0.7879
8/1/10
-$65,450
1400
0.6938
6/1/07
$14,880
243
0.9385
10/1/13
$201,600
2557
0.5129
5/1/16
$313,200
3500
0.4009


The SUMPRODUCT of the cash flows and the discount rates gives sum of the discounted cash flows

We get $43,311.85=SUMPRODUCT(array1, array2) where array1 and array2 are the cash flow array and the discount factor array.

My spreadsheet gives the same answer as the XNPV function.


Authors Note:  Go to the following spreadsheet for more Excel Finance Function questions.

Excel Finance Problems:




No comments:

Post a Comment