Question

• Suppose you are studying two hardware lease proposals. Option 1 costs $5,000, but requires that the entire amount be paid in advance. Option 2 requires $2000 now and $1,000 per year for the next four years. If you do an NPV analysis assuming a 12% discount rate, which proposal is less expensive? What happens if you use an 18% rate? (use Excel to your answer and how you apply it in excel)

Answer 1

Solution :

A. At 12 % discount rate :

NPV of Option 1 = - $ 5,000

NPV of Option 2 = - $ 5,037.35

The NPV of cash outflows of Option 1 is lower than that of Option 2. Hence, Option 1 is less expensive.

B. At 18 % discount rate

NPV of Option 1 = - $ 5,000

NPV of Option 2 = - $ 4,690.06

The NPV of cash outflows of Option 2 is lower than that of Option 1. Hence, Option 2 is less expensive.

Please find the attached screenshot of the excel sheet containing the detailed calculation for the solution.

05.11.2019 - Microsoft Excel ? - 6 XES FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW A B C G H Statement showing calculation of NPV of Cash Outflows of Option 1 at 12% discount rate Sl.No. Particulars Cash outflow PVF @ 12% Discounted Cash outflows (3) = (1) * (2) Lease payment Year 0 $ -5,000.00_ 1.0000 $ -5,000.00 NPV of Option 1 at 12% discount rate -5,000.00 (2) Statement showing calculation of NPV of Cash Outflows of Option 1 at 18% discount rate SI.No. Particulars Cash outflow PVF @18% Discounted Cash outflows (2) (3) = (1) * (2) 1 Lease payment Year 0 $ -5,000.00 1.0000 $ NPV of Option 1 at 18 % discount rate -5,000.00 |(1) -5,000.00 $ (2) Statement showing calculation of NPV of Cash Outflows of Option 2 at 12% discount rate Sl.No. Particulars Cash outflow PVF @ 12% Discounted Cash outflows (1) (3) = (1) * (2) 1 Cash Outflow - Year 0 $ -2,000.00 1.0000 $ -2,000.00 2 Cash Outflow - Year 1 $ -1,000.00 0.8929 $ -892.86 3 Cash Outflow - Year 2 $ -1,000.00 0.7972 $ -797.19 4 Cash Outflow - Year 3 $ -1,000.00 0.7118 $ -711.78 5 Cash Outflow - Year 4 $ - 1,000.00 0.6355 $ -635.52 NPV of Option 2 at 12% discount rate -5037.35 Statement showing calculation of NPV of Cash Outflows of Option 2 at 18% discount rate Sl.No. Particulars Cash outflow PVF @ 18% Discounted Cash outflows (1) (2) (3) = (1) * (2) 1 Cash Outflow - Year 0 $ -2,000.00 1.0000 $ -2,000.00 2 Cash Outflow - Year 1 $ -1,000.00 0.8475 $ -847.46 Cash Outflow - Year 2 -1,000.00 0.7182 $ -718.18 4 Cash Outflow - Year 3 $ -1,000.00 0.6086 $ -608.63 5 Cash Outflow - Year 4 $ -1,000.00 0.5158 $ -515.79 NPV of Option 2 at 18% discount rate -4690.06