Develop a spreadsheet model, and use it to find the project\'s NPV and MIRR Key

Question

Develop a spreadsheet model, and use it to find the project's NPV and MIRR Key Output: NPV - art 1. Input Data (in thousands of dollars) MIRR uipment cost et WC/Sales irst year sales (in units) ales price per unit ariable cost per unit onvariable costs $12,500 5% 1,100 $28.00 $18.00 $1,450 $1,500 40% 12% 2.5% arket value of equipment at Year 4 ax rate ACC nflation Years Accum'd art 2. Depreciation and Amortization Schedul Initial Cost 2 4Depr'n ear 20.0% 32.0% 19.0% 12.0% quipment Depr'n Rate quipment Depr'n, Dollars nding Bk Val: Cost - Accum Dep'rn Equipment art 3. Net Salvage Values, in Year 4 stimated Market Value in Year 4 ook Value in Year 4 xpected Gain or Loss axes paid or tax credit et cash flow from salvage

Explanation / Answer

Soln : Book Value at the end of year 4 = 12500 - 2500-4000-2375-1500 = 2125

Market value = 1500, Loss = 1500-2125 = 625, tax credit = 625*40% = 250

Net cash flow from salvage = -375

Please refer the table :

MIRR = 23%

Years 0 1 2 3 4 Initial Outlay 12500 Units sold 1100 1199 1307 1425 Sales Price 28 28.7 29.42 30.15 variable costs 18 18.45 18.91 19.38 2772 3097.017 3460.142243 3865.843921 Sales revenue 30800 34411.30 38446.02 42953.82 Variable costs 19800 22121.55 24715.30174 27613.17087 Non variable operating cost 1450 1486.25 1523.41 1561.49 Depreciation 2500 4000 2375 1500 EBIT 7050 6803.50 9832.32 12279.16 Tax @ 40% 2820 2721.40 3932.93 4911.66 PAT 4230 4082.10 5899.39 7367.50 Add back depreciation 2500 4000 2375 1500 Operating Cash flows 6730 8082.10 8274.39 8867.50 Salvage Value -375 Net cash flows -12500 6730 8082.10 8274.39 8492.50 Discount factors 0.89 0.80 0.71 0.64 PV -12500 6008.93 6443.00 5889.55 5397.13 NPV 11238.61

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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