The land costs $100,000. Over the seven year period, the price of the land will be subject to a cumulative increase of 5% (meaning that you can sell it for $105,000 in year 7). Well call this value-driver LandPriceGrowth.

Assume for now that there is no investment in depreciable PP&E.

During years 1 through 7, you will incur operating costs of $50,000 each year in order to maintain the agave ranch.

You will do this project as part of your own LLC that produces a constant stream of revenues from Tic-Tac consulting. Hence, any negative earnings would be immediately offset by lower overall tax bills for your company. The tax rate is 28%. Note that any increases in land price growth contribute to the firms earnings, and are taxed at the same tax rate as other earnings. The entire price or value increase is calculated upon sale and is taxable in the year of sale (year 7 in our case).

You know that you will be able to harvest and sell 10,000 agaves. The current sales price (as of December of year 0) for an agave is $100. However, you do not know with certainty what the price will be at the end of year 7. Initially you assume that the price stays at $100 until you harvest the agaves. Model this as if there is a $0 increase in the agave price each year, and set up your model so that you can chance these yearly increases. For example, if you enter that the price increases for year 1,,7 are -1,1,-1,1,-1,1,2, respectively, then the sales price at the end of year 7 is $102.

Your friend Dwayne J. also offers to harvest all of the agaves for you as part of his exercise routine, so there are no additional harvesting costs. You use a discount rate (cost-of-capital) of 20%

1) Build a spreadsheet model in EXCEL where you show how you find the project FCFs, the discounted FCFs, the NPV, and the IRR of the project.

2) Calculate PV current and future CF (PVCAFCF) for each year. For year 7 it equals the project FCF of year 7. For year 6 it equals the project FCF of year 6 plus the PVCAFCF of year 7 multiplied by 1/(1+r). Similarly, for year 5 it equals the project FCF of year 5 plus the PVCAFCF of year 6 multiplied by 1/(1+r). Continue this iteratively until you create the PVCAFCF for year 0. If you did everything right, this should equal the NPV youve already calculated!