Question

Below are three possible investments that follow a 5-year time horizon:

1. Invest in a basic human resource information system:

• Cost: $300K
• Cash Flows: $150K each year for 5 years

2. Invest in a high-end human resource information system:

• Cost: $1 Million
• Cash Flows: $500K each year for 5 years

3. Invest in a talent acquisition function

• Cost: $500K
• Cash Flows: $250 K each year for 5 years.

Assume that you will pay for one of these projects by withdrawing funds from an account earning 8%. 1. Build a spreadsheet (e.g., using Google Sheets) that models the cash flows appropriately.

2. Calculate the NPV and IRR for each project.

3. Pick the project that you would fund and explain why you picked it.

Answer 1

In the given question, i have prepared the excel model for each option.

In excel model, i have determined the cash flow for each year and then calculated the present value of each cash flow.

Formula used for present value of the cash flow is =

Cashflow at year X / ((1+COST OF BORROWING) ^ Number of year i.e. X)

After calculating present value of each cash flow in each option, i have summed all the cash flow and subtracted the initial investment.

For IRR, I have considered excel formula function since manual calculation would be based on hit and trial method. FOr each option, IRR is 41%.

You can refern to below screen shot for understand which option has higher NPV which is option 2. Hence I would choose Option 2 and it has highest NPV (note- IRR is same for all option).