So I am creating a portfolio model for real estate and there is 4 possible ways to purchase a property. Creating 4 different possible cash flows. I need to pull the cash flow based on the way I am purchasing a property and based on which year it was purchased.
So for example:
Year 1 Year 2 Year 3 Year 4
Purchase Scenario 1 $1000 $1250 $1500 $1750
Purchase Scenario 2 $2000 $2250 $2500 $2750
Purchase Scenario 3 $3000 $3250 $3500 $3750
Purchase Scenario 4 $4000 $4250 $4500 $4750
I understand how to use index to pull a value in a certain purchase scenario in a certain row in a certain column. My issue is if a property is bought in the second year i need to be able to pull the cash flow from the second year not the first.
Lets say I buy a property using the "Purchase Scenario 2" in the second year. I need excel to pull $2250 and put that in the designated cell. I need this to work based on the date I enter as the purchase date. So if the first year of purchases starts on 1/1/2017 and I enter 1/1/2018 as the purchase date I need the cash flow from year 2 because I am purchasing the property in year 2.
Basically if I designate the property as "Purchase Scenario 3" and I am purchasing it in "Year 2" I need excel to automatically pull $3250 (based on the table I created above).
So for example:
Year 1 Year 2 Year 3 Year 4
Purchase Scenario 1 $1000 $1250 $1500 $1750
Purchase Scenario 2 $2000 $2250 $2500 $2750
Purchase Scenario 3 $3000 $3250 $3500 $3750
Purchase Scenario 4 $4000 $4250 $4500 $4750
I understand how to use index to pull a value in a certain purchase scenario in a certain row in a certain column. My issue is if a property is bought in the second year i need to be able to pull the cash flow from the second year not the first.
Lets say I buy a property using the "Purchase Scenario 2" in the second year. I need excel to pull $2250 and put that in the designated cell. I need this to work based on the date I enter as the purchase date. So if the first year of purchases starts on 1/1/2017 and I enter 1/1/2018 as the purchase date I need the cash flow from year 2 because I am purchasing the property in year 2.
Basically if I designate the property as "Purchase Scenario 3" and I am purchasing it in "Year 2" I need excel to automatically pull $3250 (based on the table I created above).