Capital Budgeting in Excel Net Present Value Formula: Net Present Value (NPV) of a future amount, A, payable in t years time subject to a discount rate, r, is given by
A
NPV =
(1 + r ) t where r = Discount Rate which measures inflation. NPV of a future amount represents the estimated present value of the future amount with estimated future inflation amounts removed. Project appraisals (evaluations) can be made by using the NPV formula.
Interpretations of Net Present Values (NPV) for Project Appraisals: • NPV > 0 Project is worthwhile and profitable to invest in. Return on investment is POSTIVE
• NPV = 0 Project breaks even – no profits are earned. Return on investment is ZERO
• NPV < 0 Project makes a loss and is not profitable. Return on investment is NEGATIVE Ex 1: A project is being considered which has €12,000 initial costs with estimated benefits to be €8,000 after the first year, €7,500 after the second year and €5,000 after the third year. Calculate the NPV of the project using 18.5% as a discount rate. What advise would you give about this investment? Solution:
Discount Rate:
NPV = NPV = -12,000 +
r = 18.5% = 0.185 (1 + r) = 1.185 8000 (1.185)
1
+
7500 (1.185)
2
+
5000 (1.185) 3
=
-12,000 + 6,751.05 + 5341.02 + 3,004.80
=
€3,096.87
Advise: Proceed with the project because under the above conditions this project will cover its cost and make a profit of €3,096.87 after three years. Note:
1.
Initial cost of the project €12,000 is not discounted because it is valued at present but is entered in the formula as a NEGATIVE amount as it is a EXPENSE to the business. Using 18.5% as the Discount Rate yields a NPV >0. This means that if the project goes ahead after 3 years a profit of €3,096.87 is made after the project’s cost is covered. Return on the above investment is €3,096.87.
2.
3.
Ex 2: A project is being considered which has €45,000 initial costs with estimated benefits to be €12,500 after the first year, €15,500 after the second year and €21,000 after the third year and €38,000 after the fourth year. Calculate the NPV of the project using 28% as a discount rate. What advise would you give about this investment? Solution:
Discount Rate:
r = 28% = 0.28 (1 + r) = 1.28
NPV = -45,000 +
12500 (1.28)1
+
15500 (1.28) 2
+
21000 (1.28) 3
+
38000 (1.28) 4
=
-45,000 + 9,765.63 + 9,460.45 + 10,013.58 + 14,156.10
=
- €1,604.24
Advise: Do not go ahead with this project under the above conditions as this project will not cover its cost and make a loss of €1,604.24 after four years. Return on the above investment is -€1,604.24
Internal Rate of Return Formula:
Internal Rate of Return (IRR): is the discount rate which makes the Net Present Value of a Project equal ZERO i.e. the project breaks even. Method for calculating the IRR:
1. 2.
Find a discount rate which yields NPV > 0 ⇒ NPV1, D1 Find a discount rate which yields NPV < 0 ⇒ NPV2, D2
Formula:
NPV 1 IRR = D1 + ( D2 − D1 ) NPV 1 − NPV 2
Example 1: Using the following information, calculate IRR for this project. Use 10% as the initial discount amount.
Year Cash
0 (70,000)
1 15,000
2 20,000
3 20,000
4 20,000
5 20,000
Solution: Discount Rate:
r = 10% = 0.1 (1 + r) = 1.1
NPV = -70,000 +
15000 (1.1)1
+
20000 (1.1) 2
+
20000 (1.1) 3
+
20000 (1.1) 4
+
20000 (1.1) 5
=
-70,000 + 13,636.36 + 16,528.93 + 15,026.30 + 13,660.27 + 12418.43
=
€1,270.29
r = 10% yields a NPV>0 Use D1 = 10% and NPV1 = 1270.29
Let D2 = D1 + 5% = 15% Note: D2 must be > D1 and yield a NPV < 0 so by trial and error pick a suitable D 2 value. Discount Rate:
r = 15% = 0.15 (1 + r) = 1.15
NPV = -70,000 +
15000 (1.15)1
+
20000 (1.15) 2
+
20000 (1.15) 3
+
20000 (1.15) 4
+
20000 (1.15) 5
=
-70,000 + 13,043.48 + 15,122.87 + 13,150.32 + 11,435.06 + 9,943.53
=
- €7,304.74
r = 15% yields a NPV<0 Use D2 = 10% and NPV2 = -7304.74
Find the IRR
NPV 1 IRR = D1 + ( D2 − D1 ) NPV 1 − NPV 2 1270.29 IRR = 10% + (15% – 10%) 1270.29 − (7304.74) IRR = 10% + (5%) (0.1481) IRR = 10% + 0.74% = 10.74%
Explanation of above answer: IRR = 10.74% Using a discount rate of 10.74% to find the Net Present Value for the above project will yield a NPV = 0. i.e. This discount rate will yield a break even return from the project.
Using Microsoft Office Excel 2003 to Calculate Financial Functions (Formulae):
Luckily in Ms Excel 2003 we have a Paste Function wizard displayed by the icon on the toolbars. This function wizard contains a collection of different types of mathematical and computing calculations e.g. The Logical Category contains the IF function, the Statistical Category contains the AVERAGE, MODE, MEDIAN functions. There is also a Financial Category which contains the IRR and the NPV functions.
Example: Using Ms Excel 2003 to calculate the NPV for Ex 1: A project is being considered which has €12,000 initial costs with estimated benefits to be €8,000 after the first year, €7,500 after the second year and €5,000 after the third year. Calculate the NPV of the project using 18.5% as a discount rate. What advise would you give about this investment? Note: This NPV calculation has already been manually calculated which equalled €3,096.87. Tasks in Excel: 1. Enter in the above details – the project’s costs, estimated benefits and the given discount rate into a spreadsheet as outlined below:
2. In cell B10, select the icon in order to construct the NPV formula. Select the Financial category from the category drop down menu. Then select the NPV function to insert into cell B10 as outlined below.
Finally, select the OK option.
3.
Use the Function Arguments dialog box to enter in the necessary details in order to find the NPV. Enter in the following details in the appropriate boxes of the dialog box:
• • • •
Rate Box: Select the appropriate cell which contains the discount rate. Then tab down to next box. Value1 Box: Select the appropriate cell which contains the project’s benefit after the first year. Then tab down to the next box. Value2 Box: Select the appropriate cell which contains the project’s benefit after the second year. Then tab down to the next box. Value3 Box: Select the appropriate cell which contains the project’s benefit after the third year.
The dialog box should appear as outlined below.
4.
Select the OK option and €15,096.87 appears in cell B10 as the NPV answer. Since Excel only deals with future benefit values for NPV calculations, we must finally subtract the project’s cost (which doesn’t have to be discounted since it is a present value) in order to find the overall NPV for the project. The spreadsheet below shows the final NPV a nswer in cell B10 with the final formula appearing up in the formula Bar.
Note: The formula in Cell B10 contains +B5 which represents adding the contents of cell B5 which is already NEGATIVE as it contains the project’s cost to the NPV. Example: Using Ms Excel 2003 to calculate the IRR for
Example 1: Using the following information, calculate IRR for this project. Use 10% as the initial discount amount. Year Cash
0 (70,000)
1 15,000
2 20,000
3 20,000
4 20,000
5 20,000
Note: This IRR calculation has already been manually calculated which equalled 10.74% Tasks in Excel: 1. Enter in the above details – the project’s costs, estimated benefits and the given initial discount rate into a spreadsheet as outlined b elow:
2.
In cell B12, select the icon in order to construct the IRR formula. Select the Financial category from the category drop down menu. Then select the IRR function to insert into cell B12 as outlined below.
Finally, select the OK option. 3.
Use the Function Arguments dialog box to enter in the necessary details in order to find the IRR. Enter in the following details in the appropriate boxes of the dialog box:
• •
Value Box: Highlight the range of cells containing the project’s cost and all of its future benefits. Then tab down to next box. Guess Box: Select the appropriate cell which contains the initial discount rate given.
The dialog box should appear as outlined below:
4.
Select the OK option and 11% or 10.68% when rounded off to 2 decimal places appears in cell B12 as the IRR answer.
Exercises: Using Ms Excel carry out the following calculations:
1.
A manger is considering investing €14,000 in new equipment for his factory. He has estimated the financial benefits of such an investment to be €3,000 after the first year, €4,000 after the second year and third year, €7,000 after the fourth year and €5,000 after the fifth year. Calculate the net present value (NPV) of this project using a discount rate of 15%. Calculate the internal rate of return (IRR) for the project mentioned above using 15% as the initial discount rate. (Round off your answer to 2 decimal places.)
2.
The net cash flows on a project are estimated to be as follows: Year Net Cash Flow
0 -50000
1
2 25500
3 24500
4 17000
14000
Calculate the NPV of the project using a discount rate of 22%. Calculate the internal rate of return (IRR) for the project mentioned above using 22% as the initial discount rate. (Round off your answer to 2 decimal places.)