Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Loan Amount | Interest Rate | Loan Term | Start Date  |

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

| 10000       | 5%            | 3 years   | 2023-01-01  |

 

Task :

1. Calculate the total investment value for each stock using a formula (Purchase Price * Quantity).
2. Use XNPV function to calculate the net present value of cash flows from Stock A.
3. Calculate the annualized return for each stock using IRR function.
4. Use TEXTJOIN function to concatenate stock names that have increased in price.
5. Create a scatter plot to visualize the relationship between Purchase Price and Current Price.

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

Steps to Perform:

1. Calculate Total Investment Value for Each Stock:
   • Use a formula to calculate the total investment value for each stock, which is Purchase Price * Quantity (assuming Quantity is 1 for each stock).
   • Example: =C2 * 1 assuming Purchase Price is in column C and Quantity is 1.
2. Use XNPV Function to Calculate Net Present Value (NPV):
   • Use the XNPV function in Excel to calculate the net present value of cash flows for Stock A.
   • Example: =XNPV(rate, cashflow_range, dates_range) where rate is the discount rate, cashflow_range are the cash flows, and dates_range are the corresponding dates.
3. Calculate Annualized Return Using IRR Function:
   • Use the IRR function in Excel to calculate the annualized return for each stock.
   • Example: =IRR(cashflow_range) where cashflow_range represents the cash flows (purchase price as negative and current price as positive).
4. Use TEXTJOIN Function to Concatenate Stock Names with Increased Prices:
   • Use the TEXTJOIN function to concatenate the names of stocks that have increased in price.
   • Example: =TEXTJOIN(", ", TRUE, IF(current_price > purchase_price, stock_name, "")) assuming current price is in column D, purchase price in column C, and stock names in column A. Enter as an array formula using Ctrl + Shift + Enter.
5. Create a Scatter Plot to Visualize Purchase Price vs. Current Price:
   • Select the data range including Purchase Price and Current Price columns.
   • Go to Insert > Scatter (or Bubble) Chart > Scatter to create the chart.
   • Customize the chart with appropriate titles, axis labels, and legend to visualize the relationship between purchase price and current price.

Detailed Steps:

• Step 1: Calculate Total Investment Value
  • Enter the formula =C2 * 1 in a new column (assuming Purchase Price is in column C and Quantity is 1) to calculate the total investment value for each stock.
• Step 2: Use XNPV Function
  • Enter =XNPV(rate, B2:B3, A2:A3) where rate is the discount rate, B2 are cash flows (negative for purchase, positive for current value), and A2 are dates (purchase date and current date).
• Step 3: Calculate Annualized Return Using IRR Function
  • Enter =IRR({-C2, D2}) assuming C2 is the purchase price and D2 is the current price for Stock A. Do this similarly for Stocks B and C.
• Step 4: Use TEXTJOIN Function
  • Enter {=TEXTJOIN(", ", TRUE, IF(D2:D4 > C2:C4, A2:A4, ""))} where D2 is the current price range, C2 is the purchase price range, and A2 is the stock name range. Press Ctrl + Shift + Enter to enter this as an array formula.
• Step 5: Create Scatter Plot
  • Select the data range including Purchase Price and Current Price columns.
  • Go to Insert > Scatter Chart > Scatter to create the chart.
  • Customize chart elements (title, axis labels) as needed for clarity.