Final PMT in a Series that results in a Specific XIRR??

How much of this is a math question, and how much is a programming question? If you already know the math, please share what you know so we can help with the programming.

I tend to start this sort of thing as a math question first. The math does not seem that difficult, so I am going to assume that once you understand the math, that programming this will be fairly straightforward.

XNPV=SUM(For i=1 to n) of Pi/(1+r)^((di-d1)/365) <- from the XNPV help file. Given a rate, a series of cash flows, and a corresponding series of dates, we can calculate the XNPV(), and this is fairly straightforward.

XIRR uses the same formula, except that it numerically searches for the rate that makes XNPV=0. Written out 0=SUM[(For i=1 to n) of Pi/(1+r)^((di-d1)/365)].
Rearrange to see the nth payment separate from the others:
0=SUM[(For i=1 to (n-1)) of Pi/(1+r)^((di-d1)/365)] + Pn/(1+r)^((dn-d1)/365)
It should be apparent to someone skilled in business math (like yourself) that the first SUM[] term is simply the XNPV of all payments except the last payment (at the target rate), so we can substitute:
0=XNPV(all but last payment) + Pn/(1+r)^((dn-d1)/365)
At this point, given a target rate and the date for the last payment, it should be simple algebra to solve for the final payment Pn.

Can we assume that your algebra skills are up to this task from here?

	A	B	C
2	1/1/2018	$ (200.00)
3	2/1/2018	$ 10.00
4	4/1/2018	$ 50.00
5	6/1/2018	$ 146.92	B5: =-XNPV(10%, B2:B4, A2:A4)*(1+10%)^((A5-A2)/365)
6
7	Check:
8	10.00%		A7: =XIRR(B2:B5, A2:A5)

Well, I guess I spoke too soon. When working with my real numbers, the XNPV formula returns a #NUM! error that prevents the formula from working.

But, as I mentioned earlier, the XNPV formula seems to get close to the right answer, but if you're working in millions or tens of millions, it's way off.

Can you supply an example? I tried the data from the XNPV() help file (https://support.office.com/en-us/art...b-d67c16b664b7 ), multiplied by 1E4, and everything still seemed to work just fine. I don't think the size of the numbers should matter.

You need to display a little more precision in the XIRR result. $200K as the terminal payment is an IRR of ~ 15.9858%, not 16%. The result returned by the formula is correct.

I'm not sure what you mean. Your file says that the 200000 in F2 is not equal to the 200447 computed by the formula given above. I am not sure what you are seeing here, but I am seeing rounding errors. With 200000 in F2, the 16.0% in G2, if you expand the number of decimal places in the number format, is more like 15.9598....%, not exactly 16.0000000000%. If you put the 200447 computed by our formula into F2, then you will find that the test XIRR is much closer to 16.000000000...% (I get 15.99996...%). If you expand the 200447 and enter the value to its full precision (or copy->paste special -> as values) you should see that the XIRR computed in G2 is essentially 16.0% -- to match the 16% entered into A2.

I could be misunderstanding, but, if I am understanding, this looks like a case where you are not looking at the computed numbers to their full precision.

What he said.