Monthly IRR Calculation for Stock Portfolio

[GoogleSheetz]

Hello, all. I'm trying to calculate the IRR (Internal Rate of Return) of a stock portfolio but my cash flow data is monthly.

I used both IRR and XIRR but it seems like the way I'm calculating them is wrong. How should I go about calculating the monthly IRR accurately?

Since I can't attached excel sheet, I've shared a Google Sheet so that you can make a copy of it and edit in whatever way you want:
Monthly IRR

Thanks.

 

[StephenCrump]

Welcome to the Forum!

I'm not sure how to interpret your numbers. For simplicity, I have assumed start value $1,288.20 at 1 May 2020, cash flow +$1,290 on 1 June, May investment return -$143.43 --> 1 June balance $2,434.77 etc. In practice, your cash flows are probably spread across the month and you might want to calculate a return assuming an average mid-month distribution?

This gives IRR and schedule as shown. Note this is a monthly return, not annualised for consistency with XIRR.

	A	B	C	D	E	F	G	H	I	J
1	Month	Cash Flow	Portfolio Value	Chg. in Portfolio Value	Cumulative Cash Flow		IRR	5.6%	Cash flow	Value
2	1 May 2020	-$1,200.00	$1,288.20				$1,288.20			$1,288.20
3	1 Jun 2020	-$1,290.00	$2,434.77	$1,146.56	-$2,490.00		$1,290.00		$1,290.00	$2,650.64
4	1 Jul 2020	-$1,290.00	$3,880.31	$1,445.54	-$3,780.00		$1,290.00		$1,290.00	$4,089.68
5	1 Aug 2020	-$3,870.00	$9,130.12	$5,249.81	-$7,650.00		$3,870.00		$3,870.00	$8,189.64
6	1 Sep 2020	-$2,250.00	$8,318.95	-$811.17	-$9,900.00		$2,250.00		$2,250.00	$10,900.13
7	1 Oct 2020	-$25,000.00	$28,872.96	$20,554.01	-$34,900.00		$25,000.00		$25,000.00	$36,513.03
8	1 Nov 2020	-$1,290.00	$28,389.17	-$483.79	-$36,190.00		$1,290.00		$1,290.00	$39,856.11
9	1 Dec 2020	-$2,140.00	$34,204.28	$5,815.10	-$38,330.00		$2,140.00		$2,140.00	$44,237.17
10	1 Jan 2021	$0.00	$40,011.22	$5,806.95	-$38,330.00		$0.00		$0.00	$46,724.57
11	1 Feb 2021	-$350.00	$45,825.35	$5,814.12	-$38,680.00		$350.00		$350.00	$49,701.84
12	1 Mar 2021	-$3,000.00	$51,616.77	$5,791.42	-$41,680.00		$3,000.00		$3,000.00	$55,496.51
13	1 Apr 2021	-$1,250.00	$57,280.38	$5,663.62	-$42,930.00		$1,250.00		$1,250.00	$59,867.01
14	1 May 2021	-$1,250.00	$63,143.83	$5,863.45	-$44,180.00		$1,250.00		$1,250.00	$64,483.26
15	1 Jun 2021	-$1,250.00	$69,014.74	$5,870.91	-$45,430.00		$1,250.00		$1,250.00	$69,359.07
16	1 Jul 2021	-$1,275.00	$74,757.97	$5,743.23	-$46,705.00		$1,275.00		$1,275.00	$74,534.04
17	1 Aug 2021	-$1,275.00	$80,000.00	$5,242.03	-$47,980.00		-$78,725.00		$1,275.00	$80,000.00
Sheet 1

Cell Formulas
Range	Formula
H1	H1	=IRR(G2:G17)
J2	J2	=C2
D3:D17	D3	=C3-C2
E3:E17	E3	=SUM(B$2:B3)
I3:I17	I3	=-B3
J3:J17	J3	=J2*(1+i)+I3
G2	G2	=C2
G3:G16	G3	=-B3
G17	G17	=-B17-C17

Named Ranges
Name	Refers To	Cells
i	='Sheet 1'!$H$1	J3:J17

 

[GoogleSheetz]

Welcome to the Forum!

I'm not sure how to interpret your numbers. For simplicity, I have assumed start value $1,288.20 at 1 May 2020, cash flow +$1,290 on 1 June, May investment return -$143.43 --> 1 June balance $2,434.77 etc. In practice, your cash flows are probably spread across the month and you might want to calculate a return assuming an average mid-month distribution?

This gives IRR and schedule as shown. Note this is a monthly return, not annualised for consistency with XIRR.

	A	B	C	D	E	F	G	H	I	J
1	Month	Cash Flow	Portfolio Value	Chg. in Portfolio Value	Cumulative Cash Flow		IRR	5.6%	Cash flow	Value
2	1 May 2020	-$1,200.00	$1,288.20				$1,288.20			$1,288.20
3	1 Jun 2020	-$1,290.00	$2,434.77	$1,146.56	-$2,490.00		$1,290.00		$1,290.00	$2,650.64
4	1 Jul 2020	-$1,290.00	$3,880.31	$1,445.54	-$3,780.00		$1,290.00		$1,290.00	$4,089.68
5	1 Aug 2020	-$3,870.00	$9,130.12	$5,249.81	-$7,650.00		$3,870.00		$3,870.00	$8,189.64
6	1 Sep 2020	-$2,250.00	$8,318.95	-$811.17	-$9,900.00		$2,250.00		$2,250.00	$10,900.13
7	1 Oct 2020	-$25,000.00	$28,872.96	$20,554.01	-$34,900.00		$25,000.00		$25,000.00	$36,513.03
8	1 Nov 2020	-$1,290.00	$28,389.17	-$483.79	-$36,190.00		$1,290.00		$1,290.00	$39,856.11
9	1 Dec 2020	-$2,140.00	$34,204.28	$5,815.10	-$38,330.00		$2,140.00		$2,140.00	$44,237.17
10	1 Jan 2021	$0.00	$40,011.22	$5,806.95	-$38,330.00		$0.00		$0.00	$46,724.57
11	1 Feb 2021	-$350.00	$45,825.35	$5,814.12	-$38,680.00		$350.00		$350.00	$49,701.84
12	1 Mar 2021	-$3,000.00	$51,616.77	$5,791.42	-$41,680.00		$3,000.00		$3,000.00	$55,496.51
13	1 Apr 2021	-$1,250.00	$57,280.38	$5,663.62	-$42,930.00		$1,250.00		$1,250.00	$59,867.01
14	1 May 2021	-$1,250.00	$63,143.83	$5,863.45	-$44,180.00		$1,250.00		$1,250.00	$64,483.26
15	1 Jun 2021	-$1,250.00	$69,014.74	$5,870.91	-$45,430.00		$1,250.00		$1,250.00	$69,359.07
16	1 Jul 2021	-$1,275.00	$74,757.97	$5,743.23	-$46,705.00		$1,275.00		$1,275.00	$74,534.04
17	1 Aug 2021	-$1,275.00	$80,000.00	$5,242.03	-$47,980.00		-$78,725.00		$1,275.00	$80,000.00
Sheet 1

Cell Formulas
Range	Formula
H1	H1	=IRR(G2:G17)
J2	J2	=C2
D3:D17	D3	=C3-C2
E3:E17	E3	=SUM(B$2:B3)
I3:I17	I3	=-B3
J3:J17	J3	=J2*(1+i)+I3
G2	G2	=C2
G3:G16	G3	=-B3
G17	G17	=-B17-C17

Named Ranges
Name	Refers To	Cells
i	='Sheet 1'!$H$1	J3:J17

Thank you. In fact, the cash flows are mostly made at the beginning of each month, so the start value at 1 May 2020 is $1,288.20. However, I recorded the Portfolio Value at the end of each month. So I guess that'll make the calculation inaccurate.

There could be some months in which multiple deposits were made. In this case, an average mid-month distribution would work as a good approximation. Do you have any suggestions as to the best time to in the month to record the cash flow & portfolio value to make the IRR as accurate as possible?

 

[StephenCrump]

Do you have any suggestions as to the best time to in the month to record the cash flow & portfolio value to make the IRR as accurate as possible?

There's no simple answer - it's up to you. Depending on what you're trying to show, it may be appropriate to ignore cash flows and calculate time-weighted returns, or to include cash flows and calculate money-weighted returns.

If you do want money-weighted, the importance of any cash flow distribution assumption will depend on the materiality of the cash flows relative to the portfolio value. In your case, the calculated IRR for October will vary hugely depending on the timing of the $25,000 cash flow. So a more granular approach would deliver a more accurate IRR.