Here is what I am trying to do. I have some land that I want to build 6 rental properties on it that will maximize my rental income. I have 5 different homes I can choose from and each have different rental amounts. I could choose the home that would give the most rent and build 6 of those, but I would like some variety. Is there a way in excel to help me to find the best option? Thank you for your help.
Finding the best option
- Thread starter nkolbaba
- Start date
Nkolbaba
The best option will be the scenario which has the Highest NPV
This is going to be the option that the 6 properties have the highest individual NPV
Anything after this will result in a reduction away from the Highest NPV
So The next best option will be 5 of the best and 1 of the second best NPV's
3rd best will be 4 of the Best NPV and 2 of the second Best NPV
etc
So the issue becomes how much "Variety" can you afford/do you require?
If you require to use 2 different designs then 3 of each of those will give you the best NPV
If you require to use 3 different designs then 2 of each of those will give you the best NPV etc
If you want 5 different designs then the top 5 NPV's and the top 1 NPV twice will work
Notice No Excel so far
To calculate the NPV's you will need the purchase/construction cost, rental payments, operating costs and any other expected income/costs during the life for say 10 or 20 years. You should also need to estimate the expected increase in costs /rental returns over the life of the project (1 design may have higher Operating costs or higher rental increases than the others and that should be modelled)
Put all that into a small spreadsheet and calculate the net cash flows each year
Then use the Excel NPV() function to calculate the NPV. You will need to assume an IRR (Internal Rate of Return, that is the Return you will be happy to receive on your money (typically 10 or 15%), as the NPV is the amount of money in Todays terms that the project makes in excess of the IRR.
As always with introducing people to NPV you should read:
http://www.tvmcalcs.com/blog/comments/the_npv_function_doesnt_calculate_net_present_value/
The best option will be the scenario which has the Highest NPV
This is going to be the option that the 6 properties have the highest individual NPV
Anything after this will result in a reduction away from the Highest NPV
So The next best option will be 5 of the best and 1 of the second best NPV's
3rd best will be 4 of the Best NPV and 2 of the second Best NPV
etc
So the issue becomes how much "Variety" can you afford/do you require?
If you require to use 2 different designs then 3 of each of those will give you the best NPV
If you require to use 3 different designs then 2 of each of those will give you the best NPV etc
If you want 5 different designs then the top 5 NPV's and the top 1 NPV twice will work
Notice No Excel so far
To calculate the NPV's you will need the purchase/construction cost, rental payments, operating costs and any other expected income/costs during the life for say 10 or 20 years. You should also need to estimate the expected increase in costs /rental returns over the life of the project (1 design may have higher Operating costs or higher rental increases than the others and that should be modelled)
Put all that into a small spreadsheet and calculate the net cash flows each year
Then use the Excel NPV() function to calculate the NPV. You will need to assume an IRR (Internal Rate of Return, that is the Return you will be happy to receive on your money (typically 10 or 15%), as the NPV is the amount of money in Todays terms that the project makes in excess of the IRR.
As always with introducing people to NPV you should read:
http://www.tvmcalcs.com/blog/comments/the_npv_function_doesnt_calculate_net_present_value/