VIPSolutions

VIPSolutions logo ✨ VIPSolutions

Home Dashboard Ask AI Archive

2. In Microsoft Excel, calculate the Payback Period, Internal Rate of Return (IRR), Net Present Value (NPV), and Profitability Index (PI) for each investment option (A, B and C).Investment A Initial Expenditure Year #1 Year #2 Year #3 Year #4 Year #5 Net Investment Cost ($700,000) Additional Revenue $100,000 = $200,000 $300,000 $450,000 $600,000 Additional Operating Expenses ($40,000) | ($40,000) = ($40,000) = ($40,000) ($40,000) Amortization ($42,424) | ($84,848) | ($127,273) ($190,909) ($254,545) Net Increase in Income ($700,000) $17,576 $75,152 $132,727 $219,091 = $305,455 Less: Tax @ 33% (65,800) | (524,800) = ($43,800) = ($72,300) ($100,800) Increase in Aftertax Income $11,776 $50,352 $88,927 $146,791 $204,655 Add back Amortization $42424 © $84,848 © $127,273 $190,909 = $254,545 Net Change in Cash Flow ($700,000) $54200 | $135200 $216,200 $337,700 $459,200 Discount Rate ? Investment B Initial Expenditure Year #1 Year #2 Year #3 Year #4 Year #5 Net Investment Cost ($540,000) Additional Revenue $382,500 | $325,125 $125,000 $100,000 $85,000 Additional Operating Expenses ($19,125) | ($19,125) = ($25,500) = ($31,875) = ($38,250) Amortization (6202,973) | ($172,527) ($66,331) ($53,065) = ($45,105) Net Increase in Income ($540,000) $160,402 $133473 $33,169 $15,060 $1,645 Less: Tax@ 33% ($52,933) | ($44,046) (510,946) ($4970) ($543) Increase in Aftertax Income $107,470 $89,427 $22,223 $10,090 $1,102 Add back Amortization $202,973 © $172527 = $66,331 $53,065 $45,105 Net Change in Cash Flow ($540,000) $310,442 $261,954 $88,554 $63,155 $46,207 Discount Rate ? Investment C Initial Expenditure Year #1 Year #2 Year #3 Year #4 Year #5 Net Investment Cost ($720,000) Additional Revenue $87,500 $175000 $262,250 $393,750 = $525,000 Additional Operating Expenses ($26,250) | ($26,250) = ($26,250) = ($26,250) = ($26,250) Amortization ($43,644) | ($87,288) | ($130,807) ($196,398) ($261,864) Net Increase in Income ($720,000) $17,606 $61,462 $105,193 $171,102 $236,886 Less: Tax @ 33% ($5,810) (520,283) ($34,714) ($56,464) = ($78,173) Increase in Aftertax Income $11,796 $41,180 $70,479 $114,639 $158,714 Add back Amortization $43644 | $87,288 $130,807 = $196,398 = $261,364 Net Change in Cash Flow ($720,000) $55,440 | $128467 $201,286 $311,036 $420,577 Discount Rate ?

Question:

2. In Microsoft Excel, calculate the Payback Period, Internal Rate of Return (IRR), Net Present Value (NPV), and Profitability Index (PI) for each investment option (A, B and C).Uploaded ImageUploaded ImageUploaded ImageInvestment A Initial Expenditure Year #1 Year #2 Year #3 Year #4 Year #5 Net Investment Cost ($700,000) Additional Revenue $100,000 = $200,000 $300,000 $450,000 $600,000 Additional Operating Expenses ($40,000) | ($40,000) = ($40,000) = ($40,000) ($40,000) Amortization ($42,424) | ($84,848) | ($127,273) ($190,909) ($254,545) Net Increase in Income ($700,000) $17,576 $75,152 $132,727 $219,091 = $305,455 Less: Tax @ 33% (65,800) | (524,800) = ($43,800) = ($72,300) ($100,800) Increase in Aftertax Income $11,776 $50,352 $88,927 $146,791 $204,655 Add back Amortization $42424 © $84,848 © $127,273 $190,909 = $254,545 Net Change in Cash Flow ($700,000) $54200 | $135200 $216,200 $337,700 $459,200 Discount Rate ? Investment B Initial Expenditure Year #1 Year #2 Year #3 Year #4 Year #5 Net Investment Cost ($540,000) Additional Revenue $382,500 | $325,125 $125,000 $100,000 $85,000 Additional Operating Expenses ($19,125) | ($19,125) = ($25,500) = ($31,875) = ($38,250) Amortization (6202,973) | ($172,527) ($66,331) ($53,065) = ($45,105) Net Increase in Income ($540,000) $160,402 $133473 $33,169 $15,060 $1,645 Less: Tax@ 33% ($52,933) | ($44,046) (510,946) ($4970) ($543) Increase in Aftertax Income $107,470 $89,427 $22,223 $10,090 $1,102 Add back Amortization $202,973 © $172527 = $66,331 $53,065 $45,105 Net Change in Cash Flow ($540,000) $310,442 $261,954 $88,554 $63,155 $46,207 Discount Rate ? Investment C Initial Expenditure Year #1 Year #2 Year #3 Year #4 Year #5 Net Investment Cost ($720,000) Additional Revenue $87,500 $175000 $262,250 $393,750 = $525,000 Additional Operating Expenses ($26,250) | ($26,250) = ($26,250) = ($26,250) = ($26,250) Amortization ($43,644) | ($87,288) | ($130,807) ($196,398) ($261,864) Net Increase in Income ($720,000) $17,606 $61,462 $105,193 $171,102 $236,886 Less: Tax @ 33% ($5,810) (520,283) ($34,714) ($56,464) = ($78,173) Increase in Aftertax Income $11,796 $41,180 $70,479 $114,639 $158,714 Add back Amortization $43644 | $87,288 $130,807 = $196,398 = $261,364 Net Change in Cash Flow ($720,000) $55,440 | $128467 $201,286 $311,036 $420,577 Discount Rate ?

This is AI generated Response.
Asked by: Ubaid
Created at: 2025-07-24 14:49:12

Final Answer:

Rendered Raw Text
Did you like this answer?
Let's break down the calculations for **Payback Period**, **IRR**, **NPV**, and **Profitability Index (PI)** for Investments A, B, and C using the cash flows provided. You can use the following Excel formulas and steps: --- ### 1. **Set Up Cash Flows** For each investment, the cash flows (from "Net Change in Cash Flow" row) are: #### **Investment A** Year 0: -700,000 Year 1: 54,200 Year 2: 135,200 Year 3: 216,200 Year 4: 337,700 Year 5: 459,200 #### **Investment B** Year 0: -540,000 Year 1: 310,442 Year 2: 261,954 Year 3: 88,554 Year 4: 63,155 Year 5: 46,207 #### **Investment C** Year 0: -720,000 Year 1: 55,440 Year 2: 128,467 Year 3: 201,286 Year 4: 311,036 Year 5: 420,577 --- ### 2. **Excel Formulas** #### **A. Payback Period** Manual Calculation: - Cumulatively add the cash flows until the initial investment is recovered. #### **B. IRR** - Use the formula: `=IRR(range_of_cash_flows)` #### **C. NPV** - Assume a discount rate, e.g., 10% (replace with your actual rate if known). - Formula: `=NPV(discount_rate, range_of_cash_flows_excluding_initial) + initial_cash_flow` #### **D. Profitability Index (PI)** - Formula: `=NPV(discount_rate, range_of_cash_flows_excluding_initial) / ABS(initial_cash_flow)` --- ### 3. **Example in Excel** #### **Investment A Example** Suppose your cash flows are in cells **B10:G10** (B = Year 0, C = Year 1, ... G = Year 5). - **Payback Period** (manual, see below) - **IRR**: `=IRR(B10:G10)` - **NPV** (using 10%): `=NPV(10%,C10:G10)+B10` - **PI**: `=NPV(10%,C10:G10)/ABS(B10)` #### **Payback Period Calculation (Manual Steps):** Sum cumulative cash flows year by year until the initial investment is paid back: - Year 0: -700,000 - Year 1: -700,000 + 54,200 = -645,800 - Year 2: -645,800 + 135,200 = -510,600 - Year 3: -510,600 + 216,200 = -294,400 - Year 4: -294,400 + 337,700 = +43,300 ⇒ **payback achieved during year 4** Payback Period ≈ 3 + (294,400 / 337,700) ≈ **3.87 years** --- #### **Repeat for B and C** ### **Investment B** - Cumulative Cash Flow: - Year 0: -540,000 - Year 1: -229,558 - Year 2: +32,396 ⇒ **payback during year 2** - Fraction in Year 2: 229,558 / 261,954 ≈ 0.88 - Payback Period ≈ 1.88 years ### **Investment C** - Cumulative Cash Flow: - Year 0: -720,000 - Year 1: -664,560 - Year 2: -536,093 - Year 3: -334,807 - Year 4: -23,771 - Year 5: +396,806 ⇒ **payback during year 5** - Fraction in Year 5: 23,771 / 420,577 ≈ 0.06 - Payback Period ≈ 4.06 years --- ## **Summary Table** | Investment | IRR (Excel) | NPV (10%) (Excel) | PI (Excel) | Payback Period (Years) | |------------|--------------|-------------------|------------|------------------------| | A | `=IRR(B10:G10)` | `=NPV(10%,C10:G10)+B10` | `=NPV(10%,C10:G10)/ABS(B10)` | 3.87 | | B | `=IRR(B10:G10)` | `=NPV(10%,C10:G10)+B10` | `=NPV(10%,C10:G10)/ABS(B10)` | 1.88 | | C | `=IRR(B10:G10)` | `=NPV(10%,C10:G10)+B10` | `=NPV(10%,C10:G10)/ABS(B10)` | 4.06 | --- ## **Instructions for Excel** 1. **Copy the cash flows into Excel.** 2. **Use the above formulas for IRR, NPV, PI.** 3. **Calculate payback period manually as shown above.** If you need the exact computed values for IRR, NPV, and PI, please specify the discount rate, or use the default 10% as above. --- **Let me know if you want the calculations done with a specific discount rate, or if you need the Excel file template!**

📢 Instructions:

  • Ask anything relatd to above question only.
  • For new Question goto :
    Ask AI

Related Answers

Study Help, Fast Answers

VIPSolutions focuses on clear, step‑by‑step explanations so you can learn quickly. Whether you need an AI‑generated walkthrough or a short hint to get unstuck, each solution is organized for fast reading and easy review later.

Search similar questions, compare approaches, and bookmark the best answers for revision. Our goal is simple: quick, reliable study help that feels natural—not noisy.