Question Seven: Below are a list of purchases and disbursements
of shares of VOE Vanguard Value MidCap 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