Complicated Calculation

[hawaean]

Hello Excel Experts.

Please feel free to ask me questions about my sample investment spreadsheet. I'm not sure how much of it I need to explain to get my question(s) answered. The basic idea is the "Projection" tab summarizes the potential investments based on the top table, where you plot different letters (P=purchase,R=rent,S=sale) along the timeline to indicate when investments are made (ie. Purchase "P03" in 2017). On the "List" tab, I have all the potential investments. This is where the Projection tab pulls data from to calculate different things, like cash flow and equity.

Problem 1: As long as List and Projection have the same number of property rows, this sheet works fine. Since the sumproducts can't work with different ranges, everything gets screwed up if I add another property in the List tab. Can I keep the functionality of the spreadsheet, but add properties to the list? In other words, can the formulas check the projected list of investments against a larger/dynamic range of properties on the List tab? We can assume that the identifiers (ID, column A) are unique.

Let's use the "Net (Cash)" row as an example.

Current formula: =SUMPRODUCT((C$2:C$8="S")*List!$AC$3:$AC$9)+SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*List!$U$3:$U$9)*12-SUMPRODUCT((C$2:C$8="P")*List!$H$3:$H$9)

Problem occurs when I add a few rows on the List tab: =SUMPRODUCT((C$2:C$8="S")*List!$AC$3:$AC$12)+SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*List!$U$3:$U$12)*12-SUMPRODUCT((C$2:C$8="P")*List!$H$3:$H$12)

The question is, can I still do what this formula is doing, but have the ability to add rows on the List tab?

Problem 2: This isn't really a problem, but I'm curious if there's a more efficient way to calculate the "asset" and "liability" lines on the Projected tab. Currently, I have 2 separate tables (rows 10-35) running calculations of assets while accounting for appreciation, and liabilities while accounting for paying it down over time. Is there a way to fit all the calculations in the Asset and Liability rows, eliminating the need for the 2 above tables?

Thanks in advance!

	A	B	C	D	E	F	G	H	I	J	K
1		DP	2015	2016	2017	2018	2019	2020	2021	2022	2023
2	P01	50,000	P	R	R	R	R	R	S
3	P02	60,000		P	R	R	R	R	R	R	R
4	P03	50,000			P	R	R	R	R	R	R
5	P04	75,000				P	R	R	R	R	R
6	P05	30,000					P	R	R	R	R
7	P06	65,000						P	R	R	R
8	P07	120,000							P	R	R
9
10	Asset
11	P01		245,000	252,452	260,130	268,043	276,195	284,596
12	P02			147,000	151,471	156,078	160,826	165,717	170,758	175,951	181,303
13	P03				269,500	277,697	286,144	294,847	303,815	313,056	322,578
14	P04					343,000	353,433	364,183	375,260	386,674	398,435
15	P05						122,500	126,226	130,065	134,021	138,098
16	P06							283,220	291,834	300,711	309,857
17	P07								441,000	454,413	468,235
18	Loan
19	P01		195,000	190,629	186,057	181,276	176,274	171,043
20	P02			87,000	84,371	81,622	78,746	75,739	72,593	69,302	65,861
21	P03				219,500	214,734	209,749	204,535	199,081	193,377	187,411
22	P04					268,000	262,451	256,648	250,578	244,229	237,589
23	P05						92,500	89,783	86,941	83,968	80,859
24	P06							218,220	213,773	209,098	204,183
25	P07								321,000	315,036	308,767
26
27
28	Net (Cash)		-55,184	-57,825	-47,826	-70,567	-17,973	-54,323	17,852	20,681	20,681
29	Net (Equity)		-55,184	-57,825	-47,826	-70,567	-17,973	-54,323	38,739	20,681	20,681
30	Flow/Yr		2,166	6,585	10,259	14,723	15,702	19,173	20,681	20,681	20,681
31	Flow/Yr (g)		19,500	32,700	57,900	84,900	94,500	117,900	130,200	130,200	130,200
32	Asset		245,000	399,452	681,102	1,044,818	1,199,097	1,518,789	1,712,732	1,764,826	1,818,505
33	Liability		195,000	277,629	489,929	745,632	819,721	1,015,968	1,143,965	1,115,010	1,084,670
34	Equity		50,000	121,823	191,173	299,186	379,376	502,821	568,767	649,816	733,835
35	Reserve		3,644	5,275	9,852	14,646	16,310	20,524	22,976	22,976	22,976
36	Operating $	350,000	291,172	231,716	179,312	103,951	84,315	25,777	41,177	61,858	82,539
37	Net Worth		53,644	127,098	201,025	313,832	395,685	523,345	591,742	672,792	756,811

<tbody>
</tbody>

Projection​

Worksheet Formulas Cell Formula C11 =IF(OR(C2="",C2="S"),"", -FV( INDEX(List!$A$3:$AI$12,MATCH($A11,List!$A$3:$A$12,0),23)/12, 12*(COUNTA($C2:C2)-1), 0, INDEX(List!$A$3:$AI$12,MATCH($A11,List!$A$3:$A$12,0),4) )) C19 =IF(OR(C2="",C2="S"),"", MAX(0, FV( INDEX(List!$A$3:$AI$12,MATCH($A19,List!$A$3:$A$12,0),11)/12, 12*(COUNTA($C2:C2)-1), SUM( INDEX(List!$A$3:$AI$12,MATCH($A19,List!$A$3:$A$12,0),13), INDEX(List!$A$3:$AI$12,MATCH($A19,List!$A$3:$A$12,0),14)), -INDEX(List!$A$3:$AI$12,MATCH($A19,List!$A$3:$A$12,0),10) ))) C28 =SUMPRODUCT((C$2:C$8="S")*List!$AC$3:$AC$9)+SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*List!$U$3:$U$9)*12-SUMPRODUCT((C$2:C$8="P")*List!$H$3:$H$9) C29 =SUMPRODUCT((C$2:C$8="S")*List!$AA$3:$AA$9)+SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*List!$U$3:$U$9)*12-SUMPRODUCT((C$2:C$8="P")*List!$H$3:$H$9) C30 =SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*List!$U$3:$U$9)*12 C31 =SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*(List!$L$3:$L$9))*12 C32 =SUM(C11:C17) C33 =SUM(C19:C25) C34 =C32-C33 C35 =SUMPRODUCT(((C2:C8="R")+(C2:C8="P"))*List!$G$3:$G$9)*1.5 C36 =SUM(B35:B36,C28)-C35 C37 =SUM(C34:C35) <tbody> </tbody>

<tbody>
</tbody>

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	List		Purchase	98%	103%	P03						Rent			8%	0%		1%				Sale	7%			5%				Analysis
2	ID	Address	List Price	Buy Price	Sale Total	$ on Hand	Rsv	Cash Inv	DP	Loan	APR	Income	Mort	xPmt	PM	Ins	HOA	Tx (Yr)	Msc	Exp (mo)	Cash Flow	Price	AR	Repairs	Recap-25%	Cap Gain	1031	Tax	Net	Cap	R:BP (1%)	$:$	DTI	Return (GRM)	Return (Net)	Payoff
3	P01	132 Oak Street	250,000	245,000	252,350	50,000	2429	57,350	20%	195,000	4.5%	1,625	988	100	130	37	55	1,617	0	1,445	180	319,669	3.0%		7,016	80,356	151,288	20,887	130,402	6.2%	0.66%	4%	89%	12.6 yrs	17.1 yrs	24.9 yrs
4	P02	555 Maple Ln	150,000	147,000	151,410	60,000	1087	64,410	41%	87,000	4.5%	1,100	441	100	88	22	-	970	0	732	368	197,556	3.0%	5,000	4,811	50,970	130,157	13,393	116,764	7.4%	0.75%	7%	67%	11.2 yrs	11.5 yrs	20.6 yrs
5	P03	1st Ave #12	275,000	269,500	277,585	50,000	3052	58,085	19%	219,500	4.5%	2,100	1,112	100	168	40	225	1,779	0	1,794	306	351,636	3.0%		7,718	88,392	160,963	22,975	137,988	6.8%	0.78%	6%	85%	10.7 yrs	12 yrs	25.4 yrs
6	P04	1200 Cedar Road	350,000	343,000	353,290	75,000	3196	85,290	22%	268,000	4.5%	2,250	1,358	100	180	51	-	2,264	0	1,878	372	434,502	3.0%		8,419	94,763	190,480	24,689	165,790	6.4%	0.66%	5%	83%	12.8 yrs	15.1 yrs	26.1 yrs
7	P05	920 Brook Ave	125,000	122,500	126,175	30,000	1109	33,675	24%	92,500	4.5%	800	469	100	64	18	-	809	0	718	82	150,660	3.0%	600	2,506	27,036	70,219	7,085	63,134	6.4%	0.65%	3%	90%	12.8 yrs	15.5 yrs	21 yrs
8	P06	72 E Hillsdale Street	289,000	283,220	291,717	65,000	2809	73,497	23%	218,220	5.0%	1,950	1,171	100	156	42	35	1,869	0	1,661	289	338,179	3.0%		4,635	49,825	128,317	13,064	115,253	6.6%	0.69%	5%	85%	12.2 yrs	15.8 yrs	25.2 yrs
9	P07	4350 Green Ridge	450,000	441,000	454,230	120,000	4064	133,230	27%	321,000	5.0%	2,650	1,723	100	212	66	-	2,911	0	2,344	306	511,240	3.0%	3,000	5,412	53,102	190,040	14,113	175,927	5.8%	0.60%	3%	88%	13.9 yrs	27.4 yrs	26.6 yrs
10	P08	1014 Canyon Drive	150,000	147,000	151,410	50,000	1357	54,410	34%	97,000	5.0%	950	521	100	76	22	55	970	0	855	95	151,410	3.0%		601	0	47,227	0	47,227	5.8%	0.65%	2%	90%	12.9 yrs	23.3 yrs	21.2 yrs
11	P09	88 Thorton Ln	415,000	406,700	418,901	50,000	4399	62,201	12%	356,700	5.0%	2,550	1,915	100	204	61	-	2,684	0	2,504	46	418,901	3.0%	1,200	1,664	0	41,463	0	41,463	6.1%	0.63%	1%	98%	13.3 yrs	35.4 yrs	26.9 yrs
12	P10	41-829 Broadway Blvd	365,000	357,700	368,431	50,000	3904	60,731	14%	307,700	5.0%	2,350	1,652	100	188	54	50	2,361	0	2,240	110	368,431	3.0%		1,463	0	42,551	0	42,551	6.2%	0.66%	2%	95%	12.7 yrs	24.2 yrs	26.4 yrs
13	P11	1550 Lone Way	180,000	176,400	181,692	50,000	1954	55,292	28%	126,400	5.0%	1,750	679	100	140	26	175	1,164	0	1,217	533	181,692	3.0%		722	0	46,575	0	46,575	8.9%	0.99%	12%	70%	8.4 yrs	7.3 yrs	22.7 yrs
14	P12	560 Tumbleweed St	325,000	318,500	328,055	50,000	3675	59,555	16%	268,500	5.5%	2,100	1,525	100	168	48	90	2,102	0	2,105	-5	328,055	3.0%	6,500	1,303	0	43,079	0	43,079	6.1%	0.66%	0%	100%	12.7 yrs	52.5 yrs	25.9 yrs

<tbody>
</tbody>

List​

Worksheet Formulas Cell Formula D3 =C3*$D$1 E3 =D3*$E$1 F3 =IFERROR(INDEX(Projection!$A$2:$B$8,MATCH(A3,Projection!$A$2:$A$8,0),2),INDEX(Projection!$A$2:$B$8,MATCH(IFERROR($F$1,0),Projection!$A$2:$A$8,0),2)) G3 =MAX(2*SUM(-PMT(K3/12,360,D3-F3,0),P3:Q3,R3/12),0) H3 =SUM(E3:E3)-J3 I3 =MAX(IFERROR((D3-J3)/D3,0),0) J3 =IF(OR(D3=0,D3="",SUM(D3:D3)<f3< font="">),0,SUM(D3:D3)-F3</f3<>) M3 =-PMT($K3/12,360,J3) T3 =SUM(M3:Q3,S3)+R3/12 U3 =L3-T3 Y3 =SLN(D3*45%,0,27.5)*MAX(1,(COUNTA(INDEX(Projection!$A$2:$K$8,MATCH($A3,Projection!$A$2:$A$8,0),0))-2))*25% Z3 =MAX(0,V3*(1-7%)-(D3+X3-Y3*4)) AA3 =IFERROR(V3*(1-$W$1)-MAX(0, FV($K3/12,MAX(COUNTA(INDEX(Projection!$A$2:$K$8,MATCH($A3,Projection!$A$2:$A$8,0),0)),1)*12,SUM($M3:$N3),-$J3)),0) AB3 =MAX(0,15%*(V3*(1-7%)-D3-X3)+Z3*7.5%+Y3) AC3 =AA3-AB3 AD3 =IFERROR((L3-O3-P3-Q3-R3/12-S3)*12/D3,"") AE3 =IFERROR(MAX(0,L3)/D3,"") AF3 =IFERROR(U3*12/H3,"") AG3 =IFERROR(T3/L3,"") AH3 =IFERROR(MAX(ROUNDUP((D3/L3)/12,1),0)&" yrs","") AI3 =IFERROR(MAX(ROUNDUP(SUM(H3)/(L3*12-SUM(M3,O3:Q3,S3)*12-R3),1),0) &" yrs","") AJ3 =IFERROR(MAX(ROUNDUP(NPER(K3/12,-M3-$N3,$J3)/12,1),0) &" yrs","") <tbody> </tbody>

<tbody>
</tbody>

 

[Muhammad_Usman]

I think Your first Problem is due to range , as you know that sum product is array formula and in this all ranges should equal to same size. If you add some rows in any range you have to change number of rows all ranges select in formula.

 

[hawaean]

I think Your first Problem is due to range , as you know that sum product is array formula and in this all ranges should equal to same size. If you add some rows in any range you have to change number of rows all ranges select in formula.

Is there a formula that can examine one range against another range that are of different sizes? These don't have to be sumproducts if there's no way around its limitation.

 

[hawaean]

Let's use the "Net (Cash)" row as an example.

Current formula: =SUMPRODUCT((C$2:C$8="S")*List!$AC$3:$AC$9)+SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*List!$U$3:$U$9)*12-SUMPRODUCT((C$2:C$8="P")*List!$H$3:$H$9)

I'll clarify what the formula is doing:

1) Checks table (in its own column) for all instances of "S", "R", or "P" in rows 2:8 (C2:C8).
2) For each "S", find the corresponding rows on the List tab with the same property ID and adds the sum of the sale proceeds (column AC).
3) For each "R" and "P", find the corresponding rows on the List tab with the same property ID and adds sum of the net rental income (column U). The assumption is you get rent in the year you buy the property.
4) For each "P", find the corresponding rows on the List tab with the same property ID and subtracts the sum of the net out-of-pocket purchase cost (column H).

Please let me know if there's a way to calculate the same results, but where I'm allowed to add rows on either tab. My guess is it may need to be a index/match that uses the property ID's to find the correct data to sum.

Thanks.

 

[Eric W]

Try:

C28: =SUM(SUMIF(List!$A:$A,IF(C$2:C$8="S",$A$2:$A$8),List!$AC:$AC)+SUMIF(List!$A:$A,IF((C$2:C$8="R")+(C$2:C$8="P"),$A$2:$A$8),List!$U:$U)-SUMIF(List!$A:$A,IF(C$2:C$8="P",$A$2:$A$8),List!$H:$H))
confirmed with Control+Shift+Enter.

This gives wildly different results than your example, but this could be because I had a hard time importing your data, and also because your formula may not be doing what you expect. When I manually validated some of the results of this formula, they match your explanation from post #4 . The formula I presented does not rely on the number of rows on the Projection sheet to match the number of rows on the List sheet.

Hope this helps.

 

[hawaean]

Try:

C28: =SUM(SUMIF(List!$A:$A,IF(C$2:C$8="S",$A$2:$A$8),List!$AC:$AC)+SUMIF(List!$A:$A,IF((C$2:C$8="R")+(C$2:C$8="P"),$A$2:$A$8),List!$U:$U)-SUMIF(List!$A:$A,IF(C$2:C$8="P",$A$2:$A$8),List!$H:$H))
confirmed with Control+Shift+Enter.

This gives wildly different results than your example, but this could be because I had a hard time importing your data, and also because your formula may not be doing what you expect. When I manually validated some of the results of this formula, they match your explanation from post #4 . The formula I presented does not rely on the number of rows on the Projection sheet to match the number of rows on the List sheet.

Hope this helps.

Thanks Eric,

Taking C30 as another example, I applied your formula to get the net Cash Flow, which is really just = search the List tab for matching properties and summing column U.

C30 =SUMIF(List!$A:$A,IF((C$2:C$8="R")+(C$2:C$8="P"),$A$2:$A$8),List!$U:$U)

The result is 0, but should be $180. The next one, D30, should be $549 Because both P01 and P02 have an 'R' or 'P' in it. This formula returns $180.

This seems close to what I'm after, but it's probably just missing a small tweak.

 

[Eric W]

The C30 formula should be:

=SUM(SUMIF(List!$A:$A,IF((C$2:C$8="R")+(C$2:C$8="P"),$A$2:$A$8),List!$U:$U))
confirmed with Control+Shift+Enter.

We need the SUM around the SUMIF because the embedded IF creates an array containing the product value (column A) if the condition is true (column C= P or R), or FALSE if not. The SUMIF will calculate 7 times, using each value from the array, generating an internal result array. Then without the SUM, Excel can only display the first value from that array. With the SUM, it will total the array and give you the answer.