Excel has hundreds of functions which make life easier for us as the user as we saw with some of the TVM functions in earlier projects. The IF function has the following syntax: =if(condition, entry if true, entry if false). Condition is a mathematical expression that returns true or false. When the condition is true, the second entry is placed in the cell, otherwise the third entry is placed in the cell. Careful specification of conditions such as this is one way of helping to make sure that when in the future you use the model again, it will work correctly.
Project 3 NPV PROJECT 5 NPV EXAMPLE OF INPUTS FOR PROJECT NOTE Your project will require clear inputs, proper referencing and more details than given in the example above Will also need to make a NPV Profile chart/graph Project 3 NPV PROJECT 5 NPV NET PRESENT VALUE WITH EXCEL Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). NPV(rate,value1,[value2],...) The NPV function syntax has the following arguments: Rate Required. The rate of discount over the length of one period. Value1, value2, ... Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income. Value1, value2, ... must be equally spaced in time and occur at the end of each period. NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence. Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored. *Description From https://support.office.com/en-us/article/NPV-function-8672cb67- 2576-4d07-b67b-ac28acf2a568* NET PRESENT VALUE WITH EXCEL Suppose you need $1,000 in one year, $2,000 more in 2 years, and $500 in 3 years. If you can earn 9% on your money, how much do you have to put in the account today to exactly cover these amounts in the future? =NPV(rate,value1,[value2],...) Rate = 0.09 Cash Flow 1(Value1) = 1000 Cash Flow 2(Value2) = 2000 Cash Flow 3(Value3)= 500 =NPV(0.09,1000,2000,500) = $2,986.88 EXAMPLE You are offered an investment that will pay you $50 today, $200 in one year, $400 the next year, $600 the next year, and $800 at the end of the fourth year. You can earn 12% on very similar investments. What is the most you should pay for these cash flows today? *CAREFUL BECAUSE NOW HAVE MONEY IN PERIOD 0* Remember the NPV function in Excel returns the NPV based on a discount rate and a series of FUTURE payments (negative values) and income (positive values) DRAW A TIMELINE! CAPITAL BUDGETING/SPENDING Generally represents spending on fixed assets for a project Most of the time this is made up of the Initial Cost (CF0) as well as any salvage value at the end of the project Sometimes maintenance costs are capital spending that occur during the life of a project, but the vast majority of capital spending will be at the beginning If there is salvage value at the end of the project then there may be tax implications NPV The net present value approach is an intuitive valuation approach to capital budgeting problems. Discounting the after-tax cash flows by the weighted average cost of capital (discount rate) allows managers to determine whether a project will be profitable or not. NPV Rule Positive Accept Major advantages of the NPV approach include the overall usefulness and easy understandability of the figure GENERAL PARTS OF A CAPITAL BUDGETING PROBLEM 1. Identify Relevant Cash Flows (ignore sunk costs!) 2. Compute the Initial Investment (CF0) 3. Compute the After-tax OCFs for each period of the project 4. Compute any additional non-operational CFs such as changes in NWC or after-tax salvage value 5. Estimate the appropriate discount rate 6. Compute NPV or IRR (helps to have a timeline) 7. Decide about accepting or rejecting the project EXAMPLE Your firm is considering building a new brewery. You have paid a consultant $92,000 for a plan. New equipment will cost $420,000. At the conclusion of the project the equipment can be sold for $80,000 (before tax salvage). Depreciation is by the 3-year MACRS method. The brewery should generate additional revenues of $224,000 per year and additional costs of $29,000 per year for 5 years. The NWC needed for the project is $2,000. The firm’s marginal tax rate is 33% and the cost of capital is 16% Your firm is considering building a new brewery. You have paid a consultant $92,000 for a plan. New equipment will cost $420,000. At the conclusion of the project the equipment can be sold for $80,000 (before tax salvage). Depreciation is by the 3-year MACRS method. The brewery should generate additional revenues of $224,000 per year and additional costs of $29,000 per year for 5 years. The NWC needed for the project is $2,000. The firm’s marginal tax rate is 33% and the cost of capital is 16% The $92,000 is a sunk cost Your firm is considering building a new brewery. You have paid a consultant $92,000 for a plan. New equipment will cost $420,000. At the conclusion of the project the equipment can be sold for $80,000 (before tax salvage). Depreciation is by the 3-year MACRS method. The brewery should generate additional revenues of $224,000 per year and additional costs of $29,000 per year for 5 years. The NWC needed for the project is $2,000. The firm’s marginal tax rate is 33% and the cost of capital is 16% What is the initial investment? Year0 = -420,000 Net Working Capital = $2,000 NEW EQUIPMENT WILL COST $420,000. AT THE CONCLUSION OF THE PROJECT THE EQUIPMENT CAN BE SOLD FOR $80,000 (BEFORE TAX SALVAGE). 0 1 2 3 5 -$420000 4 -$2,000 $2,000 DEPRECIATION Used in 2 parts OCFs After-tax salvage value 2 common ways of handling depreciation Straight-line Depreciation MACRS STRAIGHT-LINE DEPRECIATION Under the straight-line method of depreciation, recognize depreciation expense evenly over the estimated useful life of an asset. Depreciation expense each period is found with: ???????????? = (??????? ???? − ??????? ?????) ?????? ?? ????? For straight-line depreciation always assume savage value after taxes MODIFIED ACCELERATED COST RECOVERY SYSTEM DEPRECIATION MACRS lists different depreciation percentages for each year for different types of assets. MACRS Depreciation Expense: ????????????? = ??????? ???? ∗ ????? ?????????? ??? ???? ? ????????????? = ??????? ???? ∗ ????? ?????????? ??? ???? ? DEPRECIATION New equipment will cost $420,000. At the conclusion of the project the equipment can be sold for $80,000 (before tax salvage). Depreciation is by the 3-year MACRS method. The brewery should generate additional revenues of $224,000 per year and additional costs of $29,000 per year for 5 years. The firm’s marginal tax rate is 33% and the cost of capital is 16% ????????????? = ??????? ???? ∗ ????? ?????????? ??? ???? ? ????????????1 = 420,000 ∗ .3333 = 139,986 ????????????2 = 420,000 ∗ .4445 = 186,690 ????????????3 = 420,000 ∗ .1481 = 62,202 ????????????4 = 420,000 ∗ .0741 = 31,122 ????????????5 = 420,000 ∗ 0 = 0 Important: Depreciation does not go on timeline! But need it for use in other formulas AFTER-TAX SALVAGE VALUE When an asset is sold for salvage, it may be sold for more than its book value If this is the case, then you may have to pay taxes on the salvage value For our purposes, are always concerned with the after-tax salvage value After tax Salvage Value =Salvage value – T*(salvage value-book value) Book Value = initial cost – accumulated depreciation AFTER-TAX SALVAGE VALUE New equipment will cost $420,000. At the conclusion of the project the equipment can be sold for $80,000 (before tax salvage). Depreciation is by the 3-year MACRS method. The brewery should generate additional revenues of $224,000 per year and additional costs of $29,000 per year for 5 years. The firm’s marginal tax rate is 33% and the cost of capital is 16% After tax Salvage Value =Salvage value – T*(salvage value-book value) =80,000-.33*(80,000-Book Value) Book Value = Initial Cost – Accumulated Depreciation =$420,000- Accumulated Depreciation ????????????? = ??????? ???? ∗ ????? ?????????? ??? ???? ? ????????????1 = 420,000 ∗ .3333 = 139,986 ????????????2 = 420,000 ∗ .4445 = 186,690 ????????????3 = 420,000 ∗ .1481 = 62,202 ????????????4 = 420,000 ∗ .0741 = 31,122 ????????????5 = 420,000 ∗ 0 = 0 New equipment will cost $420,000. At the conclusion of the project the equipment can be sold for $80,000 (before tax salvage). Depreciation is by the 3-year MACRS method. The brewery should generate additional revenues of $224,000 per year and additional costs of $29,000 per year for 5 years. The firm’s marginal tax rate is 33% and the cost of capital is 16% Book Value = Initial Cost – Accumulated Depreciation =$420,000-$420,000 =0 After tax Salvage Value =Salvage value – T*(salvage value-book value) =80,000-(.33*(80,000-0)) =$53,600 This example has the equipment fully depreciated – this may not always be the case NEW EQUIPMENT WILL COST $420,000. AT THE CONCLUSION OF THE PROJECT THE EQUIPMENT CAN BE SOLD FOR $80,000 (BEFORE TAX SALVAGE). 0 1 2 3 5 -$420,000 4 $53,600 -$2,000 $2,000 SIMPLE OCF: TAX SHIELD METHOD ???? = ???????? − ???????? ? ∗ 1 − ? + ? ∗ ???????????? ? ???? = ???????? − ???????? ? ∗ 1 − ? + ? ∗ ???????????? ? ? is for time (like when we did DDM) – Do not multiply by ?, it is a notation So, for year 1 ???1 = ???????? − ???????? 1 ∗ 1 − ? + ? ∗ ???????????? 1 Your firm is considering building a new brewery. You have paid a consultant $92,000 for a plan. New equipment will cost $420,000. At the conclusion of the project the equipment can be sold for $80,000 (before tax salvage). Depreciation is by the 3-year MACRS method. The brewery should generate additional revenues of $224,000 per year and additional costs of $29,000 per year for 5 years. The NWC needed for the project is $2,000. The firm’s marginal tax rate is 33% and the cost of capital is 16% What are the operating cash flows each year? Project over 5 years, so 5 years of OCF: Start with year 1 ???? = ???????? − ???????? ? ∗ 1 − ? + ? ∗ ???????????? ? ???1 = 224,000 − 29,000 1 ∗ 1 − .33 + .33 ∗ ???????????? 1 ???1 = 224,000 − 29,000 1 ∗ 1 − .33 + .33 ∗ 139,986 ???? =$176,845 Depreciation for each year of project: ????????????? = ??????? ???? ∗ ????? ?????????? ??? ???? ? ????????????1 = 420,000 ∗ .3333 = 139,986 ????????????2 = 420,000 ∗ .4445 = 186,690 ????????????3 = 420,000 ∗