Back in December 2014 I wrote a post called “Solve For Last Payment Due“. The idea being that if you wanted to earn a certain return, you could easily calculate with a formula, the final payment required for you to earn that return on the last payment date. That seemed to me to be a useful calculator to have. What I did not mention was that that same formula can be used to change any of the cash flows you expect to get, in order to earn the required internal rate of return.
The reason I did not mention it was that I could not think of a scenario where changing one of the cash flows (with the exception of the last one) to earn a certain return might be useful. I came up with one, just so I could show how it works. Suppose your brother-in-law wants to borrow $30,000 to get a business started. He wants to pay it back over 5 years. Due to the risk involved with this type of loan, you want a return of 10%, but until the business starts making a profit, he cannot pay enough each month to pay back the loan. You work out a payment schedule that starts at $400 a month and increases $25 at the start of each new year. That is still not enough to pay of the loan and give you the 10% interest.
Your brother-in-law however, is expecting a relatively large payment from an annuity in January of 2017, so he can make a large payment that month. If you keep the payment schedule you established, how much does he have to pay you on 1/15/2017? The answer is $9,487.15. The calculator looks like this (not showing the total cash flows):
The first thing that is obvious is that the internal rate of return with the scheduled payments, without an extra large payment, is a -2.4%. All you have to do is input the cash flow date (yellow cells) to get your answer:
Now, copy/paste the $9,487.15 into the 1/15/2017 cash flow as below and the XIRR matches the required 10%.
Here are the named ranges I used to make the calculator dynamic and the formula:
Your brother-in-law agrees to the schedule, but asks for the due date on January 2017 be moved to the end of the month (1/31/2017 from 1/15/2017). Just change the date in the schedule to 1/31/2017 and put a zero in the cash flow cell like below:
Change the new date to 1/31/2017. Since there is a zero payment for 1/31/2017 the new date of 1/31/2017 gives a new amount you need of $9,587.69:
Now copy/paste the $9,587.69 into the cash flow cell as below and you will again see the 10% match:
Download spreadsheet “Any”