Conditional Formatting within same workbook but comparing different tabs

[ExcelUser18]

Hi All,

I need to compare two workbooks for a quarter over quarter analysis that are identical in size/formatting however some of the numbers may change. What I'd like to have is 1 workbook and have a 1Q21 tab and a 2Q21 tab side by side and apply a conditional formatting to 2Q21 which will show an increase (green) or a decrease (red) from the prior quarter. Of course if no change, no color would be applied. While the size (rows and columns) almost never change, it is possible that 3Q21 might be larger with an additional row or column (either in the middle or at the end of the range) than 2Q21, which may throw off the conditional formatting. For now, the range would be A1:AO150.

Also, if solved, would a simple copy tab to the same workbook continue to carry over the conditional formatting to the prior tab or how would you suggest?

Any help would be appreciated, please let me know if you need clarification.

 

[Toadstool]

Hi ExcelUser18,

Here is the starting point with, for the sake of simplicity, just has data in B1 to D10.

ExcelUser18.xlsx
	A	B	C	D
1	1Q21	Sales	Costs	Profit
2		1000	350	650
3		1000	350	650
4		1000	350	650
5		900	350	550
6		1000	200	800
7		1000	350	650
8		10	2000	-1990
9		1000	350	650
10		1000	350	650
1Q21

Cell Formulas
Range	Formula
D2:D10	D2	=B2-C2

The simplest approach would be just to compare each cell for > or < but note the color coding green red will depend on the meaning of the cell. If there are more or less rows of data then they'll all be highlit red/green for that row.

ExcelUser18.xlsx
	A	B	C	D
1	2Q21	Sales	Costs	Profit
2		1000	350	650
3		1000	350	650
4		1100	350	750
5		800	350	450
6		1000	400	600
7		1000	350	650
8		900	1900	-1000
9		1000	350	650
10
2Q21

Cell Formulas
Range	Formula
D2:D9	D2	=B2-C2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:D10	Expression	=B1<'1Q21'!B1	text	NO
B1:D10	Expression	=B1>'1Q21'!B1	text	NO

The challenge is when you add 3Q21, which could be a copy of 2Q21 and it would carry across the Conditional Formatting but still comparing to 1Q21. To get around this you could have a cell to give the sheet name against which to compare and then use INDIRECT.

ExcelUser18.xlsx
	A	B	C	D
1	3Q21	Sales	Costs	Profit
2	compare to	1000	350	650
3	2Q21	1000	500	500
4		2	350	-348
5		2222	350	1872
6		1000	400	600
7		1000	350	650
8		900	1900	-1000
9
10
3Q21

Cell Formulas
Range	Formula
D2:D8	D2	=B2-C2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:D10	Expression	=B1<INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW(),COLUMN()))	text	NO
B1:D10	Expression	=B1>INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW(),COLUMN()))	text	NO

 

[ExcelUser18]

Thanks Toadstool. The 1st part of your answer works correctly and if its an exact copy, the conditional formatting works. To your 2nd part, 1Q21 might have 130 rows but 2Q21 might have 135 rows or 125 rows. It sounds like it will throw off the formatting and thus cause half of the sheet to light up in colors, which obviously isn't the purpose. Any idea of how to get around that? One thing I may add to this is that in Column B and in Row 6, there are identifying numbers and headers, respectively, which I use index(match(match)) to pull data from a 3rd tab (not previously mentioned). Regardless of the size of each spreadsheet, the exact data that I need pulls into 2Q21 tab, which is then compared to 1Q21 to determine if the numbers increased or decreased. Is there some sort of index match formula with re: to conditional formatting?

Regarding the 3rd part, I might just skip that and continue to format each future tab, assuming part 2 of the aforementioned answer could be figured out.

 

[Toadstool]

It's tricky to follow without an example of what you have so far so maybe you can anonymize some test data and post using XL2BB?

If column B has something like a GL Code and you only want to compare when they match then this would work.

ExcelUser18-v2.xlsx
	A	B	C	D	E
1	1Q21	GLCode	Sales	Costs	Profit
2		222-22	1000	350	650
3		333-33	1000	350	650
4		444-44	1000	350	650
5		555-55	900	350	550
6		666-66	1000	400	600
7		777-77	1000	350	650
8		888-88	10	2000	-1990
9		999-99	1000	350	650
10		123-45	1000	350	650
11
1Q21

Cell Formulas
Range	Formula
E2:E10	E2	=C2-D2

ExcelUser18-v2.xlsx
	A	B	C	D	E
1	2Q21	GLCode	Sales	Costs	Profit
2		222-22	1000	350	650
3		333-33	1000	350	650
4		444-44	1100	350	750
5		222-22	800	350	450
6		666-66	1000	400	600
7		777-77	1000	350	650
8		888-88	900	1900	-1000
9		444-44	1000	400	600
10
11
12
2Q21

Cell Formulas
Range	Formula
E2:E9	E2	=C2-D2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:E16	Expression	=C2<INDEX('1Q21'!$C$2:$E$16,MATCH($B2,'1Q21'!$B$2:$B$16,0),COLUMN()-COLUMN($B$1))	text	NO
C2:E16	Expression	=C2>INDEX('1Q21'!$C$2:$E$16,MATCH($B2,'1Q21'!$B$2:$B$16,0),COLUMN()-COLUMN($B$1))	text	NO

 

[ExcelUser18]

Mr. Excel Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Validation Tape
2	Cut Off Date					= hard coded on purpose
3
4								FORMULA			FORMULA				FORMULA
5			Sign Off
6	Corresponding Database Field:		Investment Name		Current Commitment - Whole Loan	Current Commitment - SR Financing	Current Commitment - SM Ret	Calc	Whole Loan Spread	Sr Spread	Fully Fin. Ret Spread - UW	Current OPB as of 6/30/18 or closing - Whole Loan	Current OPB as of 6/30/18 or closing - Sr Financing	Current OPB as of 6/30/18 or closing - SM Ret	Calc
7	Portfolio	Investment Number	Investment	Initial Whole Loan Commitment	Whole Cut Off Commitment	Senior Cut Off Commitment	Retained Cut Off Commitment	Committed Advance Rate	Fully Financed Whole Loan Spread	Fully Financed Senior Spread	Fully Financed Retained Spread	Whole Cut Off OPB	Senior Cut Off OPB	Retained Cut Off OPB	Cut Off Advance Rate
8	2 Unrealized	CSCP02001	1	121,243,000.00	121,243,000	90,932,250	30,310,750	75.00%	3.60%	2.10%	8.10%	109,535,201.88	81,270,878.65	28,264,323.23	74.20%
9		CSCP02002	2	51,000,000.00	51,000,000	38,250,000	12,750,000	75.00%	3.65%	2.15%	8.15%	36,725,330.19	27,352,495.38	9,372,834.81	74.48%
10		CSCP02003	3	46,000,000.00	46,000,000	34,500,000	11,500,000	75.00%	3.55%	1.65%	9.25%	41,401,633.10	31,051,224.83	10,350,408.27	75.00%
11		CSCP02005	4	136,400,000.00	132,400,000	99,897,500	32,502,500	75.45%	3.29%	1.85%	7.73%	127,162,739.16	97,651,122.85	29,511,616.31	76.79%
12		CSCP02007	5	59,944,500.00	59,944,500	47,955,600	11,988,900	80.00%	2.60%	1.50%	7.00%	53,956,987.79	43,165,590.23	10,791,397.56	80.00%
13		CSCP02008	6	65,000,000.00	65,000,000	49,400,000	15,600,000	76.00%	2.75%	1.40%	7.03%	65,000,000.00	49,400,000.00	15,600,000.00	76.00%
14		CSCP02010	7	71,000,000.00	34,834,793	26,864,317	7,970,475	77.12%	3.79%	2.65%	7.62%	34,834,792.85	26,864,317.45	7,970,475.40	77.12%
15		CSCP02011	8	90,000,000.00	90,000,000	65,000,000	25,000,000	72.22%	2.90%	1.50%	6.54%	90,000,000.00	65,000,000.00	25,000,000.00	72.22%
16		CSCP02013	9	35,815,000.00	31,815,000	24,640,410	7,174,590	77.45%	3.15%	2.00%	7.10%	26,027,220.72	18,608,860.30	7,418,360.42	71.50%
17		CSCP02015	10	80,000,000.00	81,003,327	56,000,000	25,003,326.99	69.13%	3.00%	1.90%	5.46%	80,654,456.18	56,000,000.00	24,654,456.18	69.43%
18		CSCP02016	11	275,000,000.00	265,000,000	212,000,000	53,000,000	80.00%	2.85%	1.90%	6.65%	265,000,000.00	212,000,000.00	53,000,000.00	80.00%
19		CSCP02017	12	76,602,500.00	76,602,500	49,000,000	27,602,500	63.97%	2.75%	1.75%	4.53%	63,773,701.16	49,000,000.00	14,773,701.16	76.83%
20		CSCP02018	13	116,500,000.00	117,084,274	79,515,000	37,569,274	67.91%	3.25%	1.85%	6.21%	117,084,273.65	79,515,000.00	37,569,273.65	67.91%
21		CSCP02019	14	89,000,000.00	89,000,000	71,200,000	17,800,000	80.00%	2.50%	1.50%	6.50%	80,908,229.39	64,282,604.71	16,625,624.68	79.45%
22		CSCP02020	15	165,000,000.00	165,000,000	132,000,000	33,000,000	80.00%	2.35%	1.40%	6.15%	159,711,352.58	127,708,303.21	32,003,049.37	79.96%
23		CSCP02021	16	53,050,000.00	53,050,000	42,440,000	10,610,000	80.00%	2.65%	1.50%	7.25%	52,788,993.73	42,045,455.65	10,743,538.08	79.65%
24		CSCP02022	17	64,579,912.00	64,731,273	41,976,943	22,754,330	64.85%	2.93%	1.50%	5.57%	64,473,918.93	40,069,360.74	24,404,558.19	62.15%
25		CSCP02024	18	470,000,000.00	470,000,000	376,000,000	94,000,000	80.00%	2.30%	1.60%	5.10%	355,887,657.34	275,113,460.87	80,774,196.47	77.30%
26		CSCP02025	19	66,500,000.00	66,500,000	53,200,000	13,300,000	80.00%	2.75%	1.70%	6.95%	61,148,691.33	48,918,953.07	12,229,738.26	80.00%
27		CSCP02026	20	200,000,000.00	200,000,000	160,000,000	40,000,000	80.00%	4.00%	2.35%	10.60%	177,620,702.70	142,096,562.19	35,524,140.51	80.00%
28		CSCP02027	21	59,000,000.00	59,000,000	44,250,000	14,750,000	75.00%	3.25%	1.65%	8.05%	58,216,030.25	40,953,473.42	17,262,556.83	70.35%
29		CSCP02028	22	200,000,000.00	200,000,000	160,000,000	40,000,000	80.00%	3.30%	1.80%	9.30%	164,858,516.80	128,753,655.03	36,104,861.77	78.10%
30		CSCP02030	23	65,528,000.00	65,528,000	52,422,400	13,105,600	80.00%	3.20%	2.10%	7.60%	34,004,775.07	26,924,754.54	7,080,020.53	79.18%
31		CSCP02031	24	92,818,994.00	92,818,994	69,614,246	23,204,749	75.00%	3.65%	2.00%	8.60%	37,500,000.00	28,125,000.00	9,375,000.00	75.00%
32		CSCP02032	25	80,000,000.00	80,000,000	64,000,000	16,000,000	80.00%	2.90%	1.60%	8.10%	71,005,344.35	56,804,275.47	14,201,068.88	80.00%
33		CSCP02033	26		168,248,235.33	88,868,447.80	79,379,787.53	52.82%		3.00%		166,779,781.20	88,868,447.80	77,911,333.40	53.28%
34		CSCP02034	27	82,200,000.00	82,200,000	57,540,000	24,660,000	70.00%	5.00%	2.50%	10.83%	79,135,768.44	52,708,518.45	26,427,249.99	66.61%
35		CSCP02035	28	205,000,000.00	205,000,000	150,750,000	54,250,000	73.54%	3.70%	2.15%	8.01%	123,363,873.72	91,310,528.00	32,053,345.72	74.02%
36		CSCP02036	29	211,190,000.00	211,190,000	168,952,000	42,238,000	80.00%	3.25%	2.00%	8.25%	205,190,000.00	164,152,000.00	41,038,000.00	80.00%
37		CSCP02037	30	69,750,000.00	69,750,000	55,800,000	13,950,000	80.00%	3.50%	2.00%	9.50%	69,000,000.00	55,200,000.00	13,800,000.00	80.00%
38		CSCP02038	31	57,250,000.00	57,250,000	42,937,500	14,312,500	75.00%	3.60%	2.00%	8.40%	56,600,000.00	42,450,000.00	14,150,000.00	75.00%
39		CSCP02039	32	45,500,000.00	45,500,000	36,400,000	9,100,000	80.00%	3.45%	2.00%	9.25%	45,500,000.00	36,400,000.00	9,100,000.00	80.00%
6.31.21 TEST

Cell Formulas
Range	Formula
K8:K32,K34:K39	K8	=(P8*E8-Q8*F8)/G8
H8:H39,O8:O39	H8	=+F8/E8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AZ150	Expression	=A1>'3.31.21 Final'!A1	text	NO
A1:AZ150	Expression	=A1<'3.31.21 Final'!A1	text	NO
E1:AO130	Expression	=E1>'3.31.21 Final'!E1	text	NO
E1:AO130	Expression	=E1<'3.31.21 Final'!E1	text	NO

 

[ExcelUser18]

See above post. I have the book already formatted with both tabs however I'm not sure how I can upload that; I'm a novice user. If you could copy that tab twice then add the conditional formatting, that might be more helpful. Ultimately, I'm looking for any change starting from Cell A1 through the rest of the data sheet. Right now, if Tab 6.31.21 Test is higher than 3.31.21 Final, it shows up as Green and if it's lower than the 3.31.21 Final tab, it shows up as Orange. (Ignore the other extra color shading. Interestingly enough, Conditional Formatting overwrites any colors which is GREAT). While future quarters may have extra rows or columns, the formatting for Column B and Row 7 (the column/row I'd like reference in the index/match formula to pull from a 3rd tab) will remain the same. Basically if Row 11 is deleted in 6.31.21 TEST tab, it won't compare to Row 11 in the 3.31.21 Final tab. That is why I hope we can set the conditional formatting formula based off Column B and Row 7 so it won't matter if a row or column is added/deleted. I think that makes sense but I could certainly be wrong. Let me know what else I could do to help clarify. Thank you!

 

[Toadstool]

Hi Exceluser18,

I'm perplexed. If your data may have more/fewer rows then is it possible Investment Numbers may go and new ones arrive? (e.g. CSCP02008 is not on the next quarter sheet but CSCP02009 has been added). If so then, assuming Investment Number is your unique identifier for a row, you will need to use some kind of MATCH to only compare rows with the same Investment Number.

You've said columns may also be different which means if column H is moved/deleted then every cell to its right would be highlit as different. Again, you'd need to use some kind of MATCH to look in the correct column.

The complexity of your Conditional Formats will be great as they'll need to search the other sheet for a matching number, so something like this:

=INDEX('6.31.21 FINAL'!$A$7:$Q$999,MATCH('6.31.21 TEST (3)'!$B8,'6.31.21 FINAL'!$B:$B,0)-ROW($B$6),MATCH('6.31.21 TEST (3)'!D$7,'6.31.21 FINAL'!$7:$7,0))

With so many references to the other sheet embedded in the Conditional Format it would make maintenance tricky each month. You'd probably want to avoid that by using INDIRECT so maybe put the sheet name to compare in D1 and compare using:

=INDEX(INDIRECT("'"&$D$1&"'!$A$7:$Q$999"),MATCH(INDIRECT("'"&$D$1&"'!$B"&ROW()),INDIRECT("'"&$D$1&"'!$B:$B"),0)-ROW($B$6),MATCH(INDIRECT("'"&$D$1&"'!"&ADDRESS(7,COLUMN(D7))),INDIRECT("'"&$D$1&"'!$7:$7"),0))

Is this a direction you want to go?

 

[ExcelUser18]

Hi Toadstool,

Yes, it is likely that next quarter Investment #CSCP02008 is not on the next quarter tab but CSCP02009 will be added. Or lets say I needed to add a new column to next quarter because we need to incorporate a new column of data. My view is that it doesn't pose an issue because if a new investment number is added as a new row with new data, we know we won't need to check it from the prior quarter because it doesn't exist though you now bring up a new point: should there be formatting to flag that entire row? We have green and orange to reflect increases or decreases however could that be flagged another color because the formula doesn't return back a match?

I think the match comments you made are what I'm referencing but I'll explain with more detail in case I'm mixing things up. The formula I'd like to use to pull data from a 3rd unreferenced tab to fill in the tape would start in cell D8 of 6.31.21 TEST and would look like this: =INDEX('3rd Unreferenced Tab'!$1:$1048576,MATCH('6.31.21 TEST'!$C8,'3rd Unreferenced Tab'!$B:$B,0),MATCH('6.31.21 TEST'!D$7,'3rd Unreferenced Tab'!$5:$5,0)). What I'm hoping for is to replicate the same formula but for the conditional formatting so that way it won't matter the layout of the tape from quarter to quarter, as long as it finds the two identifying row/columns, it'll pull back the right data/conditional formatting correctly. In the instance a new number is added, it won't find it on the prior quarter's tab and the whole row should light up (assuming conditional formatting can be applied that way). All the conditional formatting requests are to flag for the user as a way to focus on increases/decreases/new investment #'s and their respective data.

Please let me know if that does not make sense; I hope I am being clear.

Thank you

 

[Toadstool]

Hi ExcelUser18,

You describe yourself as a novice so I should eschew the INDIRECT proposal but I just can't help myself.

Here is your 6.31.21 FINAL sheet which I've reduced to just column H for brevity. I've increased the number in G8, reduced the number in F10 (which of course reduce the result of the calculation in H10), removed the entry for CSCP02008 and added a row for CSCP02012. The cell colours are just there to remind me of the manual changes.

ExcelUser18-v3.xlsx
	A	B	C	D	E	F	G	H
1	Validation Tape
2	Cut Off Date					= hard coded on purpose
3
4								FORMULA
5			Sign Off
6	Corresponding Database Field:		Investment Name		Current Commitment - Whole Loan	Current Commitment - SR Financing	Current Commitment - SM Ret	Calc
7	Portfolio	Investment Number	Investment	Initial Whole Loan Commitment	Whole Cut Off Commitment	Senior Cut Off Commitment	Retained Cut Off Commitment	Committed Advance Rate
8	2 Unrealized	CSCP02001	1	121243000	121243000	90932250	30310751	0.75
9		CSCP02002	2	51000000	51000000	38250000	12750000	0.75
10		CSCP02003	3	46000000	46000000	34400000	11500000	0.747826087
11		CSCP02005	4	136400000	132400000	99897500	32502500	0.75451284
12		CSCP02007	5	59944500	59944500	47955600	11988900	0.8
13		CSCP02009	6	65000000	65000000	49400000	15600000	0.76
14		CSCP02010	7	71000000	34834792.85	26864317.45	7970475.4	0.771192111
15		CSCP02011	8	90000000	90000000	65000000	25000000	0.722222222
16		CSCP02012	9	35815000	31815000	24640410.3	7174589.7	0.774490344
17		CSCP02013	9	35815000	31815000	24640410.3	7174589.7	0.774490344
18		CSCP02015	10	80000000	81003326.99	56000000	25003326.99	0.691329629
19		CSCP02016	11	275000000	265000000	212000000	53000000	0.8
20		CSCP02017	12	76602500	76602500	49000000	27602500	0.639665807
21		CSCP02018	13	116500000	117084273.7	79515000	37569273.65	0.679126218
6.31.21 FINAL

Cell Formulas
Range	Formula
H8:H21	H8	=+F8/E8

Here's your 6.31.21 TEST sheet with Conditional Formatting applied.
In cell D1 you must enter the sheet name against which you want to compare.
The formula depends on column B always being the Investment Number and the heading always being in row 7. The conditional format therefore starts in B7.
There are three Conditional Format formulae:

1. The check for no matching Investment Number. If B7 and below have no match on the sheet against which to compare then it highlights the cell in amber (as it's done for row 13 because CSCP02008 doesn't exist on 6.31.21 FINAL).
2. The check for 6.31.21 TEST sheet value less than 6.31.21 FINAL value matched by Investment Number which gives a highlight in apricot.
3. The check for 6.31.21 TEST sheet value greater than 6.31.21 FINAL value matched by Investment Number which gives a highlight in light green.

Please note that the new CSCP02012 on 6.31.21 FINAL cannot identify a 6.31.21 TEST cell because there's nothing to match.

ExcelUser18-v3.xlsx
	A	B	C	D	E	F	G	H
1	Validation Tape			6.31.21 FINAL
2	Cut Off Date					= hard coded on purpose
3
4								FORMULA
5			Sign Off
6	Corresponding Database Field:		Investment Name		Current Commitment - Whole Loan	Current Commitment - SR Financing	Current Commitment - SM Ret	Calc
7	Portfolio	Investment Number	Investment	Initial Whole Loan Commitment	Whole Cut Off Commitment	Senior Cut Off Commitment	Retained Cut Off Commitment	Committed Advance Rate
8	2 Unrealized	CSCP02001	1	121243000	121243000	90932250	30310750	0.75
9		CSCP02002	2	51000000	51000000	38250000	12750000	0.75
10		CSCP02003	3	46000000	46000000	34500000	11500000	0.75
11		CSCP02005	4	136400000	132400000	99897500	32502500	0.75451284
12		CSCP02007	5	59944500	59944500	47955600	11988900	0.8
13		CSCP02008	6	65000000	65000000	49400000	15600000	0.76
14		CSCP02010	7	71000000	34834792.85	26864317.45	7970475.4	0.771192111
15		CSCP02011	8	90000000	90000000	65000000	25000000	0.722222222
16		CSCP02013	9	35815000	31815000	24640410.3	7174589.7	0.774490344
17		CSCP02015	10	80000000	81003326.99	56000000	25003326.99	0.691329629
18		CSCP02016	11	275000000	265000000	212000000	53000000	0.8
19		CSCP02017	12	76602500	76602500	49000000	27602500	0.639665807
20		CSCP02018	13	116500000	117084273.7	79515000	37569273.65	0.679126218
21
6.31.21 TEST

Cell Formulas
Range	Formula
H8:H20	H8	=+F8/E8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:H999	Expression	=AND(B7<>"",ISNA(INDEX(INDIRECT("'"&$D$1&"'!$A$7:$Q$999"),MATCH($B7,INDIRECT("'"&$D$1&"'!$B:$B"),0)-ROW($B$6),MATCH(INDIRECT("'"&$D$1&"'!"&ADDRESS(7,COLUMN(B6))),INDIRECT("'"&$D$1&"'!$7:$7"),0))))	text	YES
B7:H999	Expression	=B7>INDEX(INDIRECT("'"&$D$1&"'!$A$7:$Q$999"),MATCH($B7,INDIRECT("'"&$D$1&"'!$B:$B"),0)-ROW($B$6),MATCH(INDIRECT("'"&$D$1&"'!"&ADDRESS(7,COLUMN(B6))),INDIRECT("'"&$D$1&"'!$7:$7"),0))	text	NO
B7:H999	Expression	=B7<INDEX(INDIRECT("'"&$D$1&"'!$A$7:$Q$999"),MATCH($B7,INDIRECT("'"&$D$1&"'!$B:$B"),0)-ROW($B$6),MATCH(INDIRECT("'"&$D$1&"'!"&ADDRESS(7,COLUMN(B6))),INDIRECT("'"&$D$1&"'!$7:$7"),0))	text	NO

 

[ExcelUser18]

Mr. Excel Example.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2
3								Ref Only - DO NOT USE	Ref Only - DO NOT USE
4								OLD!!!			FORMULA
5
6	Sr Spread	CurrentRet Spread		Property Risk	Participation	Appr As Is - At Close - Attachment	Appr As Is - At Close - LTV	Re-UW As Is Value			Calc	RE-UW LTV
7	Cut Off Senior Spread	Cut Off Retained Spread		UW Risk Rating	Participation %	Appraised Senior LTV	Appraised Retained LTV	Current Values			Current Senior LTV	Current Retained LTV
8	2.10%	7.91%		Performing	100.0%	53.46%	71.28%	127,664,823	127,664,823	129,047,997	62.98%	84.88%
9	2.15%	8.03%		Performing	100.0%	55.31%	65.42%	49,259,348	49,259,348	50,126,521	54.57%	73.27%
10	1.65%	9.25%		Performing	100.0%	51.11%	68.14%	50,140,185	50,140,185	50,302,518	61.73%	82.31%
11	1.85%	8.07%		Performing	100.0%	60.78%	78.42%	159,527,524	159,527,524	160,003,544	61.03%	79.47%
12	1.50%	7.00%		Performing	100.0%	58.44%	73.05%	72,402,031	72,402,031	73,408,904	58.80%	73.50%
13	1.40%	7.03%		Performing	100.0%	53.70%	70.65%	78,772,288	78,772,288	78,772,288	62.71%	82.52%
14	2.65%	7.62%		Performing	100.0%	43.03%	49.19%	64,021,697	64,021,697	60,601,017	44.33%	57.48%
15	1.50%	6.54%		Performing	100.0%	52.00%	71.20%	107,020,526	107,020,526	107,020,526	60.34%	83.55%	582,148
16	2.00%	6.03%		Performing	100.0%	64.80%	72.32%	29,318,650	29,318,650	29,636,241	60.29%	84.33%	1,034,979
17	1.90%	5.50%		Watchlist	100.0%	48.81%	61.02%	85,000,000	85,000,000	85,299,858	65.65%	94.55%
18	1.90%	6.65%		Performing	100.0%	50.12%	62.65%	300,000,000	300,000,000	296,423,149	65.92%	82.39%	20,765,703
19	1.75%	6.07%		Performing	100.0%	52.53%	65.67%	71,000,000	71,000,000	76,326,324	64.20%	83.55%
20	1.85%	6.21%		Watchlist	100.0%	58.78%	73.58%	131,380,405	131,380,405	131,380,405	59.71%	87.93%	1,565,890
21	1.50%	6.37%		Performing	100.0%	51.20%	64.00%	125,500,000	125,500,000	126,924,785	50.65%	63.75%
22	1.40%	6.14%		Performing	100.0%	55.79%	69.74%	203,846,154	203,846,154	204,758,144	62.37%	78.00%
23	1.50%	7.15%		Performing	100.0%	51.66%	64.58%	77,550,000	77,550,000	78,683,971	53.44%	67.09%
24	1.50%	5.28%		Watchlist	100.0%	53.47%	71.29%	69,400,770	69,400,770	73,717,005	54.36%	87.46%
25	1.60%	4.68%		Performing	100.0%	42.24%	52.80%	534,305,790	534,305,790	604,040,308	45.55%	58.92%
26	1.70%	6.95%		Performing	100.0%	59.95%	74.94%	81,400,000	81,400,000	81,400,000	60.10%	75.12%
27	2.35%	10.60%		Performing	100.0%	53.54%	66.93%	200,000,000	200,000,000	211,448,126	67.20%	84.00%
28	1.65%	7.05%		Performing	100.0%	50.25%	54.72%	72,806,175	72,806,175	76,560,706	53.49%	76.04%
29	1.80%	8.65%		Performing	100.0%	54.82%	68.52%	197,807,326	197,807,326	205,286,055	62.72%	80.31%
30	2.10%	7.38%		Performing	100.0%	49.13%	61.41%	45,933,219	45,933,219	46,409,301	58.02%	73.27%
31	2.00%	8.60%		Watchlist	50.0%	40.18%	53.57%	94,112,109	94,112,109	94,112,109	59.77%	79.69%
32	1.60%	8.10%		Performing	100.0%	48.81%	61.01%	101,052,334	101,052,334	101,246,028	56.11%	70.13%
33	3.00%			REO	100.0%	57.82%	70.46%	147,477,572		147,427,705	60.28%	113.13%
34	2.50%	9.99%		Performing	100.0%	59.90%	77.16%	88,000,000	88,000,000	91,427,570	51.39%	77.16%	8,592,029
35	2.15%	8.12%		Performing	100.0%	36.97%	50.27%	245,000,000	245,000,000	245,380,624	37.21%	50.27%
36	2.00%	8.25%		Performing	100.0%	54.14%	67.67%	303,200,000	303,200,000	303,200,000	54.14%	67.67%
37	2.00%	9.50%		Performing	100.0%	59.23%	74.03%	93,200,000	93,200,000	93,205,457	59.22%	74.03%
38	2.00%	8.40%		Performing	100.0%	56.08%	74.77%	75,700,000	75,700,000	75,698,810	56.08%	74.77%
39	2.00%	9.25%		Performing	100.0%	59.57%	74.47%	61,100,000	61,100,000	61,098,429	59.58%	74.47%
40
6.31.21 TEST

Cell Formulas
Range	Formula
AA8:AA39	AA8	=AB8*O8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:AZ200	Expression	=AND(B7<>"",ISNA(INDEX(INDIRECT("'"&$D$1&"'!$A$7:$Q$999"),MATCH($B7,INDIRECT("'"&$D$1&"'!$B:$B"),0)-ROW($B$6),MATCH(INDIRECT("'"&$D$1&"'!"&ADDRESS(7,COLUMN(B6))),INDIRECT("'"&$D$1&"'!$7:$7"),0))))	text	YES
B7:AZ200	Expression	=B7>INDEX(INDIRECT("'"&$D$1&"'!$A$7:$Q$999"),MATCH($B7,INDIRECT("'"&$D$1&"'!$B:$B"),0)-ROW($B$6),MATCH(INDIRECT("'"&$D$1&"'!"&ADDRESS(7,COLUMN(B6))),INDIRECT("'"&$D$1&"'!$7:$7"),0))	text	NO
B7:AZ200	Expression	=B7<INDEX(INDIRECT("'"&$D$1&"'!$A$7:$Q$999"),MATCH($B7,INDIRECT("'"&$D$1&"'!$B:$B"),0)-ROW($B$6),MATCH(INDIRECT("'"&$D$1&"'!"&ADDRESS(7,COLUMN(B6))),INDIRECT("'"&$D$1&"'!$7:$7"),0))	text	NO