Building Portfolios using FINCAD functions
Introduction
One of the main advantages of using FINCAD XL with Microsoft® Excel is the ability to modify and customize your spreadsheet. A common question asked of the FINCAD Client Services team is "How do you build a portfolio using one of FINCAD's functions?" This article addresses this question.
Within FINCAD XL v9 there are 6 portfolio templates:
- Cap and Floor
- CDS (Single Asset) - IMM Dates
- Bond
- Option
- Vanilla Interest Rate Swap
- Swaption
» Click to evaluate the lastest trial version of FINCAD Analytics.
This featured tip explains how to create an American option portfolio that can take in a discount factor curve for the risk free rate using the function aaBIN_curve, as an example.
Steps:
1) Start with the Swap Curve workbook. (FINCAD XL → Workbooks (User data) → Curves (Interest Rate)).
2) Add two worksheets (Sheet 1 and 2 for example) to this workbook (Insert → Worksheet).
3) In Sheet 2, paste an example of the function aaBIN_curve. To paste an example of a function, go to the Function Finder (FINCAD XL → Function Finder), type in the function name (aaBIN_curve) into the Search field, hit Search, select the function and hit 'Paste Example.'
4) At this stage, we want to ensure that every input is represented in the portfolio sheet that we are creating. Copy all of the function labels plus their inputs from the pasted example; go to Sheet 1, row 5 and Paste Special → Transpose. This will ensure the entire pasted cell labels are entered horizontally.
5) As some labels are going to share common inputs, we will need to delete duplicated columns. However, before we delete, we need rearrange the labels with the common inputs into some common cells. For example, move the following labels into cells A1, A2, A3 respectively: 'value (settlement date) date', 'interpolation method' and 'number of time steps' and move their inputs into cells B1, B2, B3. These labels and their inputs have now been duplicated and their columns can be deleted from row 5 as well as the discount factor curve - risk free. These common labels and their inputs will later be referenced back into the function as absolute values.
6) On Sheet 1, we can now paste the function into the cell beside the statistic input. This will result in a #VALUE. To paste a function, go to the Function Finder, type in the function name, aaBIN_curve, into the Search field, hit Search, select the function and hit 'Paste Function.'
7) In this example, the fair value for 'statistic' defaults to 1.
8) The next step is to link the function to the corresponding cells containing the inputs. This can best be done by going to the cell containing the function and selecting 'Function Wizard' (FINCAD XL →Function Wizard) from the FINCAD XL menu. This will bring up an arguments box allowing the user to reference the inputs. Within the FINCAD Function Wizard, reference each argument with its corresponding input. For example, set price_u = A6 = 100. Enter inputs for all remaining arguments and scroll down to complete this process.
9) For the argument 'df_crv_std 'use the named range df_curve. In this workbook, the sheet named 'Curve' will be used to calculate the appropriate risk free rate. Also, remember to set the 'curve date' field to be equal to the 'value (settlement) date' field and to update the sheet with the latest risk free interest rate data. Please remember to enter in the data for the curve. More information on entering in data for the curve can be found by going to the 'Curve' sheet and selecting the 'How to Use' button.
10) For the argument 'df_crv_hld' we will be using a single cell rate input rather than a curve. This function, along with many other functions, provides the user with added flexibility. Please refer to the Function Reference, and specifically the notes at the bottom for a list of the additional flexibility that is available.
11) Once the cells have been linked to the function, you can now enter in the appropriate data for your options on subsequent rows. Before you copy down the formula remember to absolute value the common cells.
General Comments:
1) Extra care needs to be taken when using functions that require the use of table inputs. For example, in an amortizing swap, a table would need to be built containing the changing notional. The best way to achieve this is to enter in table entries onto a separate sheet and reference them to the main page using named ranges.
2) In FINCAD XL v10, due for release in November 2006, the step of pasting an example, copying and then transposing the data will not be needed. The user will have the choice of either pasting the example vertically or horizontally.
3) Multiple statistics can be outputted using arrays. For more information on outputting multiple statistics, please contact your account manager.
4) The user can create switches for inputs that require a choice to be made. For example, the 'option type' input allows the user to select 1 for call or 2 for put. Rather than using these values, a switch could be created that displays "call" and "put" and is referenced into the function. The steps required for pasting switch controls can be found in the March 2005 newsletter "Working with Switches."
5) The risk free rate which has been entered through the curve sheet can also be replaced with a single cell parameter.
6) A Bloomberg Finance LP* and Reuter® enabled workbook that downloads the curve data is also available.
» For more information or a customized demonstration of the software, contact a FINCAD Representative.
Disclaimer
Your use of the information in this article is at your own risk. The information in this article is provided on an "as is" basis and without any representation, obligation, or warranty from FINCAD of any kind, whether express or implied. We hope that such information will assist you, but it should not be used or relied upon as a substitute for your own independent research.
* Bloomberg is a trademark of Bloomberg Finance LP. FINCAD is not associated in any way with Bloomberg Finance LP.