Can Excel choose the highest value between variables between defined tables (challeging)

[Neo4u]

Hi Guys,

I am dealing with a tremendous issue and I am walking with this idea for months and it is quite challenging also. Oke here is the deal, it is about Math and Finance.

Suppose I have three Real Estates A,B and C. See inserted picture for the table:

If I buy 1 Real Estate A I can buy it for $1,000 and get an periodic income of $100. So my ROI (Return On investment) is (monthly income/Purchase price) = 10% and if I buy a second Real Estate Type A than the purchase price becomes
$ 1,100 and the income stays fixed for Real Estate namely $100, so the ROI becomes than 9,1% and so on..the same goes for the other Real Estate B en C.

The Case is the following, let say I got an Investment budget of $10,000 I want to invest that into the Real Estate with the highest possible Income, can Excel tell me eventually you have to buy 5 Real Estate A en 1 Real Estate C (marked in table in blue) for the optimal ROI.

Here are some statistics for this given issue:

Total Cost Real Estate Investment A = 1,000 + 1,100 + 1,200 + 1,300 + 1,400 = $ 6,000
Total Cost Real Estate Investment C = 4,000 + = $ 4,000

Total Investment Cost= $ 10,000

Total Income Real Estate A = 5 * 100 = $ 500
Total Income Real Estate C = 1 * 300 = $ 300

Total Income = $ 800

So my Overall ROI becomes= Total Income/ Total Invesment Cost = $800/$10,000 = 8%

Note: The prices of the real estate is a linear function: Cost A = Initial price + 10% *initial price * (n-1)

 

[paddydive]

Hi Neo,

I think all you need is Goal Seek and Scenario for this.. You can check video for this on Scenario Analysis in Excel - YouTube
create your own scenario and I hope it will work for you...

 

[Neo4u]

Thank you Paddydive for the comments , i have taken your advice and these are cool features, but i think that it will not work with those: Or i may overlook something.. So what i did, I tried to make another approach on how to get the problem more clearer.

see the excel file below:

So this is the first thing he must do,
Step 1: Fill in the amount you want to invest in to get the optimal ROI by investing in Real Estate A en B
Step 2: Determine which ones have the highest ROI value and what is the next highest ROI Value and so on...till you run out of your investment money
Step 3: The Real Estate's has been identified and from that you can calculate the total price and other calculations
Step 4: Summerise in clear language you need to buy x Real Estate A en y Real estate B

In this example i need 5 Real Estate Type A buildings and 1 Real Estate type B building:

Now when i have more cash, I want to invest again and buy more buildings so I will fill in again my investment amount of let's say $11,000:

See herebelow

Now the tricky part:

Step 1: Ignore the already used entries from the table (marked in red)
step 2: determine the highest ROI value in from both the tables (Real estate A en Real estate B)
etc..
So in this case he also have to remember what i have bought and somehow keep that in mind.

Hope this gets a bit clearer what my problem is...hope to get some input on this...