Payment Required to Earn “X” IRR

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):

Any1

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:

Any2

Now, copy/paste the $9,487.15 into the 1/15/2017 cash flow as below and the XIRR matches the required 10%.

Any5

Here are the named ranges I used to make the calculator dynamic and the formula:

Any4Any3

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:

Any6

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:

Any7

Now copy/paste the $9,587.69 into the cash flow cell as below and you will again see the 10% match:

Any8

Download spreadsheet “Any

 

ooo

 

 

One thought on “Payment Required to Earn “X” IRR

  1. Pingback: Any Payment Required, Revisited | Excel@CFO

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress spam blocked by CleanTalk.