I'm new here and it may be a little more difficult since I'm not able to attach the subject workbook.
I'd like to simplify my formulas if possible.
The workbook is a real estate tool to plan out investments. On my main tab/sheet, I have a simple table of years 0 - 10 and a list of purchases, which are generically named "Prop (1)", "Prop (2)", etc. Within the table, I plot out the plan using representing letters: P= purchase, R= rent, S=sell. I can elaborate on this if it helps.
Below this table, I have interactive valuations of the different scenarios that I plot, which correspond to the year columns. The row categories include cash flow, profit/loss, asset (value), liability (loans), equity (asset-liability) and cash-on-hand.
Here's where it gets tricky. I have sheets that correspond to each potential investment and they are named accordingly: Prop (1), Prop (2), etc. On each sheet, I have data, including purchase breakdown, rental income/expense, and future resale.
Hopefully, this all makes sense so far. The way it's currently set up, here's my formulas for each summary set:
Profit/Loss: =IF(C2="P",-'PROP (1)'!$B$18,IF(C2="R",'PROP (1)'!$B$10,IF(C2="S",'PROP (1)'!$B$21,0)))+IF(C3="P",-'PROP (2)'!$B$18,IF(C3="R",'PROP (2)'!$B$10,IF(C3="S",'PROP (2)'!$B$21,0)))+IF(C4="P",-'PROP (3)'!$B$18,IF(C4="R",'PROP (3)'!$B$10,IF(C4="S",'PROP (3)'!$B$21,0)))
Cash Flow: =IF(C$2="",0,'PROP (1)'!$B10)+IF(C$3="",0,'PROP (2)'!$B10)+IF(C$4="",0,'PROP (3)'!$B10)
Asset: =IF(C2="",0,FV('PROP (1)'!$C$21,COUNTA(C$2:$C2)-1,0,-'PROP (1)'!$B$13,1))+IF(C3="",0,FV('PROP (2)'!$C$21,COUNTA(C$3:$C3)-1,0,-'PROP (2)'!$B$13,1))+IF(C4="",0,FV('PROP (3)'!$C$21,COUNTA(C$4:$C4)-1,0,-'PROP (3)'!$B$13,1))
Liability: =IF(C2="",0,FV('PROP (1)'!$C$3/12,(COUNTA($C$2:C2)-1)*12,'PROP (1)'!$B$3,-'PROP (1)'!$B$17))+IF(C3="",0,FV('PROP (2)'!$C$3/12,(COUNTA($C$2:C2)-1)*12,'PROP (2)'!$B$3,-'PROP (2)'!$B$17))+IF(C4="",0,FV('PROP (3)'!$C$3/12,(COUNTA($C$2:C2)-1)*12,'PROP (3)'!$B$3,-'PROP (3)'!$B$17))
Here's a sample property sheet:
On the main worksheet, the formulas are repeated for each "investment". I'd like to have an array or index/match or sumproduct formula that can check the column 'A' names and pull the data from the corresponding "Prop (x)" sheets. The formula doesn't need to be able to check the actual sheet names, as I'm fine with manually added the sheetname in cell A1 for each case.
Note: You'll notice the Asset and Liability equations include the future value (FV) formula. I combined this with the COUNTA to project value over time. The mortgage balance in year 5 will not be the starting value.
The reason for my request is if I add Prop (4), or let's say 6 more investments, it's not practical to add the repeating formulas for every sheet. Plus, my formulas are unwieldy and I'm sure there's a better way to put this together.
Thanks all.
I'd like to simplify my formulas if possible.
The workbook is a real estate tool to plan out investments. On my main tab/sheet, I have a simple table of years 0 - 10 and a list of purchases, which are generically named "Prop (1)", "Prop (2)", etc. Within the table, I plot out the plan using representing letters: P= purchase, R= rent, S=sell. I can elaborate on this if it helps.
Below this table, I have interactive valuations of the different scenarios that I plot, which correspond to the year columns. The row categories include cash flow, profit/loss, asset (value), liability (loans), equity (asset-liability) and cash-on-hand.
Here's where it gets tricky. I have sheets that correspond to each potential investment and they are named accordingly: Prop (1), Prop (2), etc. On each sheet, I have data, including purchase breakdown, rental income/expense, and future resale.
Hopefully, this all makes sense so far. The way it's currently set up, here's my formulas for each summary set:
Profit/Loss: =IF(C2="P",-'PROP (1)'!$B$18,IF(C2="R",'PROP (1)'!$B$10,IF(C2="S",'PROP (1)'!$B$21,0)))+IF(C3="P",-'PROP (2)'!$B$18,IF(C3="R",'PROP (2)'!$B$10,IF(C3="S",'PROP (2)'!$B$21,0)))+IF(C4="P",-'PROP (3)'!$B$18,IF(C4="R",'PROP (3)'!$B$10,IF(C4="S",'PROP (3)'!$B$21,0)))
Cash Flow: =IF(C$2="",0,'PROP (1)'!$B10)+IF(C$3="",0,'PROP (2)'!$B10)+IF(C$4="",0,'PROP (3)'!$B10)
Asset: =IF(C2="",0,FV('PROP (1)'!$C$21,COUNTA(C$2:$C2)-1,0,-'PROP (1)'!$B$13,1))+IF(C3="",0,FV('PROP (2)'!$C$21,COUNTA(C$3:$C3)-1,0,-'PROP (2)'!$B$13,1))+IF(C4="",0,FV('PROP (3)'!$C$21,COUNTA(C$4:$C4)-1,0,-'PROP (3)'!$B$13,1))
Liability: =IF(C2="",0,FV('PROP (1)'!$C$3/12,(COUNTA($C$2:C2)-1)*12,'PROP (1)'!$B$3,-'PROP (1)'!$B$17))+IF(C3="",0,FV('PROP (2)'!$C$3/12,(COUNTA($C$2:C2)-1)*12,'PROP (2)'!$B$3,-'PROP (2)'!$B$17))+IF(C4="",0,FV('PROP (3)'!$C$3/12,(COUNTA($C$2:C2)-1)*12,'PROP (3)'!$B$3,-'PROP (3)'!$B$17))
Here's a sample property sheet:
On the main worksheet, the formulas are repeated for each "investment". I'd like to have an array or index/match or sumproduct formula that can check the column 'A' names and pull the data from the corresponding "Prop (x)" sheets. The formula doesn't need to be able to check the actual sheet names, as I'm fine with manually added the sheetname in cell A1 for each case.
Note: You'll notice the Asset and Liability equations include the future value (FV) formula. I combined this with the COUNTA to project value over time. The mortgage balance in year 5 will not be the starting value.
The reason for my request is if I add Prop (4), or let's say 6 more investments, it's not practical to add the repeating formulas for every sheet. Plus, my formulas are unwieldy and I'm sure there's a better way to put this together.
Thanks all.
