Appendix E – Excel Verification

NPV, IRR & Economic Analysis
Excel Function Verification

EGN-2113 Case Study | New Manufacturing Plant Investment | Group 7

MARR: 12%
Project Life: 25 Years
Options Evaluated: A, B, D
Recommended: Abu Dhabi (A)
1
Net Present Value (NPV)
Excel Function: =NPV(rate, CF1:CFn) + CF0
NPV Formula

The NPV function discounts all future cash flows at the MARR. Year 0 investment is added separately since it is not discounted.

NPV = -P + NCF × (P/A, i%, n) + Salvage × (P/F, i%, n) Excel: =NPV(0.12, CF1:CF25) + CF0 where: CF0 = Initial Investment (negative) CF1–24 = Annual Net Cash Flow CF25 = NCF + Salvage Value i = 12% (MARR)
Option Excel Formula Initial Investment Annual NCF Salvage NPV Result
Abu Dhabi (A) ★ =NPV(0.12,F6:F30)+F5 -44,500,000 12,212,400 6,500,000 51,665,904
Dubai (B) =NPV(0.12,J6:J30)+J5 -45,400,000 12,250,560 9,000,000 51,212,256
Sharjah (D) =NPV(0.12,N6:N30)+N5 -37,500,000 10,341,069 4,500,000 43,871,148
NPV Comparison (AED) – All Options
2
Internal Rate of Return (IRR)
Excel Function: =IRR(CF0:CFn)
IRR Formula

IRR is the discount rate that makes NPV = 0. Accept if IRR ≥ MARR (12%).

IRR: solve for i* where NPV = 0 0 = -P + NCF × (P/A, i*%, n) + Salvage × (P/F, i*%, n) Excel: =IRR(F5:F30) F5 = Year 0 (negative initial investment) F6:F29 = Years 1–24 (annual NCF) F30 = Year 25 (NCF + Salvage) Decision Rule: Accept if IRR ≥ MARR (12%)
Option Excel Formula IRR Result vs MARR (12%) Decision
Abu Dhabi (A) ★ =IRR(F5:F30) 27.39% +15.39% ✅ Accept
Dubai (B) =IRR(J5:J30) 26.93% +14.93% ✅ Accept
Sharjah (D) =IRR(N5:N30) 27.53% +15.53% ✅ Accept
⚠️ All three options pass individual IRR. Since options are mutually exclusive, we must use Incremental IRR to choose between them (see Section 3).
3
Incremental IRR (ΔIRR)
For mutually exclusive alternatives ranked by ascending cost
Method

Rank options by initial cost (lowest first). Compare each pair using incremental cash flows. Accept the higher-cost option only if ΔIRR ≥ MARR.

Ranking by Initial Cost (Ascending): 1. Sharjah (D) → AED 37,500,000 ← Baseline (cheapest) 2. Abu Dhabi (A) → AED 44,500,000 3. Dubai (B) → AED 45,400,000 Step 1: Compare A vs D → ΔIRR(A−D) vs MARR Step 2: Compare B vs A → ΔIRR(B−A) vs MARR
1

Increment A − D (Abu Dhabi vs Sharjah)

Calculate year-by-year difference in cash flows, then find IRR of those incremental flows.

ΔCF₀ = -44,500,000 − (-37,500,000) = -7,000,000 ΔCF₁₋₂₄ = 12,212,400 − 10,341,069 = +1,871,331/year ΔCF₂₅ = 18,712,400 − 14,841,069 = +3,871,331 Excel: =IRR(ΔCF_range) → ΔIRR = 26.67% Decision: 26.67% ≥ 12% MARR → Choose A over D ✅
✅ ΔIRR(A−D) = 26.67% > MARR 12% → Select Abu Dhabi (A) over Sharjah (D)
2

Increment B − A (Dubai vs Abu Dhabi)

Calculate incremental flows of Dubai over Abu Dhabi, find IRR of those flows.

ΔCF₀ = -45,400,000 − (-44,500,000) = -900,000 ΔCF₁₋₂₄ = 12,250,560 − 12,212,400 = +38,160/year ΔCF₂₅ = 21,250,560 − 18,712,400 = +2,538,160 Excel: =IRR(ΔCF_range) → ΔIRR = 7.03% Decision: 7.03% < 12% MARR → Reject B, Keep A ❌
❌ ΔIRR(B−A) = 7.03% < MARR 12% → Reject Dubai (B), retain Abu Dhabi (A)
Increment ΔInvestment (AED) ΔNCF/yr (AED) ΔYear 25 (AED) ΔIRR vs MARR Decision
A − D -7,000,000 +1,871,331 +3,871,331 26.67% ≥ 12% ✅ Choose A
B − A -900,000 +38,160 +2,538,160 7.03% < 12% ❌ Reject B
4
Discounted Payback Period (DPP)
Year when cumulative discounted cash flow turns positive
DPP Formula
Discounted CF at year t = NCF_t × (P/F, 12%, t) = NCF_t / (1 + 0.12)^t Cumulative DCF = Sum of all discounted CFs up to year t DPP = year where Cumulative DCF first becomes ≥ 0 Exact DPP = Last negative year + |Last negative cum. DCF| / Next year's disc. CF

Abu Dhabi (A) – DPP Calculation

YearDisc. CF (AED)Cumulative (AED)
✅ DPP = 5.08 years

Dubai (B) – DPP Calculation

YearDisc. CF (AED)Cumulative (AED)
✅ DPP = 5.20 years

Sharjah (D) – DPP Calculation

YearDisc. CF (AED)Cumulative (AED)
✅ DPP = 5.04 years
5
Final Decision Summary
All methods point to the same recommendation
Recommended Option
Abu Dhabi
Option A – Highest NPV
NPV at 12%
51.67M
AED – highest of all options
IRR
27.39%
vs MARR 12% — strong return
Payback Period
5.08 yrs
Discounted payback
★ RECOMMENDED
Abu Dhabi (Option A)
Initial InvestmentAED 44,500,000
Annual NCF12,212,400
NPV51,665,904
IRR27.39%
ΔIRR vs D26.67% ✅
DPP5.08 years
Dubai (Option B)
Initial InvestmentAED 45,400,000
Annual NCF12,250,560
NPV51,212,256
IRR26.93%
ΔIRR vs A7.03% ❌
DPP5.20 years
Sharjah (Option D)
Initial InvestmentAED 37,500,000
Annual NCF10,341,069
NPV43,871,148
IRR27.53%
ΔIRRBaseline ❌
DPP5.04 years