Question

Barium Inc. wants to set up a private cemetery business. The project will have sales of $500,000 and operating expenses of $300,000 in its first year. Sales are expected to grow at 4% per year for 10 years, while operating expenses are expected to grow at 3.5% per year for the same time period. This project would require an initial investment in Land of $1,000,000 and an initial investment in Equipment of $200,000. The Land is not depreciable, and the Equipment will be depreciated straight-line to zero over the 10-year life of the project. The Land will be sold at no gain for $1,000,000 at the end of the project and Equipment will be sold at the end of the project for a gain of $50,000. Barium’s income tax rate is 20% and its discount rate is 9%.

Construct an Excel spreadsheet to calculate the project’s NPV and IRR.

Answer 1

Year 6 9 10 Particulars Annual Sales Revenue 520,000.00 $540,800.00 $ 562,432.00 $584,929.28 $608,326.45 $632,659.51 $657,965.89 $684,284.53 $711,655.91 500,000.00 $ Less 300,000.00 310,500.00$321,367.50 $ 15,000.00 $ 185,000.00 $ 37,000.00 $ 148,000.00 $ 15.000.00$ 163,000.00 $ 332,615.36 $344,256.90 $356,305.89$368,776.60$381,683.78$395,042.71$408,869.21 15,000.00 15,000.00 15,000.00 214,816.64$225,672.38$237,020.56 $248,882.91$261,282.11 $274,241.81$287,786.70 42,963.33 45,134.48 47,404.11 49,776.58 52,256.42 $ 54,848.36 57,557.34 171,853.31$180,537.90$189,616.45$199,106.33 $209,025.69 $219,393.45$230,229.36 15,000.00 15,000.00 15,000.00 15,000.00 15,000.00 15,000.00 15.000.00 186,853.31 $195,537.90 $204,616.45$214,106.33 $224,025.69 $234,393.45$245,229.36 Operating expenses Less: Depreciation Profit Before Tax $ 15,000.00 15,000.00$ 194,500.00 $204,432.50 $ 38,900.00 40,886.50 155.600.00$163.546.00$ 15,000.00 15,000.00 $ 170,600.00 $178,546.00 $ $ 15,000.00$ 15.000.00$ 15,000.00 $ 15,000.00 $ $ Income Tax @20% $ Net Income After Tax $ Add: Depreciation Annunal Cash Flow $ $

ВВ ВЕ ВК ВА Вс BD BF BG ВН BI ВJ BL BM PVF @9% Year PV 1(1,200,000.00) 0.917 $ PVF @10%PV Now interpollate the situation if Discount rate increase by 1% ( from 9 % to 10%) NPV will decrease by 59141.59 (74629.3-15487.71) Cash Flow 1(1,200,000.00) 0.909 $ 0.826 $ 140,991.74 0.751 $ 134,144.25 0.683 127,623.32 0 $ (1,200,000.00) 163,000.00 149.541.28 148,181.82 0.842 $ 143,590.61 0.772 $ 170,600.00 but we have to decrase NPV at 0 bcz at IRR NPV is 0 3 178.546.00 137,870.27 0.708 $ 132,371.60 74629.3 1 4 186,853.31 So 0.650 $ 195,537.90 121,413.65 0.564 115,500.65 0.513 109,870.40 0.621 5 127,086.22 59141.59 0.596 $ 122,006.10 6 204,616.45 0,547 $ 0.502 $ 7 214,106.33 117.123.49 1.28 0.467 $ 0.424 $ So if Exsting discounting rate increased by 1.26% NPV will 0 224,025.69 8 112,430.94 104,509.64 0.460 $ So IRR is 9+1.28= 10.28 % 9 234,393.45 107.921.26 99,405.70 0.422$ 0.386 $ 0.386 10 245,229.36 103,587.53 94,546.53 Salvage va $ 0.422 $ 50,000.00 21,100.00 19.300.00 $ Net present value 74,629.30 15,487.71