Thursday, August 15, 2019

Using XIRR to design a contract


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 12th 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