Question

You want to purchase agricultural land and enrolling the land in a conservation program offered by your state water and soil resources board. This program pays landowners not to farm the land for a specified period of time. A requirement of the program is that you establish vegetation on the land that would benefit wildlife. The program provides cost-share funding to help landowners establish wildlife habitat. Your estimate that the costs and returns associated with purchasing the land, enrolling it in the program, and establishing wildlife habitat to be as follows:

- $1,000/acre purchase price of land, paid back annually in equal installments over 9 years @ 6 percent interest (the first payment occurs one year after the loan is established).

- $200/acre consulting costs immediately to help develop a management plan.

- $125/acre native prairie establishment costs incurred one year after purchase.

- $75/acre prairie maintenance costs in years 1, 2, 3 and 5.

- $50/acre prairie maintenance costs in year 4, and years 6-9.

- 10 annual easement payments (revenue) of $175/acre each year, beginning immediately.

- $3/acre/year in property taxes and liability insurance, beginning immediately and continuing each year you own the property (including the year you sell it).

- Your ARR is 3 percent.

- Sell the land once enrollment in the conservation program ends (in year 10) for $2,100/acre.

Prepare Cash Flow and Discounted Cash Flow Tables:

1) What is the undiscounted annual payment you will make each of 9 years to pay off the loan for the land?

2) What is the sum of all undiscounted costs for this project?

3) What are the total discounted returns for the project in year 9?

4) What is the discounted net return of this project in year 5?

5) What is the net present value (NPV) of this project?

6) What is the internal rate of return (IRR) for this project?

7) What is the benefit-cost ratio for this project?

Answer 1

1. For calculating the equal annual installment,

Put in excel this formula EMI =PMT(rate,nper,pv)

Where, PMT = function of excel

Rate	6%
Nper	9
PV	$ 1,000.00
Equal annual installment	-147.02

Mathematical formula

Equal annual installment = [P x R x (1+R)^N]/[(1+R)^N-1]

where P stands for the loan amount or principal, R is the interest rate per year and N is the number of installments.

Here putting P = 1000, R = 0.06 and N = 9

We will get Equal annual installment = $147.02

2. Total cost is calculated as shown in the table

	1	2	3	4	5	6	7	8	9	10	Total
Equal Annual Payments		147	147	147	147	147	147	147	147	147	1323
consulting costs	200										200
native prairie establishment costs		125									125
prairie maintenance costs	75	75	75	50	75	50	50	50	50		550
	3	3	3	3	3	3	3	3	3	3	30
Total Cost	278	350	225	200	225	200	200	200	200	150	2228

So Total Cost $228

3. According to the calculation shown

Revenue	175	175	175	175	175	175	175	175	175	175
Selling price										2100
Total undiscounted return (CF)	175	175	175	175	175	175	175	175	175	2275
Discounted Total Return in year 9
	-1	-2	-3	-4	-5	-6	-7	-8	0	1
Discounted Return in year 9	180.25	185.6575	191.2272	196.964	202.873	208.9592	215.2279	221.6848	175	2208.738	3986.581

For discounting, the formula is used as DCF = CF/(1+r)^p

Where r= ARR = 3%, p = discounting value power as shown in calculation

Hence in year 9, total discounted return = $3986.6

4. For net return

Net return	-103	-175	-50	-25	-50	-25	-25	-25	-25	2125
In year 5
p	-1	-2	-3	-4	0	1	2	3	4	5
Discounted Net Return in year 5	-106.09	-185.658	-54.6364	-28.1377	-50	-24.2718	-23.5649	-22.8785	-22.2122	1833.044	1315.595

Net discounted return in year 5 = $1315.6

5. For NPV,

Net return	-103	-175	-50	-25	-50	-25	-25	-25	-25	2125
p	1	2	3	4	5	6	7	8	9	10
PV	-100	-164.954	-45.7571	-22.2122	-43.1304	-20.9371	-20.3273	-19.7352	-19.1604	1581.2	1124.986

NPV= $1125

6. Putting IRR formula and values of Net return in excel, we get IRR of 22%

7. Total discounted value of cost = $1930

NPV = $1125

So Benefit Cost Ratio= NPV/Total Cost = 0.582

	1	2	3	4	5	6	7	8	9	10	Total
Equal Annual Payments		147	147	147	147	147	147	147	147	147	1323
consulting costs	200										200
native prairie establishment costs		125									125
prairie maintenance costs	75	75	75	50	75	50	50	50	50		550
	3	3	3	3	3	3	3	3	3	3	30
Total Cost	278	350	225	200	225	200	200	200	200	150	2228
	1	2	3	4	5	6	7	8	9	10
Discounted value of total cost	269.9029	329.9086	205.9069	177.6974	194.087	167.4969	162.6183	157.8818	153.2833	111.6141	1930.397
Revenue	175	175	175	175	175	175	175	175	175	175
Selling price										2100
Total undiscounted return	175	175	175	175	175	175	175	175	175	2275
Discounted Total Return in year 9
p	-1	-2	-3	-4	-5	-6	-7	-8	0	1
Discounted Return in year 9	180.25	185.6575	191.2272	196.964	202.873	208.9592	215.2279	221.6848	175	2208.738	3986.581
Net return	-103	-175	-50	-25	-50	-25	-25	-25	-25	2125
In year 5
p	-1	-2	-3	-4	0	1	2	3	4	5
Discounted Net Return in year 5	-106.09	-185.658	-54.6364	-28.1377	-50	-24.2718	-23.5649	-22.8785	-22.2122	1833.044	1315.595
p	1	2	3	4	5	6	7	8	9	10
PV	-100	-164.954	-45.7571	-22.2122	-43.1304	-20.9371	-20.3273	-19.7352	-19.1604	1581.2	1124.986
										Benefit cost ratio	0.582774