## 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.