I want to revisit the post I wrote earlier in the month called “Payment Required to Earn “X” IRR”. The reason I want to revisit it is because when I said any cash flow can be changed, I meant any . I think this spreadsheet calculator can come in handy in a number of ways.
Lets say your firm is looking at an investment with an initial investment of $3 million on 9/15/2015. The investment policy requires a return on assets of 3.0% for this particular investment risk and the cost of borrowing is 5.0%. Therefore we require an APY (Annual Percentage Rate) of 8.0%.
So we enter the required APY of 8.0% in the yellow input cell like below. The initial internal rate of return for this one year project is 4.59%, so we will need to make an adjustment in expected cash flows in order for this investment to satisfy the 8.0% return:
1. One option is to decrease the initial investment. To do this we simply enter the initial investment date, like below. The return calculation shows we would need to reduce the initial investment by $69,383.73, to $2,930,616.27. Copy/paste the new amount into the initial cash flow cell and are IRR becomes 8.0%:
2. If a lower initial investment will not work for the borrower, we could try and change the amount of one of the cash flows on the scheduled dates. So we enter the date 6/1/16, and we find that if the cash flow on 6/1/16 was increased $73,293.65 to $573,293.65, we would again get our 8.0%:
3. Another option might be to skip the $200,000 payment on 5/31/16 and increase the 4/30/16 payment to $471,497.88:
4. We could ignore the scheduled payments and add a new payment and a new date. It doesn’t matter where we add the new date and payment so we will add both at the bottom of the cash flows:
5. Lets try another change. We can leave the original cash flows, but require an additional payment, one year from the last scheduled payment:
6. One more. How much would you have to give the borrower if you gave half ($1,500,000 on the initial date of 9/15/15 and enough on 4/1/16 to give you an 8.0% return? Again, the date and amount can be entered at the end of the cash flow. The answer is that you would have to invest only $1,491,921.5 on 4/1/16.
There can be problems when using the XIRR() function as a proof if the cash flows are non-conventional. A non-conventional cash flow is a cash flow that has signs that change more than once. This means two or more negative and two or more positive signs. You could get multiple answers, depending upon how many times the signs change. Excel XIRR() asks for a ‘Guess” so that it has a much better chance of coming back with the correct answer, if there is more than one possible answer. I have linked the guess variable to the APY required cell, so this should take care of potentially incorrect answers.
There are many more combinations and other options, but this should give some ideas as to how flexible this tool can be for capital budgeting, investment analysis, project analysis, or any other analysis with a required return.
Spreadsheet written in Excel 2013