Question
3 views

Solve the following problem using excel: The program is estimated to require a 10-year commitment to fully achieve the desired result. Analyze the following information using both the Net Present Value (NPV) method and the Internal Rate of Return (IRR) method.
(I have already worked and solved this problem, however as it is complex and contains a large number of values to be calculated, I want to be sure that I applied all appropriate concepts correctly.)
I have attached the directions and problem in a screenshot.

check_circle

Step 1

Initial investment = purchase cost = 210,000

Annual benefit will be calculated in three steps:

1. Incremental annual property taxes
2. Annual salaries & benefits costs
3. Annual program operating costs

Step 2

Let's handle them one by one:

Annual incremental property taxes:

Current taxable value = 12,000,000,000

Millage rate= 7.2 mils

1 mill = 1/1000

Hence, current annual property tax = 12,000,000,000 x 7.2 x 1/1000 = 86,400,000

Improvement in annual property tax in ordinary course = 5%

Increase due to addition of features = 2%

And subsequent increase = 1/10 of the value of previous year

hence, incremental property taxes in year 1  = 86,400,000 x 5% x (1+2%) x 1/10 = \$ 440,640

Hence incremental property tax in year 2 = 440,640 x (1 + g)

Hence incremental property tax in year 3 = 86,400 x (1 + g)2

Hence incremental property tax in year 3 = 86,400 x (1 + g)N-1

Please see the table below for the incremental property taxes for each of the year:

 Year Initial investment Annual incremental property taxes I A 0 1 440,640 2 484,704 3 533,174 4 586,492 5 645,141 6 709,655 7 780,621 8 858,683 9 944,551 10 1,039,006
Step 3

Let's now calculate annual salary and benefits costs.

Year 1 cost is 243,500 and the same is expected to increase every year by g = 2.5%

Hence, year 2 cost will be = 243,500 x (1+g) = 243,500 x (1 + 2.5%)

Year 3 cost will be = 243,500 x (1+2.5%) 2 and so on

Year N cost will be = 243,500 x (1+2.5%)N-1

Please see the table below for annual salary & benefit costs:

 Year Salary & Benefit costs N = 243,500 x (1 + g)N-1 1 243,500 2 249,588 3 255,827 4 262,223 5 268,778 6 &nb...

### Want to see the full answer?

See Solution

#### Want to see this answer and more?

Solutions are written by subject experts who are available 24/7. Questions are typically answered within 1 hour.*

See Solution
*Response times may vary by subject and question.
Tagged in 