IRR vs XIRR

SBRiii

New Member
Joined
Oct 23, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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?
 
Upvote 0
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
ABCDEFGHIJKLMN
1IRR vs. XIRR on 10% loan repaid over 10 yrs.
2Date approx.Date exact Monthly cash flowXIRRIRR
310/01/2310/01/23$100,000.00Approx. date10.467%10.000%
410/31/2311/01/23-$1,321.51Exact date10.461%
511/30/2312/01/23-$1,321.51
612/31/2301/01/24-$1,321.51
701/30/2402/01/24-$1,321.51May have something to do w/ compounding (XIRR apparently assumes compounding daily)
803/01/2403/01/24-$1,321.5110.47%10%/yr. compounded monthly
903/31/2404/01/24-$1,321.5110.52%10%/yr. compounded daily
1005/01/2405/01/24-$1,321.51However, these computed rates don't vary much between monthly & daily compounding and I understand that the Excel PMT function assumes monthly compounding
1105/31/2406/01/24-$1,321.51
1206/30/2407/01/24-$1,321.51
1307/31/2408/01/24-$1,321.51
1408/30/2409/01/24-$1,321.51
1509/30/2410/01/24-$1,321.51
Monthly
Cell Formulas
RangeFormula
G3G3=IRR(C3:C200,0.01)*12
A4:A15A4=A3+(365.25/12)
B4:B15B4=DATE(IF(MONTH(B3)<12,YEAR(B3),YEAR(B3)+1),IF(MONTH(B3)<12,MONTH(B3)+1,1),1)
C4:C15C4=PMT(0.1/12,10*12,$C$3)
F3F3=XIRR(C3:C200,A3:A200,0.01)
F4F4=XIRR(C3:C200,B3:B200,0.01)
F8F8=((1+(0.1/12))^12)-1
F9F9=((1+(0.1/365))^365)-1
 
Upvote 0
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
ABCDEFGHIJKLMN
1IRR vs. XIRR on 10% loan repaid over 10 yrs.
2Date approx.Date exact Monthly cash flowXIRRIRR
310/01/2310/01/23$100,000.00Approx. date10.467%10.000%
410/31/2311/01/23-$1,321.51Exact date10.461%
511/30/2312/01/23-$1,321.51
612/31/2301/01/24-$1,321.51
701/30/2402/01/24-$1,321.51May have something to do w/ compounding (XIRR apparently assumes compounding daily)
803/01/2403/01/24-$1,321.5110.47%10%/yr. compounded monthly
903/31/2404/01/24-$1,321.5110.52%10%/yr. compounded daily
1005/01/2405/01/24-$1,321.51However, these computed rates don't vary much between monthly & daily compounding and I understand that the Excel PMT function assumes monthly compounding
1105/31/2406/01/24-$1,321.51
1206/30/2407/01/24-$1,321.51
1307/31/2408/01/24-$1,321.51
1408/30/2409/01/24-$1,321.51
1509/30/2410/01/24-$1,321.51
1610/30/2411/01/24-$1,321.51
1711/30/2412/01/24-$1,321.51
1812/30/2401/01/25-$1,321.51
1901/30/2502/01/25-$1,321.51
2003/01/2503/01/25-$1,321.51
2103/31/2504/01/25-$1,321.51
2205/01/2505/01/25-$1,321.51
2305/31/2506/01/25-$1,321.51
2407/01/2507/01/25-$1,321.51
2507/31/2508/01/25-$1,321.51
2608/31/2509/01/25-$1,321.51
2709/30/2510/01/25-$1,321.51
2810/30/2511/01/25-$1,321.51
2911/30/2512/01/25-$1,321.51
3012/30/2501/01/26-$1,321.51
3101/30/2602/01/26-$1,321.51
3203/01/2603/01/26-$1,321.51
3304/01/2604/01/26-$1,321.51
3405/01/2605/01/26-$1,321.51
3506/01/2606/01/26-$1,321.51
3607/01/2607/01/26-$1,321.51
3707/31/2608/01/26-$1,321.51
3808/31/2609/01/26-$1,321.51
3909/30/2610/01/26-$1,321.51
4010/31/2611/01/26-$1,321.51
4111/30/2612/01/26-$1,321.51
4212/31/2601/01/27-$1,321.51
4301/30/2702/01/27-$1,321.51
4403/01/2703/01/27-$1,321.51
4504/01/2704/01/27-$1,321.51
4605/01/2705/01/27-$1,321.51
4706/01/2706/01/27-$1,321.51
4807/01/2707/01/27-$1,321.51
4908/01/2708/01/27-$1,321.51
5008/31/2709/01/27-$1,321.51
5110/01/2710/01/27-$1,321.51
5210/31/2711/01/27-$1,321.51
5311/30/2712/01/27-$1,321.51
5412/31/2701/01/28-$1,321.51
5501/30/2802/01/28-$1,321.51
5603/01/2803/01/28-$1,321.51
5703/31/2804/01/28-$1,321.51
5805/01/2805/01/28-$1,321.51
5905/31/2806/01/28-$1,321.51
6006/30/2807/01/28-$1,321.51
6107/31/2808/01/28-$1,321.51
6208/30/2809/01/28-$1,321.51
6309/30/2810/01/28-$1,321.51
6410/30/2811/01/28-$1,321.51
6511/30/2812/01/28-$1,321.51
6612/30/2801/01/29-$1,321.51
6701/30/2902/01/29-$1,321.51
6803/01/2903/01/29-$1,321.51
6903/31/2904/01/29-$1,321.51
7005/01/2905/01/29-$1,321.51
7105/31/2906/01/29-$1,321.51
7207/01/2907/01/29-$1,321.51
7307/31/2908/01/29-$1,321.51
7408/31/2909/01/29-$1,321.51
7509/30/2910/01/29-$1,321.51
7610/30/2911/01/29-$1,321.51
7711/30/2912/01/29-$1,321.51
7812/30/2901/01/30-$1,321.51
7901/30/3002/01/30-$1,321.51
8003/01/3003/01/30-$1,321.51
8104/01/3004/01/30-$1,321.51
8205/01/3005/01/30-$1,321.51
8306/01/3006/01/30-$1,321.51
8407/01/3007/01/30-$1,321.51
8507/31/3008/01/30-$1,321.51
8608/31/3009/01/30-$1,321.51
8709/30/3010/01/30-$1,321.51
8810/31/3011/01/30-$1,321.51
8911/30/3012/01/30-$1,321.51
9012/31/3001/01/31-$1,321.51
9101/30/3102/01/31-$1,321.51
9203/01/3103/01/31-$1,321.51
9304/01/3104/01/31-$1,321.51
9405/01/3105/01/31-$1,321.51
9506/01/3106/01/31-$1,321.51
9607/01/3107/01/31-$1,321.51
9708/01/3108/01/31-$1,321.51
9808/31/3109/01/31-$1,321.51
9910/01/3110/01/31-$1,321.51
10010/31/3111/01/31-$1,321.51
10111/30/3112/01/31-$1,321.51
10212/31/3101/01/32-$1,321.51
10301/30/3202/01/32-$1,321.51
10403/01/3203/01/32-$1,321.51
10503/31/3204/01/32-$1,321.51
10605/01/3205/01/32-$1,321.51
10705/31/3206/01/32-$1,321.51
10806/30/3207/01/32-$1,321.51
10907/31/3208/01/32-$1,321.51
11008/30/3209/01/32-$1,321.51
11109/30/3210/01/32-$1,321.51
11210/30/3211/01/32-$1,321.51
11311/30/3212/01/32-$1,321.51
11412/30/3201/01/33-$1,321.51
11501/30/3302/01/33-$1,321.51
11603/01/3303/01/33-$1,321.51
11703/31/3304/01/33-$1,321.51
11805/01/3305/01/33-$1,321.51
11905/31/3306/01/33-$1,321.51
12007/01/3307/01/33-$1,321.51
12107/31/3308/01/33-$1,321.51
12208/31/3309/01/33-$1,321.51
12309/30/3310/01/33-$1,321.51
Monthly
Cell Formulas
RangeFormula
G3G3=IRR(C3:C200,0.01)*12
A4:A123A4=A3+(365.25/12)
B4:B123B4=DATE(IF(MONTH(B3)<12,YEAR(B3),YEAR(B3)+1),IF(MONTH(B3)<12,MONTH(B3)+1,1),1)
C4:C123C4=PMT(0.1/12,10*12,$C$3)
F3F3=XIRR(C3:C200,A3:A200,0.01)
F4F4=XIRR(C3:C200,B3:B200,0.01)
F8F8=((1+(0.1/12))^12)-1
F9F9=((1+(0.1/365))^365)-1
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top