Question

Assume that the cost of capital (discount rate) for the question that follows is equal to 0.18 (this is a decimal not a percentage)

Consider the following capital budgeting project.

You are considering investing in a business that will cost $200,000 and will create free cash flows at the rate of $30,000 per year for the next 10 years. Your cost of capital was given in the first question. Use that cost of capital in this question.

Within EXCEL compute the following and attach your file to this post.

What is the payback of the project? If you require a payback under 7 years do you accept or reject the project?

What is the discounted payback of the project? If you require a discounted payback under 7 years do you accept or reject the project?

What is the internal rate of return of the project? Do you accept or reject the project given your cost of capital?

What is the MIRR of the project? Do you accept or reject the project given your cost of capital?

What is the NPV of the project? Do you accept or reject the project?

Within the EXCEL spread sheet create a NPV profile of the project using interest rates from 0 to 20% at increments of 1%.

.

Answer 1

Given:

Cost of Capital	0.18
Year	Cashflows
0	-200000
1	30000
2	30000
3	30000
4	30000
5	30000
6	30000
7	30000
8	30000
9	30000
10	30000

Calculation of Payback period, Discounted payback period, IRR, MIRR, NPV using Excel:

Cost of Capital	0.18
Year	Cashflows	Cumulative Cashflows	Discounted Cashflows	Discounted Cumulative Cashflows
0	-200000	-200000	($200,000.00)	($200,000.00)
1	30000	-170000	$25,423.73	($174,576.27)
2	30000	-140000	$21,545.53	($153,030.74)
3	30000	-110000	$18,258.93	($134,771.81)
4	30000	-80000	$15,473.67	($119,298.15)
5	30000	-50000	$13,113.28	($106,184.87)
6	30000	-20000	$11,112.95	($95,071.92)
7	30000	10000	$9,417.75	($85,654.17)
8	30000	40000	$7,981.14	($77,673.03)
9	30000	70000	$6,763.68	($70,909.35)
10	30000	100000	$5,731.93	($65,177.41)
		Value	Status	Reason
	Payback	6.666666667	Accept	Payback is under 7 years as required
	Discounted Payback	-	Reject	The initial cost is not recovered
	IRR	8%	Reject	IRR < cost of capital
	MIRR	13%	Reject	MIRR < cost of capital
	NPV	($55,235.09)	Reject	NPV is negative

The formulas are used as shown below:

The PV formula is used as follows:

PV (rate=cost of capital (0.18), nper is the corresponding year, ,pv is the present value of the cashflow)

Payback = Initial cost of the project / Cashflow = 200000 / 30000 (Absolute value of this is considered since, initial cashflow is taken negative)

Discounted Payback = IFERROR(INDEX(B5:B15,COUNTIF(F5:F15,"<=0"),0)+ABS((INDEX(F5:F15,COUNTIF(F5:F15,"<=0"),0)/(INDEX(E5:E15,COUNTIF(F5:F15,"<=0")+1,0)))),"-")

The Discounted payback formula used here is:

Discounted Payback period = Year before the dicounted cashflow is positive + [(Cumulative Cashflow in the same year)/Discounted cashflow in the next year)

The IRR formula is used as:

IRR (values=Cashflows)

MIRR is used as:

MIRR(values=Cashflows, finance_rate =cost of capital, reinvest_rate=cost of capital)

NPV is used as:

NPV(rate=cost of capital, values=cashflows)

Within the EXCEL spread sheet create a NPV profile of the project using interest rates from 0 to 20% at increments of 1%

Interest rate	NPV
0%	$100,000.00
1%	$83,306.08
2%	$68,115.25
3%	$54,277.75
4%	$41,660.46
5%	$30,144.81
6%	$19,625.11
7%	$10,006.96
8%	$1,205.96
9%	($6,853.46)
10%	($14,239.08)
11%	($21,011.75)
12%	($27,226.17)
13%	($32,931.59)
14%	($38,172.40)
15%	($42,988.64)
16%	($47,416.53)
17%	($51,488.80)
18%	($55,235.09)
19%	($58,682.31)
20%	($61,854.86)

The formulas used are as follows:

The NPV formula is used for the above table calculation.