I have a pretty comprehensive cash flow that has two requirements for approval of the purchase. (1) is that the monthly ROI must be 6% or above (2) is that the time to recoup the initial capital outlay takes less than 30% of the term
I figured out how to create a Data Table that can autocalculate where the rent needs to be to hit the (1) assumption of 6% per month as stated above. I am having a problem if I try to add in the second variable. Anyone want to help me figure this out?
I've uploaded images. Hopefully those can give a clear enough breakdown.
Image one is the Final Review Page. On it you will see "Months to Recoup Investment" highlighted in green as well as a table with colors in it and will see in Year 2 this particular investment gets to 6.55% per month ROI. Lastly you will see at the top that this particular investment is "Approved"
The second image shows my dashboard page where the initial data is aggregated. There are two green answers shown. The first is for $13,416 and the second is for $13,850. What is happening here is that the first answer is the answer based on the client's initial valuation. The $13,850 is where it could be to have the property still meet the appropriate conditions. This $13,850 number is based on a data table (image 3) that is calculating the % return based on different rent amounts and is pulling from N21 on that page. I then wrote a formula so that if the data table answer is in the 6% range to choose the highest value (image 4) which in this case is the $13,850 answer. In this example it just so happens that the second investment requirement of less than 30% time to recoup their cash works with that $13,850 number, but it doesn't always work. I want to be able to add this to the data table calculations, or figure out a way for the program to go down the list until both are hit.
Any thoughts??? End user is not a sophisticated Excel user so if there is a way to avoid VBA that is ideal. Also would prefer that it auto runs similar to the data table.
Any ideas, inputs, suggestions are immensely welcome!!!
Kindly,
Aynsley
I figured out how to create a Data Table that can autocalculate where the rent needs to be to hit the (1) assumption of 6% per month as stated above. I am having a problem if I try to add in the second variable. Anyone want to help me figure this out?
I've uploaded images. Hopefully those can give a clear enough breakdown.
Image one is the Final Review Page. On it you will see "Months to Recoup Investment" highlighted in green as well as a table with colors in it and will see in Year 2 this particular investment gets to 6.55% per month ROI. Lastly you will see at the top that this particular investment is "Approved"
The second image shows my dashboard page where the initial data is aggregated. There are two green answers shown. The first is for $13,416 and the second is for $13,850. What is happening here is that the first answer is the answer based on the client's initial valuation. The $13,850 is where it could be to have the property still meet the appropriate conditions. This $13,850 number is based on a data table (image 3) that is calculating the % return based on different rent amounts and is pulling from N21 on that page. I then wrote a formula so that if the data table answer is in the 6% range to choose the highest value (image 4) which in this case is the $13,850 answer. In this example it just so happens that the second investment requirement of less than 30% time to recoup their cash works with that $13,850 number, but it doesn't always work. I want to be able to add this to the data table calculations, or figure out a way for the program to go down the list until both are hit.
Any thoughts??? End user is not a sophisticated Excel user so if there is a way to avoid VBA that is ideal. Also would prefer that it auto runs similar to the data table.
Any ideas, inputs, suggestions are immensely welcome!!!
Kindly,
Aynsley
