Using XIRR to design a contract
Typically, people use XIRR or
IRR to obtain the rate of return for cash flows. This problem uses XIRR to obtain cash flows
that provide the desired rate of return.
Question Two: A person invests $1,000 and receives $100
per month until she obtains an IRR on the investment of 10%. How many
monthly payments of exactly $100 will lead to an IRR of 10 percent?
Comment:
This is a trick question
because there is no way to get close to 10 percent by giving monthly
payments of $100. This leads to a follow up question on how the cash flows
might be modified to provide a return near 10 percent.
Analysis:
I solve the problem with
XIRR.
Steps:
Step one place observation
number, date and cash flows columns A, B and C respectively. It looks like this.
A

B

C

1

1/1/00

1000

2

2/1/00

100

3

3/1/00

100

4

4/1/00

100

5

5/1/00

100

6

6/1/00

100

7

7/1/00

100

8

8/1/00

100

9

9/1/00

100

10

10/1/00

100

11

11/1/00

100

12

12/1/00

100

Step two use the XIRR function
to calculate the return of the cash flow over 11 periods, 12 periods or more if
necessary to obtain a 10 percent return.
I have formatted the cell with the XIRR function as a percentage. This is what I get.
Number of periods
Including initial $1,000 investment

Result of XIRR
function

11

0.00%

12

21.31%

When there are 10 payments of
$100 the payment received by the investor is equal to $1,000 the initial
investment and the IRR is exactly 0%.
Note that the IRR would also
be 0 percent if the person returned $1,000 11 months after the initial
investment.
The investor is better off by
getting $100 a month because money is returned earlier.
What is the return if the
investor makes one more payment of $100?
The return from an initial
investment of $1,000 followed by 11 monthly payments to the investor of $100 is
21.3%.
Follow up question: We desire to design a contract
where the rate or return is very close to 10.0 percent. What final payment in the 12^{th}
month gets us close to an IRR of 10.0%.
What final payment in the 12
month would have led to an exact return of 10 percent?
I determine this number by
trial and error. My first guess of $50
is close?
1

1/1/00

1000

2

2/1/00

100

3

3/1/00

100

4

4/1/00

100

5

5/1/00

100

6

6/1/00

100

7

7/1/00

100

8

8/1/00

100

9

9/1/00

100

10

10/1/00

100

11

11/1/00

100

12

12/1/00

50

11

0.00%


12

10.77%

I will leave it to the reader
to find the exact dollar for the last payment.
No comments:
Post a Comment