Complicated Calculation

hawaean

New Member
Joined
Aug 25, 2016
Messages
36
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!

ABCDEFGHIJK
P01
P02
P03
P04
P05
P06
P07
Asset
P01
P02
P03
P04
P05
P06
P07
Loan
P01
P02
P03
P04
P05
P06
P07
Net (Cash)
Net (Equity)
Flow/Yr (n)
Flow/Yr (g)
Asset
Liability
Equity
Reserve
Operating $
Net Worth

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]DP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: center"]2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]2020[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]2021[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]2022[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]2023[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]60,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]75,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]30,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]65,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]120,000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: center"] 245,000[/TD]
[TD="align: center"] 252,452[/TD]
[TD="align: center"] 260,130[/TD]
[TD="align: center"] 268,043[/TD]
[TD="align: center"] 276,195[/TD]
[TD="align: center"] 284,596[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 147,000[/TD]
[TD="align: center"] 151,471[/TD]
[TD="align: center"] 156,078[/TD]
[TD="align: center"] 160,826[/TD]
[TD="align: center"] 165,717[/TD]
[TD="align: center"] 170,758[/TD]
[TD="align: center"] 175,951[/TD]
[TD="align: center"] 181,303[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 269,500[/TD]
[TD="align: center"] 277,697[/TD]
[TD="align: center"] 286,144[/TD]
[TD="align: center"] 294,847[/TD]
[TD="align: center"] 303,815[/TD]
[TD="align: center"] 313,056[/TD]
[TD="align: center"] 322,578[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 343,000[/TD]
[TD="align: center"] 353,433[/TD]
[TD="align: center"] 364,183[/TD]
[TD="align: center"] 375,260[/TD]
[TD="align: center"] 386,674[/TD]
[TD="align: center"] 398,435[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 122,500[/TD]
[TD="align: center"] 126,226[/TD]
[TD="align: center"] 130,065[/TD]
[TD="align: center"] 134,021[/TD]
[TD="align: center"] 138,098[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 283,220[/TD]
[TD="align: center"] 291,834[/TD]
[TD="align: center"] 300,711[/TD]
[TD="align: center"] 309,857[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 441,000[/TD]
[TD="align: center"] 454,413[/TD]
[TD="align: center"] 468,235[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: center"] 195,000[/TD]
[TD="align: center"] 190,629[/TD]
[TD="align: center"] 186,057[/TD]
[TD="align: center"] 181,276[/TD]
[TD="align: center"] 176,274[/TD]
[TD="align: center"] 171,043[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 87,000[/TD]
[TD="align: center"] 84,371[/TD]
[TD="align: center"] 81,622[/TD]
[TD="align: center"] 78,746[/TD]
[TD="align: center"] 75,739[/TD]
[TD="align: center"] 72,593[/TD]
[TD="align: center"] 69,302[/TD]
[TD="align: center"] 65,861[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 219,500[/TD]
[TD="align: center"] 214,734[/TD]
[TD="align: center"] 209,749[/TD]
[TD="align: center"] 204,535[/TD]
[TD="align: center"] 199,081[/TD]
[TD="align: center"] 193,377[/TD]
[TD="align: center"] 187,411[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 268,000[/TD]
[TD="align: center"] 262,451[/TD]
[TD="align: center"] 256,648[/TD]
[TD="align: center"] 250,578[/TD]
[TD="align: center"] 244,229[/TD]
[TD="align: center"] 237,589[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 92,500[/TD]
[TD="align: center"] 89,783[/TD]
[TD="align: center"] 86,941[/TD]
[TD="align: center"] 83,968[/TD]
[TD="align: center"] 80,859[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 218,220[/TD]
[TD="align: center"] 213,773[/TD]
[TD="align: center"] 209,098[/TD]
[TD="align: center"] 204,183[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] 321,000[/TD]
[TD="align: center"] 315,036[/TD]
[TD="align: center"] 308,767[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: right"]-55,184[/TD]
[TD="align: right"]-57,825[/TD]
[TD="align: right"]-47,826[/TD]
[TD="align: right"]-70,567[/TD]
[TD="align: right"]-17,973[/TD]
[TD="align: right"]-54,323[/TD]
[TD="align: right"]17,852[/TD]
[TD="align: right"]20,681[/TD]
[TD="align: right"]20,681[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]
[TD="align: right"]-55,184[/TD]
[TD="align: right"]-57,825[/TD]
[TD="align: right"]-47,826[/TD]
[TD="align: right"]-70,567[/TD]
[TD="align: right"]-17,973[/TD]
[TD="align: right"]-54,323[/TD]
[TD="align: right"]38,739[/TD]
[TD="align: right"]20,681[/TD]
[TD="align: right"]20,681[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2,166[/TD]
[TD="align: right"]6,585[/TD]
[TD="align: right"]10,259[/TD]
[TD="align: right"]14,723[/TD]
[TD="align: right"]15,702[/TD]
[TD="align: right"]19,173[/TD]
[TD="align: right"]20,681[/TD]
[TD="align: right"]20,681[/TD]
[TD="align: right"]20,681[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"]19,500[/TD]
[TD="align: right"]32,700[/TD]
[TD="align: right"]57,900[/TD]
[TD="align: right"]84,900[/TD]
[TD="align: right"]94,500[/TD]
[TD="align: right"]117,900[/TD]
[TD="align: right"]130,200[/TD]
[TD="align: right"]130,200[/TD]
[TD="align: right"]130,200[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"]245,000[/TD]
[TD="align: right"]399,452[/TD]
[TD="align: right"]681,102[/TD]
[TD="align: right"]1,044,818[/TD]
[TD="align: right"]1,199,097[/TD]
[TD="align: right"]1,518,789[/TD]
[TD="align: right"]1,712,732[/TD]
[TD="align: right"]1,764,826[/TD]
[TD="align: right"]1,818,505[/TD]

[TD="align: center"]33[/TD]

[TD="align: right"][/TD]
[TD="align: right"]195,000[/TD]
[TD="align: right"]277,629[/TD]
[TD="align: right"]489,929[/TD]
[TD="align: right"]745,632[/TD]
[TD="align: right"]819,721[/TD]
[TD="align: right"]1,015,968[/TD]
[TD="align: right"]1,143,965[/TD]
[TD="align: right"]1,115,010[/TD]
[TD="align: right"]1,084,670[/TD]

[TD="align: center"]34[/TD]

[TD="align: right"][/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]121,823[/TD]
[TD="align: right"]191,173[/TD]
[TD="align: right"]299,186[/TD]
[TD="align: right"]379,376[/TD]
[TD="align: right"]502,821[/TD]
[TD="align: right"]568,767[/TD]
[TD="align: right"]649,816[/TD]
[TD="align: right"]733,835[/TD]

[TD="align: center"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3,644[/TD]
[TD="align: right"]5,275[/TD]
[TD="align: right"]9,852[/TD]
[TD="align: right"]14,646[/TD]
[TD="align: right"]16,310[/TD]
[TD="align: right"]20,524[/TD]
[TD="align: right"]22,976[/TD]
[TD="align: right"]22,976[/TD]
[TD="align: right"]22,976[/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]350,000[/TD]
[TD="align: right"]291,172[/TD]
[TD="align: right"]231,716[/TD]
[TD="align: right"]179,312[/TD]
[TD="align: right"]103,951[/TD]
[TD="align: right"]84,315[/TD]
[TD="align: right"]25,777[/TD]
[TD="align: right"]41,177[/TD]
[TD="align: right"]61,858[/TD]
[TD="align: right"]82,539[/TD]

[TD="align: center"]37[/TD]

[TD="align: right"][/TD]
[TD="align: right"]53,644[/TD]
[TD="align: right"]127,098[/TD]
[TD="align: right"]201,025[/TD]
[TD="align: right"]313,832[/TD]
[TD="align: right"]395,685[/TD]
[TD="align: right"]523,345[/TD]
[TD="align: right"]591,742[/TD]
[TD="align: right"]672,792[/TD]
[TD="align: right"]756,811[/TD]

</tbody>
Projection

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C11[/TH]
[TD="align: left"]=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)
)
)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C19[/TH]
[TD="align: left"]=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)
)
)
)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C28[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C29[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C30[/TH]
[TD="align: left"]=SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*List!$U$3:$U$9)*12[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C31[/TH]
[TD="align: left"]=SUMPRODUCT(((C$2:C$8="R")+(C$2:C$8="P"))*(List!$L$3:$L$9))*12[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C32[/TH]
[TD="align: left"]=SUM(C11:C17)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C33[/TH]
[TD="align: left"]=SUM(C19:C25)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C34[/TH]
[TD="align: left"]=C32-C33[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C35[/TH]
[TD="align: left"]=SUMPRODUCT(((C2:C8="R")+(C2:C8="P"))*List!$G$3:$G$9)*1.5[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C36[/TH]
[TD="align: left"]=SUM(B35:B36,C28)-C35[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C37[/TH]
[TD="align: left"]=SUM(C34:C35)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
ListPurchase P03RentSaleAnalysis
IDAddress
P01132 Oak Street
P02555 Maple Ln
P031st Ave #12
P041200 Cedar Road
P05920 Brook Ave
P0672 E Hillsdale Street
P074350 Green Ridge
P081014 Canyon Drive
P0988 Thorton Ln
P1041-829 Broadway Blvd
P111550 Lone Way
P12560 Tumbleweed St

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"]98%[/TD]
[TD="align: right"]103%[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]List Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]Buy Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]Sale Total[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]$ on Hand[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]Rsv[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]Cash Inv[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]DP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]Loan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E6B8B7]#E6B8B7[/URL] "]APR[/TD]
[TD="bgcolor: #D8E4BC"]Income[/TD]
[TD="bgcolor: #D8E4BC"]Mort[/TD]
[TD="bgcolor: #D8E4BC"]xPmt[/TD]
[TD="bgcolor: #D8E4BC"]PM[/TD]
[TD="bgcolor: #D8E4BC"]Ins[/TD]
[TD="bgcolor: #D8E4BC"]HOA[/TD]
[TD="bgcolor: #D8E4BC"]Tx (Yr)[/TD]
[TD="bgcolor: #D8E4BC"]Msc[/TD]
[TD="bgcolor: #D8E4BC"]Exp (mo)[/TD]
[TD="bgcolor: #D8E4BC"]Cash Flow[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] "]Price[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] "]AR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] "]Repairs[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] "]Recap-25%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] "]Cap Gain[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] , align: right"]1031[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] "]Tax[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B7DEE8]#B7DEE8[/URL] "]Net[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] "]Cap[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] "]R:BP (1%)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] "]$:$[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] "]DTI[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: right"]Return (GRM)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: right"]Return (Net)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC0DA]#CCC0DA[/URL] , align: right"]Payoff[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]250,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]245,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]252,350[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2429[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]57,350[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]20%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]195,000[/TD]
[TD="align: right"]4.5%[/TD]
[TD="align: right"]1,625[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]988[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]130[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 37[/TD]
[TD="align: right"] 55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 1,617[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,445[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]180[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]319,669[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]7,016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]80,356[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]151,288[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]20,887[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]130,402[/TD]
[TD="align: right"]6.2%[/TD]
[TD="align: right"]0.66%[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]89%[/TD]
[TD="align: right"]12.6 yrs[/TD]
[TD="align: right"]17.1 yrs[/TD]
[TD="align: right"]24.9 yrs[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]150,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]147,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]151,410[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]60,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1087[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]64,410[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]41%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]87,000[/TD]
[TD="align: right"]4.5%[/TD]
[TD="align: right"]1,100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]441[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]88[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 22[/TD]
[TD="align: right"] -[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 970[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]732[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]368[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]197,556[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]5,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]4,811[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,970[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]130,157[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]13,393[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]116,764[/TD]
[TD="align: right"]7.4%[/TD]
[TD="align: right"]0.75%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]11.2 yrs[/TD]
[TD="align: right"]11.5 yrs[/TD]
[TD="align: right"]20.6 yrs[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]275,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]269,500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]277,585[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]3052[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]58,085[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]19%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]219,500[/TD]
[TD="align: right"]4.5%[/TD]
[TD="align: right"]2,100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,112[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]168[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 40[/TD]
[TD="align: right"] 225[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 1,779[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,794[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]306[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]351,636[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]7,718[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]88,392[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]160,963[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]22,975[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]137,988[/TD]
[TD="align: right"]6.8%[/TD]
[TD="align: right"]0.78%[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]85%[/TD]
[TD="align: right"]10.7 yrs[/TD]
[TD="align: right"]12 yrs[/TD]
[TD="align: right"]25.4 yrs[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]350,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]343,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]353,290[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]75,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]3196[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]85,290[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]22%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]268,000[/TD]
[TD="align: right"]4.5%[/TD]
[TD="align: right"]2,250[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,358[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]180[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 51[/TD]
[TD="align: right"] -[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 2,264[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,878[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]372[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]434,502[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]8,419[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]94,763[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]190,480[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]24,689[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]165,790[/TD]
[TD="align: right"]6.4%[/TD]
[TD="align: right"]0.66%[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]83%[/TD]
[TD="align: right"]12.8 yrs[/TD]
[TD="align: right"]15.1 yrs[/TD]
[TD="align: right"]26.1 yrs[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]125,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]122,500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]126,175[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]30,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1109[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]33,675[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]24%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]92,500[/TD]
[TD="align: right"]4.5%[/TD]
[TD="align: right"]800[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]469[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]64[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 18[/TD]
[TD="align: right"] -[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 809[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]718[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]82[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]150,660[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]600[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2,506[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]27,036[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]70,219[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]7,085[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]63,134[/TD]
[TD="align: right"]6.4%[/TD]
[TD="align: right"]0.65%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]12.8 yrs[/TD]
[TD="align: right"]15.5 yrs[/TD]
[TD="align: right"]21 yrs[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]289,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]283,220[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]291,717[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]65,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2809[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]73,497[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]23%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]218,220[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]1,950[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,171[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]156[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 42[/TD]
[TD="align: right"] 35[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 1,869[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,661[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]289[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]338,179[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]4,635[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]49,825[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]128,317[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]13,064[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]115,253[/TD]
[TD="align: right"]6.6%[/TD]
[TD="align: right"]0.69%[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]85%[/TD]
[TD="align: right"]12.2 yrs[/TD]
[TD="align: right"]15.8 yrs[/TD]
[TD="align: right"]25.2 yrs[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]450,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]441,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]454,230[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]120,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]4064[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]133,230[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]27%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]321,000[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]2,650[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,723[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]212[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 66[/TD]
[TD="align: right"] -[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 2,911[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2,344[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]306[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]511,240[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]3,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]5,412[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]53,102[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]190,040[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]14,113[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]175,927[/TD]
[TD="align: right"]5.8%[/TD]
[TD="align: right"]0.60%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]88%[/TD]
[TD="align: right"]13.9 yrs[/TD]
[TD="align: right"]27.4 yrs[/TD]
[TD="align: right"]26.6 yrs[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]150,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]147,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]151,410[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1357[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]54,410[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]34%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]97,000[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]950[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]521[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]76[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 22[/TD]
[TD="align: right"] 55[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 970[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]855[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]95[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]151,410[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]601[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]47,227[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]47,227[/TD]
[TD="align: right"]5.8%[/TD]
[TD="align: right"]0.65%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]12.9 yrs[/TD]
[TD="align: right"]23.3 yrs[/TD]
[TD="align: right"]21.2 yrs[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]415,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]406,700[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]418,901[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]4399[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]62,201[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]12%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]356,700[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]2,550[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,915[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]204[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 61[/TD]
[TD="align: right"] -[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 2,684[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2,504[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]46[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]418,901[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]1,200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,664[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]41,463[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]41,463[/TD]
[TD="align: right"]6.1%[/TD]
[TD="align: right"]0.63%[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"]98%[/TD]
[TD="align: right"]13.3 yrs[/TD]
[TD="align: right"]35.4 yrs[/TD]
[TD="align: right"]26.9 yrs[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]365,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]357,700[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]368,431[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]3904[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]60,731[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]14%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]307,700[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]2,350[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,652[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]188[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 54[/TD]
[TD="align: right"] 50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 2,361[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2,240[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]110[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]368,431[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,463[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]42,551[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]42,551[/TD]
[TD="align: right"]6.2%[/TD]
[TD="align: right"]0.66%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]12.7 yrs[/TD]
[TD="align: right"]24.2 yrs[/TD]
[TD="align: right"]26.4 yrs[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]180,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]176,400[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]181,692[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1954[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]55,292[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]28%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]126,400[/TD]
[TD="align: right"]5.0%[/TD]
[TD="align: right"]1,750[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]679[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]140[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 26[/TD]
[TD="align: right"] 175[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 1,164[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,217[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]533[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]181,692[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]722[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]46,575[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]46,575[/TD]
[TD="align: right"]8.9%[/TD]
[TD="align: right"]0.99%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]8.4 yrs[/TD]
[TD="align: right"]7.3 yrs[/TD]
[TD="align: right"]22.7 yrs[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]325,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]318,500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]328,055[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]50,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]3675[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]59,555[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]16%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]268,500[/TD]
[TD="align: right"]5.5%[/TD]
[TD="align: right"]2,100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,525[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]168[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 48[/TD]
[TD="align: right"] 90[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"] 2,102[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2,105[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]-5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]328,055[/TD]
[TD="align: right"]3.0%[/TD]
[TD="align: right"]6,500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1,303[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]43,079[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]43,079[/TD]
[TD="align: right"]6.1%[/TD]
[TD="align: right"]0.66%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]12.7 yrs[/TD]
[TD="align: right"]52.5 yrs[/TD]
[TD="align: right"]25.9 yrs[/TD]

</tbody>
List

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=C3*$D$1[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=D3*$E$1[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=MAX(2*SUM(-PMT(K3/12,360,D3-F3,0),P3:Q3,R3/12),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=SUM(E3:E3)-J3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=MAX(IFERROR((D3-J3)/D3,0),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=IF(OR(D3=0,D3="",SUM(D3:D3)<f3< font="">),0,SUM(D3:D3)-F3</f3<>)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M3[/TH]
[TD="align: left"]=-PMT($K3/12,360,J3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T3[/TH]
[TD="align: left"]=SUM(M3:Q3,S3)+R3/12[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U3[/TH]
[TD="align: left"]=L3-T3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y3[/TH]
[TD="align: left"]=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%[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Z3[/TH]
[TD="align: left"]=MAX(0,V3*(1-7%)-(D3+X3-Y3*4))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AA3[/TH]
[TD="align: left"]=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
)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AB3[/TH]
[TD="align: left"]=MAX(0,15%*(V3*(1-7%)-D3-X3)+Z3*7.5%+Y3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AC3[/TH]
[TD="align: left"]=AA3-AB3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AD3[/TH]
[TD="align: left"]=IFERROR((L3-O3-P3-Q3-R3/12-S3)*12/D3,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AE3[/TH]
[TD="align: left"]=IFERROR(MAX(0,L3)/D3,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AF3[/TH]
[TD="align: left"]=IFERROR(U3*12/H3,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AG3[/TH]
[TD="align: left"]=IFERROR(T3/L3,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AH3[/TH]
[TD="align: left"]=IFERROR(MAX(ROUNDUP((D3/L3)/12,1),0)&" yrs","")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AI3[/TH]
[TD="align: left"]=IFERROR(MAX(ROUNDUP(SUM(H3)/(L3*12-SUM(M3,O3:Q3,S3)*12-R3),1),0) &" yrs","")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AJ3[/TH]
[TD="align: left"]=IFERROR(MAX(ROUNDUP(NPER(K3/12,-M3-$N3,$J3)/12,1),0) &" yrs","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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