Question

If you have a budget to buy new machinery what machines will you buy. Calculate the payback period of your proposal and Net present value using a discount rate of 6%.

Answer 1

Here, I have Considered Arbitrary data for Machine 1 and Machine 2, as in the question, there is no case or Data given for the machines, thus, I have considered Budget for Machine 1 and Machine 2, the Budget Include Investment data and Cash In Flow Data over different period for both the Machines, Next, I have calculated Net Present Value and Discounted Payback Period data,

Formula for Present Value = Future Value/(1+ Discounted rate)^Time Period

Net Present Value = Total Present Value of Income at 6% Discount Rate - Total Present Value of Investment at 6% Discount Rate

Discounted Payback Period = year when we will get back the Investment using discounted rate, Follow the Excel Formula as Given below,

Based on below calculation, we have seen that Net present value of machine 1 is high, thus, we will select Machine 1, further payback period for Machine 1 is quite less, thus, we should select machine 1 for purchase purpose,

1	B	C	D	E	F
2	Machine 1
3	Time Period	Income or Cash Flow	Present Value of Income at 6% Discount Rate	Investment	Present Value of Investment at 6% Discount Rate
4	0	0	0.00	300	300
5	1	50	47.17	0	0
6	2	100	89.00	0	0
7	3	200	167.92	0	0
8	4	250	198.02	0	0
9	Therefore, Net Present Value of Machine 1		202.12
10	Discounted Payback Period for Machine 1		2.98
11	Machine 2
12	Time Period	Income or Cash Flow	Present Value of Income at 6% Discount Rate	Investment	Present Value of Investment at 6% Discount Rate
13	0	0	0.00	400	400
14	1	75	70.75	0	0
15	2	125	111.25	0	0
16	3	225	188.91	0	0
17	4	235	186.14	0	0
18	Therefore, Net Present Value of Machine 2		157.06
19	Discounted Payback Period for Machine 1		3.16

Excel Formula:

1	B	C	D	E	F
2	Machine 1
3	Time Period	Income or Cash Flow	Present Value of Income at 6% Discount Rate	Investment	Present Value of Investment at 6% Discount Rate
4	0	0	=C4/(1+0.06)^B4	300	=E4/(1+0.06)^B4
5	1	50	=C5/(1+0.06)^B5	0	=E5/(1+0.06)^B5
6	2	100	=C6/(1+0.06)^B6	0	=E6/(1+0.06)^B6
7	3	200	=C7/(1+0.06)^B7	0	=E7/(1+0.06)^B7
8	4	250	=C8/(1+0.06)^B8	0	=E8/(1+0.06)^B8
9	Therefore, Net Present Value of Machine 1		=(SUM(D4:D8)-SUM(F4:F8))
10	Discounted Payback Period for Machine 1		=B6+(300-SUM(D4:D6))/D7
11	Machine 2
12	Time Period	Income or Cash Flow	Present Value of Income at 6% Discount Rate	Investment	Present Value of Investment at 6% Discount Rate
13	0	0	=C13/(1+0.06)^B13	400	=E13/(1+0.06)^B13
14	1	75	=C14/(1+0.06)^B14	0	=E14/(1+0.06)^B14
15	2	125	=C15/(1+0.06)^B15	0	=E15/(1+0.06)^B15
16	3	225	=C16/(1+0.06)^B16	0	=E16/(1+0.06)^B16
17	4	235	=C17/(1+0.06)^B17	0	=E17/(1+0.06)^B17
18	Therefore, Net Present Value of Machine 2		=(SUM(D13:D17)-SUM(F13:F17))
19	Discounted Payback Period for Machine 1		=B16+(400-SUM(D13:D16))/D17