Validation et Calcul de Swap
Uploaded by
Zineb LabiedValidation et Calcul de Swap
Uploaded by
Zineb LabiedCommon questions
Powered by AIIn the CalculateFixedLeg function, the discount factor for each payment is computed by first determining the time in years between the current date and the payment date using the DateDiff function with a 'd' parameter and dividing by 365. The function then retrieves the relevant zero-rate using the GetInterpolatedRate function based on the computed timeInYears. The discount factor is then calculated using this zero-rate by the formula: 1 / ((1 + zeroRate) ^ timeInYears). This gives the present value of each future payment .
The CalculateFixedLeg function ensures proper computation of present values for swap payments by iterating through each payment period within the swap's duration, from the start date to the maturity date. For each period, it calculates a payment amount based on the notional and interest rate, divides this by the payment interval, and discounts each future payment amount back to the present value using a discount factor. This factor is derived from the zero-coupon rates specific to each payment date, thus accurately accounting for the time value of money over multiple periods .
The number of payments in the CalculateFixedLeg function is calculated using the DateDiff function to find the total number of months between the startDate and maturityDate. This total number of months is then divided by the paymentInterval, which is determined based on the payment frequency (e.g., quarterly, semi-annual, annual). This division results in a long integer value that represents the number of payment periods over the life of the swap .
The CalculateSwapValue subroutine determines the net swap value by retrieving relevant swap parameters such as notional amounts, start and maturity dates, fixed and floating rates, and payment frequencies from the worksheet. It then calculates the value of the fixed and floating legs using CalculateFixedLeg and CalculateFloatingLeg functions. The polarity (sign) of these leg values is adjusted based on the direction of the swap (Pay Fixed or Pay Floating) as specified in the worksheet. Finally, the net swap value, which is the sum of the adjusted fixed and floating leg values, is displayed in cell L15 of the worksheet. This output is formatted to display two decimal places followed by "DH" to denote currency .
To enhance the Placeholder implementation of CalculateFloatingLeg, one could integrate the use of forward MONIA rates over the swap term, similar to the fixed leg. This would involve calculating cash flows at each determined interval and applying discount factors to determine present values. Additionally, the function could include adjustments based on historical data volatility or anticipated rate changes to reflect realistic market conditions. Error checks and more detailed validation for rates computation can also be added for robustness .
When the GetInterpolatedRate function does not find a matching interpolation for the given time in years, it defaults to using the current MONIA rate available in cell B6 of the worksheet. This default behavior ensures that the function returns a valid rate even if no direct match is found within the specified range A9:E19 of the worksheet .
The payment interval in the CalculateFixedLeg function is determined based on the frequency parameter by using a Select Case statement. For 'QUARTERLY', the interval is set to 3 months; for 'SEMI-ANNUAL', it is set to 6 months; and for 'ANNUAL', it is 12 months. If the frequency does not match any of these recognized values, the default interval is set to 3 months, assuming a quarterly payment frequency .
The CalculateFixedLeg function defaults to a quarterly payment frequency if an unrecognized value is provided to maintain consistency and ensure the function can proceed without errors. This default option is pragmatic, aligning with a common swap term frequency, thereby minimizing the risk of operational failure. However, this assumption might lead to inaccuracies if the true frequency intended was different (e.g., monthly, annually). Therefore, users must ensure feequency inputs are accurate and valid to avoid unintended financial implications .
The leg values in the CalculateSwapValue subroutine would be negative when the swap direction indicates a payment for that leg. Specifically, if cell I7 contains "Pay", the fixed leg value is negated, and similarly, if cell M7 contains "Pay", the floating leg value is negated. This adjustment reflects the cash outflow associated with making payments in a pay-fixed or pay-floating interest rate swap setup, ensuring the net result accurately represents financial inflow/outflow .
The ValidateInputs function requires several fields to be filled for it to return true. These fields include the notional amounts in cells J8 and M8, the dates in cells J9 and J10, and the payment frequencies in cells J12 and M12. The function checks if these fields are empty using the IsEmpty function. If any of these fields are empty, it displays a message box indicating which fields are not filled and returns false, thereby ensuring that no further calculations proceed until all mandatory information is provided .