Sample Data

|------------|---------------|---------------|------------|

| Stock A | 10000 | 12000 | |

| Stock B | 15000 | 14500 | |

| Bond C | 20000 | 20500 | |

Task :

Calculate the return on investment (ROI) for each investment using a formula ((Current Value - Initial Value) / Initial Value * 100).
Use XIRR function to calculate the annualized return for the entire investment portfolio.
Create a waterfall chart to visualize the contribution of each investment to the overall portfolio return.
Use INDEX and MATCH functions to find the investment with the highest ROI.
Build a sensitivity analysis table to analyze the impact of varying Current Values on overall portfolio return.

To perform the tasks outlined in Assignment using Excel, follow these step-by-step instructions:

Step to Solve:

Investment Portfolio Analysis: Step-by-Step Guide

1. Calculate Return on Investment (ROI) for Each Investment

To calculate the ROI for each investment, use the formula:

$ROI (%)=(Current Value−Initial ValueInitial Value)×100\text{ROI (\%)} = \left( \frac{\text{Current Value} - \text{Initial Value}}{\text{Initial Value}} \right) \times 100$ ROI (%)=(Initial ValueCurrent Value−Initial Value)×100

Apply this formula for each investment:

· Stock A:

Initial Value: $10,000
Current Value: $12,000
ROI = $(12,000−10,00010,000)×100=20%\left( \frac{12,000 - 10,000}{10,000} \right) \times 100 = 20\%$ (10,00012,000−10,000)×100=20%

· Stock B:

Initial Value: $15,000
Current Value: $14,500
ROI = $(14,500−15,00015,000)×100=−3.33%\left( \frac{14,500 - 15,000}{15,000} \right) \times 100 = -3.33\%$ (15,00014,500−15,000)×100=−3.33%

· Bond C:

Initial Value: $20,000
Current Value: $20,500
ROI = $(20,500−20,00020,000)×100=2.5%\left( \frac{20,500 - 20,000}{20,000} \right) \times 100 = 2.5\%$ (20,00020,500−20,000)×100=2.5%

2. Use XIRR Function to Calculate Annualized Return for the Entire Portfolio

To calculate the annualized return for the entire investment portfolio using XIRR:

Create a table with dates and cash flows (investments' initial values as negative cash flows and current values as positive cash flows).
Use Excel's XIRR function to calculate the annualized return.

Example: Assuming initial investments occurred on 01-Jan-2023 and current values are on 31-Dec-2023, the XIRR function could be used as follows:

=XIRR({-10000, -15000, -20000, 12000, 14500, 20500}, {DATE(2023,1,1), DATE(2023,1,1), DATE(2023,1,1), DATE(2023,12,31), DATE(2023,12,31), DATE(2023,12,31)})

This will give you the annualized return for the entire portfolio.

3. Create a Waterfall Chart to Visualize Contribution to Overall Portfolio Return

To visualize the contribution of each investment to the overall portfolio return using a waterfall chart:

Arrange your data to include columns for investments and their ROI contributions.
Insert a waterfall chart from Excel's chart options, selecting the ROI contributions as values.

Waterfall charts visually show how each investment contributes positively or negatively to the total portfolio return.

4. Use INDEX and MATCH Functions to Find Investment with Highest ROI

To find the investment with the highest ROI using INDEX and MATCH:

=INDEX(A2:A4, MATCH(MAX(D2:D4), D2:D4, 0))

Assuming Investment names are in A2

and ROI values are in D2

. This formula will return the name of the investment with the highest ROI.

5. Build a Sensitivity Analysis Table for Impact of Varying Current Values

To analyze the impact of varying Current Values on overall portfolio return:

Create a table where you vary the Current Values of each investment.
Calculate the ROI and overall portfolio return (using XIRR) for each scenario.
Use Excel formulas to automate these calculations.

This sensitivity analysis table helps in understanding how changes in individual investments' values affect the entire portfolio's return.