task: I'm not sure how to make this on excel can you perform it on an excel document using the following guide ...

Information : You have been asked to provide analysis for and advice to an Australian-based, high net worth client, who is considering the purchase of a fixed-term investment portfolio (FIP) issued by an Australian Bank. Your client wishes to be advised on the financial risks to which they would be exposed in association with the purchase.

You have the following information on this investment, as well as other information on key financial variables:

• You should assume the end of June 2024 as 'now/today' for the purpose of undertaking your calculations
• The FIP consists of three portions: Security A, B, and C. The maturity is one year for all securities
• The payoff for security A is AUD10,000 + R_ASX200T x AUD10,000
• R_ASX200T represents the rate ofreturn of the ASX 200Indexfrom now until the maturity of the FIP
• The payoff for Security B is HKD60,000
• The payoff of security C is AUD20,000 - (GOLD_T/GOLD₀) x AUD10,000
• GOLD_Trepresents thevalue of gold in AUDat maturity of the FIP andGOLD₀ thevalue of gold in AUDat inception of the FIP (i.e., the end of June 2024)
• Your client is funding this purchase partially with a U.S. based debt facility. At the end of one year, your client needs to pay USD5,000
• We will perfrom detailed breakdown of each component of the FIP, made up of three securities, A, B, and C, providing formulas for payoffs, historical performance, scenario analysis, and methods of risk assessment such as Value-at-Risk. The analysis is important to advise an Australia-based high-net-worth client on the financial risks associated with the given investment, especially in extreme market conditions.

Explanation:

1. Description of the Securities and Payoff Formulas Security A: The payoff depends upon the result of the performance of the ASX 200 index. Payoff Formula: AUD10,000 + (RASX200T AUD10,000), where RASX200T is the return on the ASX 200 from "now" to maturity. Risk: It will be sensitive to any fall in the level of the ASX 200 index-the additional return might be decreased or even totally eaten into, thus increasing the risk of a low payoff. Security B: This is the security which is going to pay back in HKD currency that needs to be converted into AUD currency.

Payoff Formula: HKD 60,000 at maturity after conversion at HKDAUD rate Risk: As the AUD payoff varies due to change in the currency exchange rate between HKD and AUD currency. That means in case the AUD becomes strong in comparison with HKD currency, then the ultimate payoff's value would decrease. Security C: This security is inversely proportional to Gold prices in AUD.

Payoff formula : AUD 20,000 - (GOLDT/GOLD0 x AUD 10,000), where GOLDT and GOLD0 is Gold price in AUD at maturity and inception respectively. Risk: Payoff decreases, if the gold price increases. Thus, this security is vulnerable to gold price volatility-a strong appreciation of gold prices may result in a lower return. 2. Scenario Analysis Since each security is subject to varying risk factors-ASX 200, HKDAUD exchange rate, and Gold Price-we will model possible payoff scenarios of normal adverse conditions to estimate risk under various market conditions. Outlier Scenarios ASX 200 Decline (-20%) and Gold Price Rise (+25%):

Security A: When the value of ASX 200 declines to -20%, its rate of return would significantly decline, which in turn will impact the payoff. The payoff for Security C would go down because there's an inverse relationship when gold prices went up by 25%. The value of Security B would either go down or up based on several variables of fluctuations in exchange rates when the market is under stress, more so during the instances where the AUD appreciates against the HKD. AUD Depreciation Against USD:

This generally affects the debt repayment in USD as a depreciated AUD makes the USD denominated-debt costly in terms of AUD. This is an exogenous factor from the direct calculation of the FIP payoff, but it influences the bottom line profitability taking debt obligations into consideration. Volatile Exchange Rates HKDAUD:

Here, significant fluctuations in the exchange rate HKD/AUD could significantly change the payoff associated with Security B. HKDAUD historical exchange rate data, especially in periods where the economy was unstable will be applied in the estimation of this risk. 3. Estimation of Risk and Value-at-Risk Analysis a. Value-at-Risk Value-at-Risk is the estimate of an expected maximum loss in a portfolio for a set confidence levels, for example, 95% and 99%, over one year. Provided here is the VaR calculation methodology for each security from historical data and simulated adverse scenarios.

Security A-ASX 200 Exposure:

Calculation: We used historical volatility in the ASX 200 to estimate the 99% VaR. It offers very good quantification of possible losses if the ASX 200 falls dramatically. Estimate: For example, a 1-year 99% VaR might estimate possible loss at AUD15,000, based on assumption of extreme historical volatility. Security B (Currency Exposure):

Calculation: The history volatility for the HKDAUD return is taken into account in the estimation of VaR. The estimation represents variation in value for AUD due to the change in currency. Estimate: For example, based on estimation that might be AUD8,000 estimated 1-year 99% VaR on principal exposure of AUD200,000. Security C (Gold Exposure):

Computation: Since gold is so volatile, VaR is computed using historical prices, and the worst possible upticks in the price of gold are emphasized. Estimate: Under these circumstances, the 1-year 99% VaR for this security might be approximately around AUD 10,000 for an investment of AUD 300,000. b. Expected Shortfall (Conditional VaR) Beyond VaR, Expected Shortfall gauges losses in the worst possible cases: Security A: An estimated shortfall of AUD18,000 for extreme ASX declines. Security B: An amount of about AUD9,000 because of currency volatility under extreme conditions. Security C: An estimated shortcoming of about AUD12,000 if the price of gold increases. 4. Portfolio-Level Risk and Interpretation Aggregating risk across securities while accounting for possible correlations produces a better picture of how the extreme market conditions affect the portfolio payoff. Overall, VaR: From this combined estimated VaR of about AUD28,000, one is likely to incur loss during the worst market conditions. Aggregate Expected Shortfall: An aggregate expected shortfall of about AUD33,000 is an indication of the loss severity in extreme scenarios. 5. Recommendations to the Client Market Risk: There are quite a number of significant risks to which the client is exposed: ASX 200 market movements, gold prices, and currency rates-particularly in trying times. The extreme scenarios outlined show a great potential for loss, hence caution is very warranted there. Diversification: Since each of these securities exposes differently, diversification in the portfolio-for example, with other assets less correlated with the ASX 200, gold, or HKD exchange rate-could lower risk. Debt Management: Consider hedging strategies against AUD/USD currency risk, since the repayment in USD adds further exposure. Analysis in Excel Formulate an Excel workbook based on the given data. The workbook should include separate sheets for historical data, scenario modelling, and calculation of VaR. Tabulate the simulated payoffs for each security for the generated scenarios. Apply the VaR and Expected Shortfall formulae.

task: im not sure how to make this on excel can you provide an excel booklet doing the following using the necessary guide and any additional workings ...

To analyze and advise on the potential payoffs and risks associated with this Fixed-Term Investment Portfolio (FIP), we can approach this in Excel by setting up each component (Securities A, B, and C) to calculate payoffs under various scenarios. The steps below outline how to set up Excel formulas and analyses, including using statistical techniques like Value at Risk (VaR) and Expected Shortfall. Each section explains how the calculations work and provides insights for discussing the results with your client.

Step 1: Set Up Initial Variables and Payoff Formulas

Enter Input Data and Initial Variables

• Use the provided time series data (gold prices, ASX 200 index values, exchange rates) in columns for each variable. Set "End of June 2024" values for GOLD0 (initial gold price), ASX200 (initial ASX 200 index value), HKDAUD (HKD to AUD exchange rate), and USDAUD (USD to AUD exchange rate).
• Define cells for these initial values:
  • GOLD0 (e.g., cell B2)
  • ASX2000 (e.g., cell B3)
  • HKDAUD0 (e.g., cell B4)
  • USDAUD0 (e.g., cell B5)

Define Payoff Formulas for Each Security

• Security A (Linked to ASX 200 Performance):
  • Formula: =10000 + (ASX200T / ASX2000) * 10000
  • In Excel, link ASX200T to a future projected ASX 200 index value. Assume different scenarios where ASX200T represents potential outcomes (e.g., +10%, -10%).
• Security B (Fixed in HKD, subject to exchange rate):
  • Convert HKD payoff to AUD using HKDAUDT.
  • Formula: =60000 * HKDAUDT
  • Make a cell to enter the projected HKDAUDT, based on exchange rate scenarios (e.g., 5%).
• Security C (Gold-Linked):
  • Formula: =20000 - (GOLDT / GOLD0) * 10000
  • Set GOLDT as the gold price at maturity, with scenario-based changes (e.g., 15%).

Step 2: Calculate Payoff Scenarios for Each Security

Create Scenario Table

• In a separate table, set up columns for different values of ASX200T, HKDAUDT, and GOLDT based on possible market conditions.
• Include realistic scenarios such as:
  • ASX200T increases or decreases by 10%, 20%.
  • HKDAUDT fluctuates by 5% from its initial value.
  • GOLDT changes by 15% and 25%.
• In each scenario, use the formula cells to calculate the payoffs for Securities A, B, and C.

Aggregate Total Payoff

• For each scenario, sum up the individual payoffs for Securities A, B, and C.
• Example formula: =Payoff_A + Payoff_B + Payoff_C

Step 3: Conduct Value at Risk (VaR) Analysis

Calculate Historical Returns and Standard Deviations

• Using historical data (ASX 200, gold prices, HKDAUD exchange rate), calculate returns for each series by finding the percentage change between consecutive periods.
  • Formula: (End Value - Start Value) / Start Value
• Calculate the standard deviation of returns for each series to estimate volatility.

Determine VaR for Each Security

• Set VaR at a chosen confidence level (e.g., 95% or 99%) based on the distribution of historical returns. Use the Excel function NORM.INV for calculating VaR:
  • =NORM.INV(confidence_level, mean_return, standard_deviation) * Portfolio Value
• For Example:
  • If using a 99% confidence level for Security A:
    • VaR_A = NORM.INV(0.99, mean_return_A, stdev_return_A) * Investment Amount

Combine VaR Values for Portfolio VaR

• Use the square root of the sum of squared individual VaRs, assuming correlations are considered. Excel formula:
  • =SQRT(VaR_A^2 + VaR_B^2 + VaR_C^2)
• This yields the overall portfolio VaR, giving an estimate of maximum loss with a given confidence level.

Step 4: Expected Shortfall (Conditional VaR)

Calculate Conditional VaR (CVaR) for Each Security

• Conditional VaR is the average of losses that exceed VaR. For each security:
  • Identify the returns beyond the VaR threshold.
  • Calculate the average of these extreme losses.
• For more precise results, use historical simulation or bootstrap methods for worst-case scenario outcomes.

Aggregate CVaR for Portfolio

• As with VaR, sum up the individual CVaRs to estimate the portfolio's expected shortfall.

Step 5: Final Risk Analysis and Discussion

Interpreting Payoff Scenarios

• Based on scenario analysis, discuss how extreme movements in the ASX 200, gold prices, and exchange rates impact each security.
• Highlight cases where market downturns could lead to negative returns, e.g., when ASX 200 drops by 20% and gold rises by 25%.

Portfolio-Level Risk Evaluation

• Discuss the combined VaR and CVaR values for the portfolio. Explain how these metrics offer insights into potential losses:
  • VaR: Provides a threshold below which losses should not exceed a certain percentage of the time.
  • Expected Shortfall (CVaR): Assesses extreme losses, offering a better view of worst-case outcomes.

Key Takeaways for the Client

• Emphasize the portfolio's exposure to specific risks like ASX 200 index declines and AUD exchange rate fluctuations.
• Offer guidance on the potential impact on AUD payoff amounts due to currency risk, particularly if the client's debt facility is in USD.
• Discuss diversification of risk by balancing securities linked to different assets (ASX 200, HKD, gold).

Final Presentation of Results in Excel

1. Create Summary Table
   • Compile final payoff estimates, VaR, and CVaR results for each security and the overall portfolio.
   • Visualize scenarios using charts to illustrate potential losses and gain clarity on extreme adverse conditions.

This Excel-based analysis provides the client with a clear view of potential returns, risks, and extreme outcomes, along with structured guidance for decision-making, discuss the outputs.

heres the excel case data

Date	GOLD	USDAUD	HKDAUD	ASX200			Notes:
30-Jun-14	13.436	0.9379	0.1368	5395.747			HKDAUD	AUD amount for 1 HKD
31-Jul-14	13.345	0.9363	0.1388	5632.914			USDAUD	USD amount for 1 AUD
31-Aug-14	13.22	0.9328	0.1381	5625.895			ASX200	The index value of the top 200 Australian Company
30-Sep-14	13.338	0.8707	0.1473	5292.812			GOLD	Gold Price in AUD
31-Oct-14	13.015	0.8774	0.1467	5526.602
30-Nov-14	13.372	0.8498	0.1514	5313
31-Dec-14	14.028	0.8123	0.1576	5411.018
31-Jan-15	15.5	0.7762	0.166	5588.321
28-Feb-15	14.83	0.7804	0.1647	5928.767
31-Mar-15	14.826	0.7581	0.1689	5891.505
30-Apr-15	14.42	0.7896	0.1635	5789.983
31-May-15	14.826	0.7649	0.1686	5777.16
30-Jun-15	14.665	0.7685	0.1677	5459.01
31-Jul-15	14.171	0.7276	0.1757	5699.164
31-Aug-15	15.132	0.7118	0.1819	5206.976
30-Sep-15	15.35	0.6988	0.1837	5021.629
31-Oct-15	15.5	0.7117	0.181	5239.439
30-Nov-15	14.026	0.7228	0.1781	5166.518
31-Dec-15	13.878	0.7266	0.1772	5295.859
31-Jan-16	15.295	0.7053	0.1812	5005.524
29-Feb-16	16.449	0.7132	0.18	4880.926
31-Mar-16	15.31	0.765	0.1677	5082.785
30-Apr-16	15.5	0.7587	0.1691	5252.217
31-May-16	15.946	0.7213	0.1776	5378.56
30-Jun-16	16.9	0.7424	0.1734	5233.375
31-Jul-16	16.894	0.7591	0.1696	5562.358
31-Aug-16	16.646	0.751	0.1716	5433.033
30-Sep-16	16.649	0.7649	0.1685	5435.921
31-Oct-16	15.956	0.7592	0.1696	5317.732
30-Nov-16	15.13	0.7386	0.1744	5440.472
31-Dec-16	15.228	0.7202	0.1781	5665.791
31-Jan-17	15.073	0.7578	0.1698	5620.911
28-Feb-17	15.528	0.7666	0.1676	5712.221
31-Mar-17	15.45	0.7636	0.1687	5864.905
30-Apr-17	16.118	0.748	0.172	5924.064
31-May-17	16.1	0.7432	0.1724	5724.572
30-Jun-17	15.366	0.7681	0.167	5721.494
31-Jul-17	15.07	0.7997	0.1603	5720.591
31-Aug-17	15.66	0.7948	0.1611	5714.522
30-Sep-17	15.539	0.8	0.1632	5681.61
31-Oct-17	15.75	0.8049	0.1673	5909.017
30-Nov-17	16.036	0.8049	0.1686	5969.892
31-Dec-17	15.736	0.8049	0.1635	6065.129
31-Jan-18	15.7	0.8049	0.1577	6037.683
28-Feb-18	15.957	0.7778	0.164	6015.959
31-Mar-18	16.37	0.7675	0.1657	5759.365
30-Apr-18	16.5	0.753	0.1688	5982.732
31-May-18	16.281	0.7567	0.1684	6011.883
30-Jun-18	16.005	0.7397	0.1725	6194.633
31-Jul-18	15.537	0.7438	0.1714	6280.201
31-Aug-18	15.685	0.718	0.1762	6319.498
30-Sep-18	15.482	0.7224	0.1767	6207.561
31-Oct-18	16.198	0.7075	0.1799	5830.307
30-Nov-18	15.795	0.7305	0.175	5667.157
31-Dec-18	17.05	0.7045	0.1814	5646.369
31-Jan-19	17.117	0.7267	0.1748	5864.654
28-Feb-19	17.394	0.7095	0.1791	6168.992
31-Mar-19	17.122	0.7109	0.1793	6180.731
30-Apr-19	17.174	0.7052	0.1811	6325.466
31-May-19	17.585	0.6943	0.1841	6396.852
30-Jun-19	19	0.7023	0.1824	6618.772
31-Jul-19	19.599	0.6844	0.1853	6812.56
31-Aug-19	21.432	0.6731	0.1894	6604.215
30-Sep-19	20.826	0.6755	0.1891	6688.348
31-Oct-19	20.32	0.6891	0.1852	6663.374
30-Nov-19	20.242	0.6767	0.1888	6845.996
31-Dec-19	20.407	0.7027	0.1826	6684.075
31-Jan-20	21.955	0.67	0.1925	7017.222
29-Feb-20	23.662	0.6524	0.1993	6441.21
31-Mar-20	24.572	0.6142	0.2108	5076.827
30-Apr-20	24.58	0.6523	0.1971	5522.353
31-May-20	24.221	0.666	0.1944	5755.69
30-Jun-20	24.23	0.6898	0.1874	5897.882
31-Jul-20	25.67	0.7147	0.18	5927.781
31-Aug-20	25.098	0.7389	0.1744	6060.462
30-Sep-20	24.872	0.7162	0.1801	5815.941
31-Oct-20	24.899	0.7029	0.1836	5927.58
30-Nov-20	22.408	0.7355	0.1751	6517.807
31-Dec-20	23	0.7696	0.1671	6587.096
31-Jan-21	22.526	0.7641	0.1681	6607.357
28-Feb-21	21.081	0.7711	0.1665	6673.268
31-Mar-21	20.654	0.76	0.1689	6790.666
30-Apr-21	21.255	0.7705	0.1667	7025.817
31-May-21	23.053	0.7738	0.1664	7161.627
30-Jun-21	21.873	0.7496	0.1715	7313.023
31-Jul-21	23.099	0.7335	0.175	7392.624
31-Aug-21	23.123	0.7321	0.176	7534.9
30-Sep-21	22.394	0.7232	0.1779	7332.159
31-Oct-21	22.154	0.7526	0.1712	7323.737
30-Nov-21	23.39	0.712	0.1808	7255.97
31-Dec-21	23.37	0.7277	0.1765	7444.642
31-Jan-22	23.753	0.7074	0.1819	6971.632
28-Feb-22	24.794	0.7264	0.1764	7049.125
31-Mar-22	23.924	0.7489	0.1701	7499.588
30-Apr-22	24.817	0.7084	0.1792	7435.013
31-May-22	24.01	0.7177	0.1777	7211.172
30-Jun-22	24.5	0.6907	0.1853	6568.064
31-Jul-22	23.39	0.6997	0.1826	6945.15
31-Aug-22	23.23	0.6847	0.1858	6986.757
30-Sep-22	23.84	0.6409	0.1981	6474.198
31-Oct-22	23.8	0.6395	0.1992	6863.459
30-Nov-22	24.31	0.6794	0.1913	7284.173
31-Dec-22	24.89	0.6814	0.1889	7038.689
31-Jan-23	25.31	0.7056	0.1811	7476.661
28-Feb-23	25.02	0.6737	0.1889	7258.399
31-Mar-23	27.33	0.6686	0.1902	7177.755
30-Apr-23	27.8	0.6619	0.1928	7309.152
31-May-23	28.03	0.6498	0.1974	7091.311
30-Jun-23	26.62	0.6667	0.1917	7203.299
31-Jul-23	27.07	0.6721	0.1903	7410.424
31-Aug-23	27.76	0.6478	0.1969	7305.269
30-Sep-23	26.73	0.6442	0.1978	7048.637
31-Oct-23	29.05	0.6345	0.2019	6780.678
30-Nov-23	28.44	0.6612	0.1931	7087.334
31-Dec-23	27.96	0.6818	0.1877	7590.818
31-Jan-24	28.6	0.6599	0.1935	7680.718
29-Feb-24	28.81	0.6497	0.1962	7698.699
31-Mar-24	31.02	0.6522	0.1959	7896.858
30-Apr-24	32.86	0.6485	0.1968	7664.075
31-May-24	32.56	0.6646	0.1922	7701.739
30-Jun-24	32.39	0.6672	0.1918	7767.47
Source iress.com Sep 2024