IRR vs XIRR

[SBRiii]

I understand XIRR handles irregular cash flows while IRR assumes periodic cash flows, but why would XIRR return 10.5%/yr. vs IRR's 10%/yr. on the periodic cash flow involving monthly repayment of a 10%/yr. loan over 10 yrs. Is there a way to upload a spreadsheet to w/ this question?

 

[Georgiboy]

You can use the XL2BB add in to display part of a worksheet, link below:
XL2BB - Excel Range to BBCode

 

[SBRiii]

Thanks, but when I get to the window showing Add-ins available & click OK, I get a msg. saying the file type is not supported in Protected View.

 

[Fluff]

Have a look here XL2BB Icons greyed out

 

[SBRiii]

I understand XIRR handles irregular cash flows while IRR assumes periodic cash flows, but why would XIRR return 10.5%/yr. vs IRR's 10%/yr. on the periodic cash flow involving monthly repayment of a 10%/yr. loan over 10 yrs. Is there a way to upload a spreadsheet to w/ this question?

Thanks, but when I get to the window showing Add-ins available & click OK, I get a msg. saying the file type is not supported in Protected View.

Now that I've installed it, how do I use it to post the spreadsheet demonstrating the original problem/question?

 

[SBRiii]

I understand XIRR handles irregular cash flows while IRR assumes periodic cash flows, but why would XIRR return 10.5%/yr. vs IRR's 10%/yr. on the periodic cash flow involving monthly repayment of a 10%/yr. loan over 10 yrs. Is there a way to upload a spreadsheet to w/ this question?

Here, hopefully, is the MiniSheet. Cols A, B, & C would be extended over the 10 yrs. or repayment of the loan.

IRR vs XIRR_ (003).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	IRR vs. XIRR on 10% loan repaid over 10 yrs.
2	Date approx.	Date exact	Monthly cash flow			XIRR	IRR
3	10/01/23	10/01/23	$100,000.00		Approx. date	10.467%	10.000%
4	10/31/23	11/01/23	-$1,321.51		Exact date	10.461%
5	11/30/23	12/01/23	-$1,321.51
6	12/31/23	01/01/24	-$1,321.51
7	01/30/24	02/01/24	-$1,321.51			May have something to do w/ compounding (XIRR apparently assumes compounding daily)
8	03/01/24	03/01/24	-$1,321.51			10.47%	10%/yr. compounded monthly
9	03/31/24	04/01/24	-$1,321.51			10.52%	10%/yr. compounded daily
10	05/01/24	05/01/24	-$1,321.51			However, these computed rates don't vary much between monthly & daily compounding and I understand that the Excel PMT function assumes monthly compounding
11	05/31/24	06/01/24	-$1,321.51
12	06/30/24	07/01/24	-$1,321.51
13	07/31/24	08/01/24	-$1,321.51
14	08/30/24	09/01/24	-$1,321.51
15	09/30/24	10/01/24	-$1,321.51
Monthly

Cell Formulas
Range	Formula
G3	G3	=IRR(C3:C200,0.01)*12
A4:A15	A4	=A3+(365.25/12)
B4:B15	B4	=DATE(IF(MONTH(B3)<12,YEAR(B3),YEAR(B3)+1),IF(MONTH(B3)<12,MONTH(B3)+1,1),1)
C4:C15	C4	=PMT(0.1/12,10*12,$C$3)
F3	F3	=XIRR(C3:C200,A3:A200,0.01)
F4	F4	=XIRR(C3:C200,B3:B200,0.01)
F8	F8	=((1+(0.1/12))^12)-1
F9	F9	=((1+(0.1/365))^365)-1

 

[SBRiii]

Here's another attempt w/ the Mini-Sheet - this time the entire range the IRR & XIRR calcs apply to. Column widths apparently default to the widest text so the format isn't quite right. Again, the main point is that XIRR & IRR return very different values for a simple, periodic cash flow. TIA.

IRR vs XIRR_ (003).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	IRR vs. XIRR on 10% loan repaid over 10 yrs.
2	Date approx.	Date exact	Monthly cash flow			XIRR	IRR
3	10/01/23	10/01/23	$100,000.00		Approx. date	10.467%	10.000%
4	10/31/23	11/01/23	-$1,321.51		Exact date	10.461%
5	11/30/23	12/01/23	-$1,321.51
6	12/31/23	01/01/24	-$1,321.51
7	01/30/24	02/01/24	-$1,321.51			May have something to do w/ compounding (XIRR apparently assumes compounding daily)
8	03/01/24	03/01/24	-$1,321.51			10.47%	10%/yr. compounded monthly
9	03/31/24	04/01/24	-$1,321.51			10.52%	10%/yr. compounded daily
10	05/01/24	05/01/24	-$1,321.51			However, these computed rates don't vary much between monthly & daily compounding and I understand that the Excel PMT function assumes monthly compounding
11	05/31/24	06/01/24	-$1,321.51
12	06/30/24	07/01/24	-$1,321.51
13	07/31/24	08/01/24	-$1,321.51
14	08/30/24	09/01/24	-$1,321.51
15	09/30/24	10/01/24	-$1,321.51
16	10/30/24	11/01/24	-$1,321.51
17	11/30/24	12/01/24	-$1,321.51
18	12/30/24	01/01/25	-$1,321.51
19	01/30/25	02/01/25	-$1,321.51
20	03/01/25	03/01/25	-$1,321.51
21	03/31/25	04/01/25	-$1,321.51
22	05/01/25	05/01/25	-$1,321.51
23	05/31/25	06/01/25	-$1,321.51
24	07/01/25	07/01/25	-$1,321.51
25	07/31/25	08/01/25	-$1,321.51
26	08/31/25	09/01/25	-$1,321.51
27	09/30/25	10/01/25	-$1,321.51
28	10/30/25	11/01/25	-$1,321.51
29	11/30/25	12/01/25	-$1,321.51
30	12/30/25	01/01/26	-$1,321.51
31	01/30/26	02/01/26	-$1,321.51
32	03/01/26	03/01/26	-$1,321.51
33	04/01/26	04/01/26	-$1,321.51
34	05/01/26	05/01/26	-$1,321.51
35	06/01/26	06/01/26	-$1,321.51
36	07/01/26	07/01/26	-$1,321.51
37	07/31/26	08/01/26	-$1,321.51
38	08/31/26	09/01/26	-$1,321.51
39	09/30/26	10/01/26	-$1,321.51
40	10/31/26	11/01/26	-$1,321.51
41	11/30/26	12/01/26	-$1,321.51
42	12/31/26	01/01/27	-$1,321.51
43	01/30/27	02/01/27	-$1,321.51
44	03/01/27	03/01/27	-$1,321.51
45	04/01/27	04/01/27	-$1,321.51
46	05/01/27	05/01/27	-$1,321.51
47	06/01/27	06/01/27	-$1,321.51
48	07/01/27	07/01/27	-$1,321.51
49	08/01/27	08/01/27	-$1,321.51
50	08/31/27	09/01/27	-$1,321.51
51	10/01/27	10/01/27	-$1,321.51
52	10/31/27	11/01/27	-$1,321.51
53	11/30/27	12/01/27	-$1,321.51
54	12/31/27	01/01/28	-$1,321.51
55	01/30/28	02/01/28	-$1,321.51
56	03/01/28	03/01/28	-$1,321.51
57	03/31/28	04/01/28	-$1,321.51
58	05/01/28	05/01/28	-$1,321.51
59	05/31/28	06/01/28	-$1,321.51
60	06/30/28	07/01/28	-$1,321.51
61	07/31/28	08/01/28	-$1,321.51
62	08/30/28	09/01/28	-$1,321.51
63	09/30/28	10/01/28	-$1,321.51
64	10/30/28	11/01/28	-$1,321.51
65	11/30/28	12/01/28	-$1,321.51
66	12/30/28	01/01/29	-$1,321.51
67	01/30/29	02/01/29	-$1,321.51
68	03/01/29	03/01/29	-$1,321.51
69	03/31/29	04/01/29	-$1,321.51
70	05/01/29	05/01/29	-$1,321.51
71	05/31/29	06/01/29	-$1,321.51
72	07/01/29	07/01/29	-$1,321.51
73	07/31/29	08/01/29	-$1,321.51
74	08/31/29	09/01/29	-$1,321.51
75	09/30/29	10/01/29	-$1,321.51
76	10/30/29	11/01/29	-$1,321.51
77	11/30/29	12/01/29	-$1,321.51
78	12/30/29	01/01/30	-$1,321.51
79	01/30/30	02/01/30	-$1,321.51
80	03/01/30	03/01/30	-$1,321.51
81	04/01/30	04/01/30	-$1,321.51
82	05/01/30	05/01/30	-$1,321.51
83	06/01/30	06/01/30	-$1,321.51
84	07/01/30	07/01/30	-$1,321.51
85	07/31/30	08/01/30	-$1,321.51
86	08/31/30	09/01/30	-$1,321.51
87	09/30/30	10/01/30	-$1,321.51
88	10/31/30	11/01/30	-$1,321.51
89	11/30/30	12/01/30	-$1,321.51
90	12/31/30	01/01/31	-$1,321.51
91	01/30/31	02/01/31	-$1,321.51
92	03/01/31	03/01/31	-$1,321.51
93	04/01/31	04/01/31	-$1,321.51
94	05/01/31	05/01/31	-$1,321.51
95	06/01/31	06/01/31	-$1,321.51
96	07/01/31	07/01/31	-$1,321.51
97	08/01/31	08/01/31	-$1,321.51
98	08/31/31	09/01/31	-$1,321.51
99	10/01/31	10/01/31	-$1,321.51
100	10/31/31	11/01/31	-$1,321.51
101	11/30/31	12/01/31	-$1,321.51
102	12/31/31	01/01/32	-$1,321.51
103	01/30/32	02/01/32	-$1,321.51
104	03/01/32	03/01/32	-$1,321.51
105	03/31/32	04/01/32	-$1,321.51
106	05/01/32	05/01/32	-$1,321.51
107	05/31/32	06/01/32	-$1,321.51
108	06/30/32	07/01/32	-$1,321.51
109	07/31/32	08/01/32	-$1,321.51
110	08/30/32	09/01/32	-$1,321.51
111	09/30/32	10/01/32	-$1,321.51
112	10/30/32	11/01/32	-$1,321.51
113	11/30/32	12/01/32	-$1,321.51
114	12/30/32	01/01/33	-$1,321.51
115	01/30/33	02/01/33	-$1,321.51
116	03/01/33	03/01/33	-$1,321.51
117	03/31/33	04/01/33	-$1,321.51
118	05/01/33	05/01/33	-$1,321.51
119	05/31/33	06/01/33	-$1,321.51
120	07/01/33	07/01/33	-$1,321.51
121	07/31/33	08/01/33	-$1,321.51
122	08/31/33	09/01/33	-$1,321.51
123	09/30/33	10/01/33	-$1,321.51
Monthly

Cell Formulas
Range	Formula
G3	G3	=IRR(C3:C200,0.01)*12
A4:A123	A4	=A3+(365.25/12)
B4:B123	B4	=DATE(IF(MONTH(B3)<12,YEAR(B3),YEAR(B3)+1),IF(MONTH(B3)<12,MONTH(B3)+1,1),1)
C4:C123	C4	=PMT(0.1/12,10*12,$C$3)
F3	F3	=XIRR(C3:C200,A3:A200,0.01)
F4	F4	=XIRR(C3:C200,B3:B200,0.01)
F8	F8	=((1+(0.1/12))^12)-1
F9	F9	=((1+(0.1/365))^365)-1