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?

Chapter 7 Problem 12 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

Part A, B and C)

The following table shows the calculations for Annual After-Tax Cash Flow, NPV and IRR.

Notes:

1) NPV is calculated with the use of following formula:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+Discount Rate)^1 + Cash Flow Year 2/(1+Discount Rate)^2 + Cash Flow Year 3/(1+Discount Rate)^3 + Cash Flow Year 4/(1+Discount Rate)^4 + Cash Flow Year 5/(1+Discount Rate)^5 + Cash Flow Year 6/(1+Discount Rate)^6 + Cash Flow Year 7/(1+Discount Rate)^7 + Cash Flow Year 8/(1+Discount Rate)^8 + Cash Flow Year 9/(1+Discount Rate)^9 + Cash Flow Year 10/(1+Discount Rate)^10

2) IRR is calculated with the use of following function:

IRR = IRR(Values,Guess) where Values = Cash Flows from Year 0 to Year 10 and Guess is Optional.

_______

Part D)

The internal rate of return will remain the same as discount rate has no use in the determination of IRR and cash flows will remain constant as determined in Part A.

_______

Part E)

The revised IRR is calculated with the use of following table:

Notes:

IRR is calculated with the use of following function:

IRR = IRR(Values,Guess) where Values = Cash Flows from Year 0 to Year 10 and Guess is Optional.

Year 0 1 2 3 4 5 6 7 8 9 10 Initial cost 350,000 Annual depreciation 50,000.00 50,000.00 50,000.00 50,000.00 50,000.00 50,000.00 50,000.00 EBIT 28,000.00 28,840.00 29,705.00 30,596.00 31,514.00 32,460.00 33,433.00 34,436.00 35,470.00 36,534.00 Less Taxes 10,640.00 10,959.20 11,287.90 11,626.48 11,975.32 12,334.80 12,704.54 13,085.68 13,478.60 13,882.92 EAT 17,360.00 17,880.80 18,417.10 18,969.52 19,538.68 20,125.20 20,728.46 21,350.32 21,991.40 22,651.08 Add Depreciation 50,000.00 50,000.00 50,000.00 50,000.00 50,000.00 50,000.00 50,000.00 0.00 0.00 0.00 Annual After-Tax Cash Flow -350,000 67,360.00 67,880.80 68,417.10 68,969.52 69,538.68 70,125.20 70,728.46 21,350.32 21,991.40 22,651.08 Discounted Cash Flow -350,000 61,236.36 56,099.83 51,402.78 47,107.11 43,178.05 39,583.85 36,294.88 9,960.08 9,326.50 8,732.97 Net present value @ 10% $12,922.42 Internal rate of return 10.99%

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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