|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
| |
|
|
| |
|
Bean Counter |
|
| |
|
https://www.dwmbeancounter.com |
|
| |
|
|
| |
|
|
| |
|
|
| Capital
Investment Model |
|
| |
|
|
|
| Instructions |
|
| The Spreadsheet can handle up to 5 Projects |
|
| and three Discount Rates (NPV Rate) |
|
| and calculates NPV, PI, IRR, Payback |
|
| and ARR. |
|
| |
|
| Project Summary |
|
| You need to enter up to 3 Discount (NPV) Rates |
|
| and Depreciation if calculating the ARR in the
White Cells |
|
| and the Light Blue Cells are automatically |
|
| calculated after entering the project(s)
information.. |
|
| |
|
|
|
| Project Area |
|
| The White Cells are used to enter your data |
|
| and Light Blue are automatically calculated. |
|
| You have the option to enter the estimated revenues |
|
| and expenses or just enter the difference as revenues. |
|
| You also need to enter capital investments and |
|
| any salvage values. |
|
| If Calculating the ARR you also need to enter the |
|
| Number Of Periods |
|
| |
|
|
|
| |
|
|
|
|
| Project Summary |
|
|
|
| |
|
|
|
| NPV Rate |
10.00% |
15.00% |
20.00% |
10.00% |
15.00% |
20.00% |
|
|
|
|
| Calculations |
|
NPV |
|
|
PI |
|
IRR |
Payback |
ARR |
Depreciation |
Used for ARR Calc |
|
|
|
|
|
|
|
|
|
|
| Project 1 |
$0 |
$0 |
$0 |
0.00 |
0.00 |
0.00 |
na |
0 Years |
0.00% |
$0 |
|
|
|
|
|
|
|
|
|
|
|
|
| Project 2 |
$0 |
$0 |
$0 |
0.00 |
0.00 |
0.00 |
na |
0 Years |
0.00% |
$0 |
|
|
|
|
|
|
|
|
|
|
|
|
| Project 3 |
$0 |
$0 |
$0 |
0.00 |
0.00 |
0.00 |
na |
0 Years |
0.00% |
$0 |
|
| Project 4 |
$0 |
$0 |
$0 |
0.00 |
0.00 |
0.00 |
na |
0 Years |
0.00% |
$0 |
|
| Project 5 |
$0 |
$0 |
$0 |
0.00 |
0.00 |
0.00 |
na |
0 Years |
0.00% |
$0 |
|
|
|
|
| Periods |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
| Note:Expenses
Do Not Include
Depreciation |
|
|
|
|
|
|
|
|
|
|
|
|
| Number Of Periods |
|
Only Needed For ARR Calculation |
|
|
|
|
|
|
|
|
|
|
| Project 1 |
|
|
|
|
|
|
|
|
|
|
|
|
| Revenues |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Expenses |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
|
|
| Profit |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Capital Investment |
|
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
|
|
| Salvage Value |
|
|
|
|
$0 |
|
|
|
|
|
|
|
|
|
| Cash Flow (Annual) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Cash Flow
(Cumulative) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
| Number Of Periods |
|
Only Needed For ARR Calculation |
|
|
|
|
|
|
|
|
|
|
| Project 2 |
|
|
|
|
|
|
|
|
|
|
|
|
| Revenues |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Expenses |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Profit |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Capital Investment |
|
|
|
|
|
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Salvage Value |
|
|
|
|
|
|
|
|
|
|
|
|
| Cash Flow (Annual) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Cash Flow
(Cumulative) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
| Number Of Periods |
|
Only Needed For ARR Calculation |
|
|
|
|
|
|
|
|
|
|
| Project 3 |
|
|
|
|
|
|
|
|
|
|
|
|
| Revenues |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Expenses |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Profit |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Capital Investment |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Salvage Value |
|
|
|
|
|
|
|
|
|
|
|
|
| Cash Flow (Annual) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Cash Flow
(Cumulative) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
| Number Of Periods |
|
Only Needed For ARR Calculation |
|
|
|
|
|
|
|
|
|
|
| Project 4 |
|
|
|
|
|
|
|
|
|
|
|
|
| Revenues |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Expenses |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Profit |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Capital Investment |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Salvage Value |
|
|
|
|
|
|
|
|
|
|
|
|
| Cash Flow (Annual) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Cash Flow
(Cumulative) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
| Number Of Periods |
|
Only Needed For ARR Calculation |
|
|
|
|
|
|
|
|
|
|
| Project 5 |
|
|
|
|
|
|
|
|
|
|
|
|
| Revenues |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Expenses |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Profit |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Capital Investment |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Salvage Value |
|
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Cash Flow (Annual) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| Cash Flow
(Cumulative) |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
$0 |
|
| |
|
|
|
|
|
|
|
|
|
|
|
| Net Present
Value (NPV) |
|
|
|
|
|
|
|
|
|
|
|
|
| Net
Present Value (NPV) is a way to figure out if an investment will make a
profit, considering that money today is |
|
|
|
|
|
|
|
| worth more than money in the future. |
|
|
|
|
|
|
|
|
|
|
|
| It
compares the current value of expected future cash flows (like revenue or
savings) with the initial investment cost. |
|
|
|
|
|
|
|
| Decision
Criteria: According the NPV technique, for
accept-reject type of decision, if the |
|
|
|
|
|
|
|
|
|
| project
has a positive NPV, the project is acceptable. If a project(s) NPV is less
than ‘Zero’. It |
|
|
|
|
|
|
| gives
negative NPV. Hence, it must be rejected. For mutually exclusive projects
(i.e., only |
|
|
|
|
|
|
| one
project will be selected) the project with highest positive NPV should be
selected. |
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| Internal
Rate Of Return (IRR) |
|
|
|
|
|
|
| This
is another important discounted cash flow technique used in capital budgeting
decisions |
|
|
|
| IRR can be defined as that rate which
equates the present value of cash inflows |
|
|
|
|
| with
the present value of cash outflows of an investment proposal. It is the rate
at which the |
|
|
|
|
| net
present value of the investment proposal is zero. |
|
|
|
|
|
| If
the internal rate of return exceeds the required rate of return, then the
project is accepted. If |
|
|
|
|
|
|
| the
project’s IRR is lower that the required rate of return, it will be rejected.
In case of |
|
|
|
|
|
|
| ranking
the proposals, the technique of IRR is significantly used. The projects with
higher |
|
|
|
|
|
|
| rate
of return will be ranked as first compared to the lowest rate of return
projects. |
|
|
|
|
|
|
|
| Thus, the |
|
|
|
|
|
|
|
|
|
|
| IRR acceptance rules
are: |
|
|
|
|
|
|
|
|
|
|
| Accept if r>k |
|
|
|
|
|
|
|
|
|
|
| Reject if r<k |
|
|
|
|
|
|
|
|
|
|
| May accept or reject if
r=k |
|
|
|
|
|
|
|
|
|
|
| Where;
r = internal rate of return |
|
|
|
|
|
|
|
|
|
| k=cost of capital |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
| Payback Method |
|
|
|
|
|
|
|
| The payback method is a capital budgeting
technique that determines how long it takes for an investment to recoup its initial cost. |
|
|
|
|
|
|
| It's
a simple and quick way to assess the profitability of an investment by
calculating the time it takes for the cash flow |
|
|
|
|
|
|
| from
the investment to equal its original cost. |
|
|
|
|
|
|
|
|
|
|
| A shorter payback period generally
indicates a more attractive investment, as it signifies a quicker return on
investment. |
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
| Profitability Index
(PI) |
|
|
|
|
|
|
|
|
|
|
|
| The profitability index (PI) is a
financial metric that assesses the attractiveness of an investment by
comparing the present |
|
|
|
|
|
| value of future cash flows to the initial
investment. It essentially measures how much value is created per
dollar invested. |
|
|
|
|
|
| A PI greater than 1 suggests the project
is likely profitable, while a PI less than 1 indicates it might not be a good
investment. |
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
| Average
Rate Of Return (ARR) |
|
|
|
|
|
|
|
|
|
|
| The average rate of return (ARR) is a capital
budgeting method that calculates the average annual percentage return |
|
|
|
|
|
| on an investment. |
|
|
|
|
|
|
|
|
|
|
|
| It's a simple metric that helps businesses
assess the profitability of a project by comparing the average annual
profit |
|
| to
the initial investment. |
|
|
|
|
|
|
| Projects
that exceed the target ARR are considered acceptable, while those falling
below it are typically rejected. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|