Excel Calculations for Depreciation, Free Cash Flows, Payback Period, IRR and NPV

This document provides step-by-step instructions and examples for calculating depreciation, free cash flows, payback period, internal rate of return (IRR), and net present value (NPV) using Excel. It includes a depreciation schedule for present and proposed machines, free cash flows for both machines, and calculations for payback period, IRR, and NPV. The document also provides recommendations based on the analysis.

EXCEL CALCULATIONS

TABLE OF CONTENTS
QUESTION .....................................................................................................................................1
i) Depreciation Schedule for present and proposed machines ....................................................1
ii) Free cash flows of present and proposed machines ...............................................................1
iii) Payback period, IRR and net present value ...........................................................................2
iv) Recommendations .................................................................................................................5
REFERENCES................................................................................................................................6

QUESTION
i) Depreciation Schedule for present and proposed machines
Depreciation Schedule
Present
Machine
Proposed
Machine
Cost of Machine 120000
145000+1500
0 = 160000
Useful life 5 5
Remaining Life 3 5
Salvage value 8000 24000
Depreciation
already charged 44800 0
Depreciation for the
year 22400 27200
ii) Free cash flows of present and proposed machines
Free Cash Flows
Proposed Machine
Year 1 Year 2 Year 3 Year 4 Year 5
PBDIT 105000 110000 120000 120000 120000
Depreciation -27200 -27200 -27200 -27200 -27200
PBIT 77800 82800 92800 92800 92800
Tax @ 30% -23340 -24840 -27840 -27840 -27840
PAT 54460 57960 64960 64960 64960
Add: Depreciation 27200 27200 27200 27200 27200
Change in working
capital -18000 -18000 -18000 -18000 -18000
Less: Capex 0 0 0 0 0
Free Cash Flows 63660 67160 74160 74160 74160
Inflation @ 2% 63660 64933.2 66231.86 67556.50 68907.63
Discounted Cash
Flows 59163.57 56084.42 53165.53 50398.55 47775.58
Present Machine
Year 1 Year 2 Year 3 Year 4 Year 5
PBDIT 95000 95000 95000 95000 95000
Depreciation -22400 -22400 -22400 -22400 -22400
1

PBIT 72600 72600 72600 72600 72600
Tax @ 30% -21780 -21780 -21780 -21780 -21780
PAT 50820 50820 50820 50820 50820
Add: Depreciation 22400 22400 22400 22400 22400
Change in working
capital 0 0 0 0 0
Less: Capex 0 0 0 0 0
Free Cash Flows 73220 73220 73220 73220 73220
Inflation @ 2% 73220 74684.4 76178.09 77701.65 79255.68
Discounted Cash
Flows 68048.33 64506.78 61149.55 57967.04 54950.17
iii) Payback period, IRR and net present value
Net Present Value
Computation of NPV
Proposed Machine
Year Cash inflows PV factor @ 10%
Discounted
cash inflows
1 63660 0.929 59163.57
2 64933.2 0.864 56084
3 66231.864 0.803 53166
4 67556.50 0.746 50399
5 92907.63 0.693 64415
Total discounted cash inflow 283228
Initial investment 120000
NPV (Total discounted cash
inflows - initial investment) 163228
2

Computation of NPV
Present Machine
Year Cash inflows PV factor @ 10%
Discounted cash
inflows
1 73220 0.929 68048.33
2 74684.4 0.864 64507
3 76178.09 0.803 61150
4 77701.65 0.746 57967
5 87255.68 0.693 60497
Total discounted cash
inflow 312168
Initial investment 160000
NPV (Total
discounted cash
inflows - initial
investment) 152168
Internal Rate of Return
Computation of IRR
Proposed Machine
Year Cash inflows
0 -120000
1 63660
2 64933.2
3 66231.864
4 67556.50
5 92907.63
Internal rate of return
(IRR) 48%
Computation of IRR
Present Machine
3

Year Cash inflows
0 -160000
1 73220
2 74684.4
3 76178.09
4 77701.65
5 87255.68
Internal rate of return
(IRR) 38%
Payback Period
Computation of Payback period
Proposed Machine
Year Cash inflows
Cumulative
cash inflows
1 63660 63660
2 64933.2 128593.2
3 66231.864 194825.064
4 67556.50 262381.57
5 108000 370381.57
Initial investment 120000
Payback period 2
0.3
Payback period
2 year and 4
months
Computation of Payback period
Present Machine
Year Cash inflows
Cumulative
cash inflows
1 73220 73220
2 74684.4 147904.4
3 76178.09 224082.5
4 77701.65 301784.1
5 95255.68 397039.8
Initial investment 160000
Payback period 2
4

0.2
Payback period
2 year and 2
months
iv) Recommendations
ABC manufacturing is having one present machine and a proposed machine. Useful life
of both the machines are same however the remaining life of existing machine is 2 years only.
But for the purpose of calculations the life of present machine is taken as 5 years (Alkaraan,
2017). From the above calculations it could be computed that the company should adopt option
of new proposed machine instead of existing machine as the net present value of the new
machine is higher which shows that this machine will provide more profits. If internal rate of
return is considered than also new machine is suggested as the rate of return is 50% in new
machine where in present machine it is only 38%. Further the payback period used to know the
period of recovery of cost it could be assessed that payback of present machine is shorter but the
difference from new machine is not very high. On overall analysis the company should go for
new machine and sell the old machine at 70000. The proceeds could be used for reducing the
cost of new machine and returns will be further increased as initial cost of investments will be
reduced from proceeds of old machine and output will be increased (Grant and Nilsson, 2020). It
will be beneficial for the ABC manufacturing to buy new machine and sell out old machine as
the free cash flows are higher even when the working capital is increased.
5

6

REFERENCES
Books and Journals
Alkaraan, F., 2017. Strategic investment appraisal: multidisciplinary perspectives. In Advances
in Mergers and Acquisitions. Emerald Publishing Limited.
Grant, M. and Nilsson, F., 2020. The production of strategic and financial rationales in capital
investments: Judgments based on intuitive expertise. The British Accounting
Review. 52(3). p.100861.
7