qrb 501 capital budget

qrb501_r4_capital_budgeting_model.xls

Home >Business & Finance homework help >qrb 501 capital budget

C-Bud Tools

				5/19/14 21:42
Model for Capital Budgeting
First, you will analyze Projects S and L. Their cash flows are shown immediately below in both tabular and
timeline formats. Spreadsheet analyses can be set up vertically, in a table with columns, or horizontally,
using timelines. For short problems, with just a few years, it is normal to use the timeline format
because rows can be added and you can set the problem up as a series of income statements. For long
problems, it is often more convenient to use a tabular layout.
		Expected after-tax					Project S
		net cash flows (CFt)
	Year (t)	Project S	Project L		0	1	2	3	4
	0	($1,000)	($1,000)		(1,000)	500	400	300	100
	1	500	100
	2	400	300				Project L
	3	300	400
	4	100	600		0	1	2	3	4
					(1,000)	100	300	400	600
Capital Budgeting Decision Criteria
Here are the five key methods used to evaluate projects: (1) payback period, (2) discounted payback period, (3) net
present value, (4) internal rate of return, and (5) modified internal rate of return. Using these criteria, financial
analysts seek to identify those projects that will lead to the maximization of the firm's stock price.
Payback Period
The payback period is defined as the expected number of years required to recover the investment, and it was the
first formal method used to evaluate capital budgeting projects. First, identify the year in which the cumulative
cash inflows exceed the initial cash outflows. That is the payback year. Then, take the previous year and add to it
the unrecovered balance at the end of that year divided by the following year's cash flow. Generally speaking, the
shorter the payback period, the better the investment.
Project S
	Time period:	0	1	2	3	4
	Cash flow:	(1,000)	500	400	300	100
	Cumulative cash flow:	(1,000)	(500)	(100)	200	300				Click fx > Logical > AND > OK to get dialog box.
		0.00	0.00	0.00	0.00	0.00	Use Logical "AND" to determine			Then specify you want TRUE if cumulative CF > 0 but the previous CF < 0.
		0.00	0.00	0.00	2.33	0.00	the first positive cumulative CF.			There will be one TRUE.
	Payback:	2.33					Use Logical IF to find the Payback.			Click fx > Logical > IF > OK. Specify that if true, the payback is the previous year plus a fraction, if false, then 0.
							Use Statistical Max function to			Click fx > Statistical > MAX > OK > and specify range to find Payback.
Alternative calculation:		2.33	Alternative: Use nested IF statements to				display payback.
			find payback. Fx > Logical > IF > OK, statements.
Project L
	Time period:	0	1	2	3	4
	Cash flow:	(1,000)	100	300	400	600
	Cumulative cash flow:	(1,000)	(900)	(600)	(200)	400
	Payback:	3.33	Uses IF statement.
Discounted Payback Period
The discounted payback period uses the project's cost of capital to discount the expected cash flows. The
calculation of the discounted payback period is identical to the calculation of regular payback period, except
you must base the calculation on a new row of discounted cash flows. Note that both projects have a cost of
capital of 10%.
WACC =	10%
Project S
	Time period:	0	1	2	3	4
	Cash flow:	(1,000)	500	400	300	100
	Disc. cash flow:	(1,000)	455	331	225	68
	Disc. cum. cash flow:	(1,000)	(545)	(215)	11	79
	Discounted Payback:	2.95	Uses IF statement.
Project L
	Time period:	0	1	2	3	4
	Cash flow:	(1,000)	100	300	400	600
	Disc. cash flow:	(1,000)	91	248	301	410
	Disc. cum. cash flow:	(1,000)	(909)	(661)	(361)	49
	Discounted Payback:	3.88	Uses IF statement.
The inherent problem with both paybacks is that they ignore cash flows that occur after the payback period mark.
While the discounted method accounts for timing issues (to some extent), it still falls short of fully analyzing
projects. However, all else equal, these two methods do provide some information about projects' liquidity and risk.
Net Present Value (NPV)
To calculate the NPV, find the present value of the individual cash flows and find the sum of those discounted
cash flows. This value represents the value the project adds to shareholder wealth.
WACC =	10%
Project S
	Time period:	0	1	2	3	4
	Cash flow:	(1,000)	500	400	300	100
	Disc. cash flow:	(1,000)	455	331	225	68
NPV(S) =	$78.82	= Sum disc. CF's.		or	$78.82	= Uses NPV function.
Project L
	Time period:	0	1	2	3	4
	Cash flow:	(1,000)	100	300	400	600
	Disc. cash flow:	(1,000)	91	248	301	410
NPV(L) =	$49.18				$ 49.18
The NPV method of capital budgeting dictates that all independent projects that have positive NPV should be accepted.
The rationale behind that assertion arises from the idea that all such projects add wealth, and that should be the
overall goal of the manager in all respects. If strictly using the NPV method to evaluate two mutually exclusive
projects, you would want to accept the project that adds the most value (such as the project with the higher NPV). Hence,
if considering the above two projects, you would accept both projects if they are independent, and you would only
accept Project S if they are mutually exclusive.
Internal Rate of Return (IRR)
The internal rate of return is defined as the discount rate that equates the present value of a project's cash inflows
to its outflows. In other words, the internal rate of return is the interest rate that forces NPV to 0. The
calculation for IRR can be tedious, but Microsoft® Excel® software provides an IRR function that merely
requires you to access the function and enter the array of cash flows. The IRR's for Project S and L are shown
below, along with the data entry for Project S.
		Expected after-tax
		net cash flows (CFt)
	Year (t)	Project S	Project L
	0	($1,000)	($1,000)				The IRR function assumes
	1	500	100		IRR S =	14.49%	payments occur at end of
	2	400	300		IRR L =	11.79%	periods, so that function does
	3	300	400				not have to be adjusted.
	4	100	600
The IRR method of capital budgeting maintains that projects should be accepted if their IRR is greater than the cost
of capital. Strict adherence to the IRR method would further dictate that mutually exclusive projects should be
chosen on the basis of the greatest IRR. In this scenario, both projects have IRRs that exceed the cost of capital
(10%) and both should be accepted, if they are independent. If, however, the projects are mutually exclusive, you
would chose Project S. Recall that this was the determination using the NPV method as well. The question that
naturally arises is whether or not the NPV and IRR methods will always agree.
When dealing with independent projects, the NPV and IRR methods will always yield the same accept/reject result.
However, in the case of mutually exclusive projects, NPV and IRR can give conflicting results. One shortcoming of
the internal rate of return is that it assumes that cash flows received are reinvested at the project's internal rate of
return, which is not usually true. The nature of the congruence of the NPV and IRR methods is further detailed in a
latter section of this model.
Multiple IRR's
Because of the mathematics involved, it is possible for some (but not all) projects that have more than one change of
signs in the set of cash flows to have more than one IRR. If you attempted to find the IRR with such a project using a
Financial Calculator, you would get an error message. The HP-10B says "Error - Soln" and the HP-17B says
"Many/No Solutions; Key in Guess" when such a project is evaluated. The procedure for correcting the problem is
to store in a guess for the IRR, and then the calculator will report the IRR that is closest to your guess. You can
then use a different guess value, and you should be able to find the other IRR. However, the nature of the
mathematics creates a scenario in which one IRR is quite extraordinary (often, a few hundred percent). Consider
the case of Project M.
Project M:		0	1	2
		(1.6)	10	(10)
You will solve this IRR twice; the first time using the default guess of 10%, and the second time you will enter a guess
of 300%. Notice, that the first IRR calculation is exactly as it was above.
IRR M 1 =	25.0%
IRR M 2 =	400%
The two solutions to this problem tell you that this project will have a positive NPV for all costs of capital between
25% and 400%. This point is illustrated by creating a data table and a graph of the project NPVs.
Project M:		0	1	2
		(1.6)	10	(10)
k =	25.0%
NPV =	0.00
	NPV
k	$0.0
0%	(1.60)
25%	0.00
50%	0.62
75%	0.85
100%	0.90	Max.
125%	0.87
150%	0.80
175%	0.71
200%	0.62
225%	0.53
250%	0.44
275%	0.36
300%	0.28
325%	0.20
350%	0.13
375%	0.06
400%	0.00
425%	(0.06)
450%	(0.11)
475%	(0.16)
500%	(0.21)
525%	(0.26)
550%	(0.30)
NPV Profiles
NPV profiles graph the relationship between projects' NPVs and the cost of capital. To create NPV profiles for
Projects S and L, create data tables of NPV at different costs of capital.
		Project S			Project L
	WACC	$78.82		WACC	$49.18
	0%	300.00		0%	400.00
	2%	249.74		2%	317.63
	4%	202.77		4%	242.00
	6%	158.79		6%	172.44
	8%	117.55		8%	108.35
	10%	78.82		10%	49.18
	12%	42.39		12%	(5.53)
	14%	8.08		14%	(56.20)
	16%	(24.27)		16%	(103.21)
Previously discussed was the fact that, in some instances, the NPV and IRR methods can give conflicting results. First,
you should attempt to define what you see in this graph. Notice, that the two project profiles (S and L) intersect the
x-axis at costs of capital of 14% and 12%, respectively. Not coincidently, those are the IRRs of the projects. If you
think about the definition of IRR, remember that the internal rate of return is the cost of capital at which a project
will have an NPV of 0. Looking at the graph, it is a logical conclusion that the IRR of a project is defined as the
point at which its profile intersects the x-axis.
Looking further at the NPV profiles, you see that the two project profiles intersect at a point called the
crossover point. Observe that at costs of capital greater than the crossover point, the project with the greater IRR
(Project S, in this case) also has the greater NPV. But at costs of capital less than the crossover point, the project
with the lesser IRR has the greater NPV. This relationship is the source of discrepancy between the NPV and IRR
methods. By looking at the graph, you see that the crossover appears to occur at approximately 7%. Luckily, there
is a more precise way of determining crossover. To find crossover, find the difference between the two
projects' cash flows in each year, and then find the IRR of this series of differential cash flows.
	Expected after-tax
	net cash flows (CFt)		Cash flow	Alternative: Use Tools > Goal Seek to find WACC when NPV(S) =
Year (t)	Project S	Project L	differential	NPV(L). Set up a table to show the difference in NPV's, which we
0	($1,000)	($1,000)	0	want to be zero. The following will do it, getting WACC = 7.17%.
1	500	100	400	Look at B57 for the answer, then restore B57 to 10%.
2	400	300	100		NPV S =	$ 78.82
3	300	400	(100)		NPV L =	$ 49.18
4	100	600	(500)		S - L =	$ 29.64
IRR =	Crossover rate =		7.17%
The intuition behind the relationship between the NPV profile and the crossover rate is as follows: (1) Distant cash
flows are heavily penalized by high discount rates--the denominator is (1 + k)^t, and it increases geometrically,
hence gets very large at high values of t. (2) Long-term projects like L have most of their cash flows coming in the
later years, when the discount penalty is largest, hence they are most severely impacted by high capital costs. (3)
Therefore, Project L's NPV profile is steeper than that of S. (4) Since the two profiles have different slopes, they
cross one another.
Modified Internal Rate of Return (MIRR)
The modified internal rate of return is the discount rate that causes a project's cost (or cash outflows) to equal the
present value of the project's terminal value. The terminal value is defined as the sum of the future values of the
project's cash inflows, compounded at the project's cost of capital. To find MIRR, calculate the PV of the outflows
and the FV of the inflows, and then find the rate that equates the two. Or, you can solve using the MIRR function.
WACC =	10%					MIRRS =	12.11%
			Project S			MIRRL =	11.33%
	10%
	0	1	2	3	4
	(1,000)	500	400	300	100
			Project L
	0	1	2	3	4
	(1,000)	100	300	400	600
					440.0
					363.0
					133.1
P V :	(1,000)			Terminal Value:	1,536.1
The advantage of using the MIRR, relative to the IRR, is that the MIRR assumes that cash flows received are
reinvested at the cost of capital, not the IRR. Since reinvestment at the cost of capital is more likely, the MIRR is a
better indicator of a project's profitability. Moreover, it solves the multiple IRR problem, as a set of cash flows can
have but one MIRR .
Note that if negative cash flows occur in years beyond Year 1, those cash flows would be discounted at the cost of
capital and added to the Year 0 cost to find the total PV of costs. If both positive and negative flows occurred in some
year, the negative flow should be discounted, and the positive one compounded, rather than just dealing with the net
cash flow. This makes a difference.
Also note that the MIRR function allows for discounting and reinvestment to occur at different rates. Generally,
MIRR is defined as reinvestment at the WACC, though Microsoft® Excel® software allows the calculation of a
special MIRR where reinvestment occurs at a different rate from WACC.
Finally, it is stated in the text, when the IRR versus the NPV is discussed, that the NPV is superior because (1)
the NPV assumes that cash flows are reinvested at the cost of capital whereas the IRR assumes reinvestment at
the IRR, and (2) it is more likely, in a competitive world, that the actual reinvestment rate is more likely to be the
cost of capital than the IRR, especially if the IRR is quite high. The MIRR setup can be used to prove
that NPV does indeed assume reinvestment at the WACC, and IRR at the IRR.
			Project S
WACC =	10%
	0	1	2	3	4
	(1,000)	500	400	300	100
					330.0
					484.0	Reinvestment at WACC = 10%
					665.5
PV outflows		-$1,000.00		Terminal Value:	1,579.5
PV of TV		$1,078.82
NPV		$ 78.82	Thus, we see that the NPV is consistent with reinvestment at WACC.
Now repeat the process using the IRR, which is G118 as the discount rate.
			Project S
IRR =	14.49%
	0	1	2	3	4
	(1,000)	500	400	300	100
					343.5
					524.3	Reinvestment at IRR = 14.49%
					750.3
PV outflows		-$1,000.00		Terminal Value:	1,718.1
PV of TV		$1,000.00
NPV		$0.00	Thus, if compounding is at the IRR, NPV is zero. Since the
			definition of IRR is the rate at which NPV = 0, this demonstrates
			that the IRR assumes reinvestment at the IRR.

C-Bud Tools

Page &P

Project S

Project L

Cost of capital

NPV

NPV Profile of Projects S and L

Multiple Rates of Return

Project L

Project S

The NPV, if k=25%.