Finance (excel functions)

2020S_FIN325_ClassExercise_Ch8.NPVandOtherCriteria_A-1.xlsx

Home >Business & Finance homework help >Accounting homework help >Finance (excel functions)

Project interaction

Project Interactions.
NPV, IRR, PI, PP, DPP
Q. You have two proposals to choose between. The initial proposal has a cash flow that is different than the revised proposal. Using IRR, which do you prefer?
Initial Projects	0	1	2	3	WACC	NPV	IRR	Profitability Index	Payback Period	Discounted payback period
Free Cash Flows	-350	400	7%	23.83	14.29%	1.07	0.88	0.94
Cumulative Payback	-350	50
PV of CF	-350	373.83
Cumulative Disc. Payback	-350	23.83
Revised Projects	0	1	2	3	WACC	NPV	IRR	Profitability Index	Payback Period	Discounted payback period
Free Cash Flows	-375	25	25	475	7%	57.94	12.56%	1.15	2.68	2.85
Cumulative Payback	-375	-350	-325	150
PV of CF	-375	23.36	21.84	387.74
Cumulative Disc. Payback	-375	-351.64	-329.80	57.94
Problem 1. The investment Timing Decision
You may purchase a computer anytime within the next five years. The computer will save your company money only for the year when you purchase
and the cost of computers continues to decline. If your cost of capital is 10%, and given the data listed below, when should you purchase the computer?
Cost of Capital
10%	0	1	2	3	4	5
Savings	70	70	70	70	70	70
- Cost	-50	-45	-40	-36	-33	-31
= Benefit at purchase	20	25	30	34	37	39
PV of Benefit	20	22.73	24.79	25.54	25.27	24.22
Invsetment Timing (year)	3
Problem 2. The choice between long and short-lived equipment
EX 1. Choosing Lowest Annual Cost.
Given the following costs of operating two machines and a 6% cost of capital, select the lower cost machine using "Equivalent Annual Cost" method.
Cost of Capital
6%	0	1	2	3	NPV of Cost
Machine X costs	-15	-4	-4	-4	(25.69)
Machine Y costs	-10	-6	-6	(21.00)
Lower cost Machine: X or Y	Machine X
Machine I	rate	nper	pmt (EAC)	pv	fv
6%	3	?	25.69	0
($9.61)
Machine J	rate	nper	pmt (EAC)	pv	fv
6%	2	?	21.00	0
($11.45)
EX 2. Choosing Highest Annual Cash Flows of the projects.
Select one of the two following projects based on highest Value “Equivalent Annual Annuity” (r = 9%).
Cost of Capital
9%	0	1	2	3	4	NPV of Project
Project A	-15	4.9	5.2	5.9	6.2	2.82
Project B	-20	8.1	8.7	10.4	2.78
Higher EAA project: A or B	Project B
Project A	rate	nper	pmt (EAA)	pv	fv
9%	4	?	(2.82)	0
$0.87
Project B	rate	nper	pmt (EAA)	pv	fv
9%	3	?	(2.78)	0
$1.10
Problem 3. When to replace an old machine, calcualating PV of operating cost
Q. You are operating an old machine that will last 2 more years before it gives up the ghost. It costs $12,000 per year to operate.
You can replace it now with a new machine that costs $25,000 but is much more efficient (only $8,000 per year in operating costs)
and will last for 5 years. Should we replace the machine now or stick with it for a while longer? The opportunity cost of capital is 6%
Cost of Capital
6%	0	1	2	3	4	5	NPV
New ($,thousand)	-25	-8	-8	-8	-8	-8	(58.70)
Old ($,thousand)	-12	-12	(22.00)
Replacement or Not	Not
New	rate	nper	pmt (EAC)	pv	fv
6%	5	?	58.70	0
($13.93)
Old	rate	nper	pmt (EAC)	pv	fv
6%	2	?	22.00	0
($12.00)
Equivalent Annual Cost	0	1	2	3	4	5
New ($,thousand)	0	-13.93	-13.93	-13.93	-13.93	-13.93
Old ($,thousand)	-12	-12

NPV & PI

	Relationship between NPV and PI
The following are the cash flows of two projects:
Is the project with the highest profitability index also the one with the highest NPV?
Input variables:
Year	Project A	Project B
0	-$200	-$200
1	80	100
2	80	100
3	80	100
4	80
Cost of capital	11%	11%
Solution and Explanation:
Projects	Profitability index	PV
Project A	1.2410	$248.20
Project B	1.2219	$244.37
High PI/high NPV? Yes or No	Yes
Notes:
The project with the higher PI must also have the higher NPV.

IRR & Discount rate

	Relationship between IRR & Discount rate
A new computer system will require an initial outlay of $20,000, but it will increase the firm’s cash flows by $4,000 a year for each of the next 8 years.
Input variables:
Initial investment	-$20,000	(input as a negative)
Annual cash flow	$4,000
Years	8
Discount rate 1	9%
Discount rate 2	14%
Year:	0	1	2	3	4	5	6	7	8
Cash flow:	-$20,000	$4,000	$4,000	$4,000	$4,000	$4,000	$4,000	$4,000	$4,000
Solution and Explanation:
a. Calculate the NPV and decide if the system is worth installing if the required rate of return is 9%. What if it is 14%?
Discount Rate	NPV	Installing? Y/N
9%	$2,139.28	Yes
14%	-$1,444.54	No
b. How high can the discount rate be before you would reject the project?
IRR	11.81%

NPV & DDM

	NPV & DDM
Growth Enterprises believes its latest project, which will cost $80,000 to install, will generate a perpetual growing stream of cash flows. Cash flow at the end of the first year will be $5,000, and cash flows in future years are expected to grow indefinitely at an annual rate of 5%. The discount rate for this project is 10%
Input variables:
Initial investment	-$80,000	Input as a negative	5000	5000	5000	5000	5000	5000	5000
Year 1 cash flow	$5,000
Cash flow growth rate	5%
Discount rate	10%
a. If , what is the project NPV?
PV of Future Cash Flows	$100,000
NPV	$20,000
b. What is the project IRR?
IRR	11.25%
Note!
IRR = r = (CF1/Initial Investment) + g
based on DDM model. PV = D1/(r-g)

NPV & IRR

	NPV vs. IRR
Consider projects A and B:
Input variables:
Project	0	1	2	NPV @ 10%
Project A	(30,000)	21,000	21,000	6,446
Project B	(50,000)	33,000	33,000	7,273
Discount rate	10%
Solution and Explanation:
a. Calculate IRRs for A and B.
IRR
Project A	25.69%
Project B	20.69%
b. Which project does the IRR rule suggest is best?
Project A or B
IRR selection	Project A
c. Which project is really best?
Project A or B
Best selection	Project B

PP vs. DPP

	Payback Period vs. Discounted Payback Period
Here are the expected cash flows for three projects:
the opportunity cost of capital is 10%
Input variables:
Projects	0	1	2	3	4
A	(5,000)	1,000	1,000	3,000	- 0
B	(1,000)	- 0	1,000	2,000	3,000
C	(5,000)	1,000	1,000	3,000	5,000
Cutoff years	3
Discount Rate	10%
Solution and Explanation:
a. What is the payback period on each of the projects?
Project	Payback period	Year:	0	1	2	3	4
A	3	CFs	(5,000)	1,000	1,000	3,000	- 0
Cumulative CF	(5,000)	(4,000)	(3,000)	- 0	- 0
B	2	CFs	(1,000)	- 0	1,000	2,000	3,000
Cumulative CF	(1,000)	(1,000)	- 0	2,000	5,000
C	3	CFs	(5,000)	1,000	1,000	3,000	5,000
Cumulative CF	(5,000)	(4,000)	(3,000)	- 0	5,000
b. If you use a cutoff period of 3 years, which projects would you accept under payback period criteria?
Project A, B, C
Accept projects	A,B,C
c. What is the payback period on each of the projects?
Project	Payback period	Year:	0	1	2	3	4
A	longer than 4 years	CFs	(5,000)	1,000	1,000	3,000	- 0
PV of CF	(5,000)	909	826	2,254	- 0
Cumulative PV	(5,000)	(4,091)	(3,264)	(1,011)	(1,011)
B	2.12	CFs	(1,000)	- 0	1,000	2,000	3,000
PV of CF	(1,000)	- 0	826	1,503	2,049
Cumulative PV	(1,000)	(1,000)	(174)	1,329	3,378
C	3.30	CFs	(5,000)	1,000	1,000	3,000	5,000
PV of CF	(5,000)	909	826	2,254	3,415
Cumulative PV	(5,000)	(4,091)	(3,264)	(1,011)	2,405
d. If you use a cutoff period of 3 years, which projects would you accept under discounted payback period criteria?
Project A, B, C
Accept projects	B
e. "Payback gives too much weight to cash flows that occur after the cutoff date" True or False
True/False	False

EAC Lease vs. Buy

	Equivalent Annual Cost.
A firm can lease a truck for 4 years at a cost of $30,000 annually. It can instead buy a truck at a cost of $80,000,
with annual maintenance expenses of $10,000. The truck will be sold at the end of 4 years for $30,000.
What is the equivalent annual cost of buying and maintaining the truck if the discount rate is 10%?
Input variables:
Lease/Truck life in years	4
Lease annual cost	$30,000
Cost to buy truck	$80,000
Truck annual cost	$10,000
Truck sale ending sale price	$30,000
Discount rate	10%
Solution and Explanation:
PV of costs of BUY	(91,208.3)
Equivalent annual cost of BUY	(28,773.5)
Lease or buy	Buy
Lease or Buy	0	1	2	3	4	NPV of Cost
Lease	(30,000)	(30,000)	(30,000)	(30,000)	(95,096)
Buy	(80,000)	(10,000)	(10,000)	(10,000)	20,000	(91,208)
Buy EAC	rate	nper	pmt (EAC)	pv	fv
Buy	10%	4	?	91,208.25	0
($28,773.54)

EAC New vs. Old

	EAC: New vs. Old
A forklift will last for only 2 more years. It costs $5,000 a year to maintain. For $20,000 you can buy a new lift that can last for 10 years and should require maintenance costs of only $2,000 a year.
Input variables:
Old lift life in years	2
Annual maintenance old lift	$5,000
Cost new lift	$20,000
New lift life in years	10
Annual maintenance new lift	$2,000
a. Discount rate	4%
b. Discount rate	12%
a. Calculate the equivalent cost of owning and operating the forklife if the discount rate is 4% per year.
0	1	2	3	4	5	6	7	8	9	10	NPV
New	(20,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(36,222)
Old	(5,000)	(5,000)	(9,430)
New	rate	nper	pmt (EAC)	pv	fv
4%	10	?	36,222	0
Equivalent annual cost of the new	(4,465.82)
Replace?	Yes
b. Calculate the equivalent cost of owning and operating the forklife if the discount rate is 12% per year.
0	1	2	3	4	5	6	7	8	9	10	NPV
New	(20,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(2,000)	(31,300)
Old	(5,000)	(5,000)	(8,450)
New	rate	nper	pmt (EAC)	pv	fv
12%	10	?	31,300	0
Equivalent annual cost of the new	(5,539.68)
Relace?	No