a). Complete the spreadsheet below by estimating the project\'s annual after tax

Question

a). Complete the spreadsheet below by estimating the project's annual after tax cash flow.

b). What is the investment's net present value at a discount rate of 10 percent?
c). What is the investment's internal rate of return?

d). How does the internal rate of return change if the discount rate equals 20 percent?

e). How does the internal rate of return change if the growth rate in EBIT is 8 percent instead of 3 percent?

Facts and Assumptions Equipment initial cost $ $   350,000 Depreciable life yrs.                7 Expected life yrs.               10 Salvage value $ $0 Straight line depreciation EBIT in year 1        28,000 Tax rate 38% Growth rate in EBIT 3% Discount rate 10% Year 0 1 2 3 4 5 6 7 8 9 10 Initial cost       350,000 Annual depreciation    50,000    50,000    50,000    50,000    50,000    50,000    50,000 EBIT    28,000    28,840    29,705    30,596    31,514    32,460    33,433    34,436    35,470    36,534 Net present value @ 10% Internal rate of return

Explanation / Answer

Equipment Initial cost 350000 Depreciation Life 7 years Straight line depreciation Expected Life 10 years Salvage Value 0 EBIT in year 1 28000 Tax rate 38% Growth rate in EBIT 3% Discount rate 10% Year 0 1 2 3 4 5 6 7 8 9 10 EBIT 28000 28840 29705 30596 31514 32460 33433 34436 35470 36534 Tax = 38% 10640 10959 11288 11627 11975 12335 12705 13086 13478 13883 (EBIT - Tax) 17360 17881 18417 18970 19539 20125 20729 21351 21991 22651 Annual Depreciation 50000 50000 50000 50000 50000 50000 50000 After-tax cash flow 67360 67881 68417 68970 69539 70125 70729 21351 21991 22651 Initial Cost -350000 Total Cash Flows -350000 67360 67881 68417 68970 69539 70125 70729 21351 21991 22651 Discount rate = 10% Discount factor =1/(1.10)^year 1 0.909091 0.826446 0.751315 0.683013 0.620921 0.564474 0.513158 0.466507 0.424098 0.385543 Discounted Flow (cash flow * disc factor) -350000 61236.36 56099.83 51402.87 47107.26 43178.14 39583.73 36295.03 9960.217 9326.385 8732.888 Net Present Value at 10% =sum of discounted flows   = 12922.73 Internal rate of return =irr(B2:L20) 11% Answer (d)   IRR will not change as the same is calculated based on the total cash flows (Line 20 in this case) instead of discounted cash flows. It is NPV which changes Discount factor for disc rate of 20% 1 0.833333 0.694444 0.578704 0.482253 0.401878 0.334898 0.279082 0.232568 0.193807 0.161506 Discounted flows (cash flow * disc factor) -350000 56133.33 47139.44 39593.3 33260.87 27946.1 23484.72 19739.1 4965.47 4262.028 3658.241 Net Present Value =sum(discounted flows B33:L33)   = -89817.4 Answer (e ) If growth rate of EBIT is 8%,then Year 0 1 2 3 4 5 6 7 8 9 10 EBIT 28000 30240 32659 35272 38094 41141 44432 47987 51826 55972 Tax = 38% 10640 11491 12410 13403 14476 15634 16884 18235 19694 21269 (EBIT - Tax) 17360 18749 20249 21869 23618 25508 27548 29752 32132 34703 Annual Depreciation 50000 50000 50000 50000 50000 50000 50000 After-tax cash flow 67360 68749 70249 71869 73618 75508 77548 29752 32132 34703 Initial Cost -350000 Total Cash Flows -350000 67360 68749 70249 71869 73618 75508 77548 29752 32132 34703 Internal Rate of Return =IRR(b49:l49) 13%

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.