PnL Explained in Excel when trading USD Interest Rate Swaps
In one of my earlier posts I have shown how to use Deriscope to calculate the Carry and Roll of a single interest rate swap.
There, I had defined the Carry and Roll-Down CR as:
CR = PV(CFinterm) + PV(T) - PV(T0)
CFinterm stands for the cash flows received between the today's date T0 and some future horizon date T (including T, but not T0)
PV(CFinterm) is the Present Value of the cash flows CFinterm
PV(T) is the Present Value of the swap at the horizon time T, under the assumption mentioned above
PV(T0) is the Present Value of the swap at the present time T0
As explained in the respective post, the CR is the sum of two parts: The PV(CFinterm) commonly referred as Carry and the PV(T) - PV(T0) commonly referred as Roll-Down.
Carry and Roll-Down is a forecasting trading tool. It is based on the market data available today - at T0 - and the assumption that all yield curves evolve in such a fashion that by T they look like – relative to T – exactly as they do at T0 relative to T0. An additional assumption is that any index resets occurring between T0 and T are assumed equal to their corresponding forward values as calculated at T0.
When the time T has been reached, i.e. when both T0 and T are in the past, there are no unknowns up to T that need to be forecast. The interim cash flows CFinterm will have been realized and therefore be known. The same applies for the yield curves at T. They will be whatever the market decides them to be at T.
Given these two new definitions for CFinterm and PV(T) in terms of a) realized index resets between T0 and T and b) realized market curves at T, the right-hand side of the CR formula evaluates to a new quantity referred as PnL:
PnL (evaluated at T0)= PV(CFinterm) + DF(T)*PV(T) - PV(T0)
where the multiplier with the discount factor DF(T) has been added in order to make the middle term of PV(T) evaluated at the same time T0 as the other two terms.
The equivalent definition for T-evaluation also holds:
PnL (evaluated at T)= PV(CFinterm) / DF(T) + PV(T) - PV(T0) / DF(T)Here is a picture that helps to visualize the cash flows that pertain to each of these three terms. It refers to a 3-year interest rate swap that exchanges 3-month Libor against a semiannual fixed rate, as defined in detail further below.
The PnL defined above is NOT a forecasting trading tool. It is a diagnostic tool, or – if you wish – a "forensic" tool.
PnL is just a single number. More interesting is its breakdown in components that carry explanatory content and show who is to "blame" if the PnL is negative, or "pat in the back" if it is positive!
I will now discuss the PnL and its breakdown in the context of a concrete example of the single USD interest rate swap depicted above, so that all concepts become evident.
Discounting and Forecasting Curves
For easy cross reference with my previous Carry&Roll post, I will be using the same market data as in that post, which are Bloomberg rates as of 22 May 2019.
The required yield curves are created as objects of Deriscope type Yield Curve in the first sheet, called Curves.
The first curve is an OIS curve and used as a discounting curve.
The second curve is "dually" bootstrapped out of the Libor market rates using the OIS curve as exogeneous input and used as forecasting curve.
More details are available in my article about constructing a semi-dual, exogenous discounting curve.
I will be using the same USD interest rate swap structure that was defined in my Carry&Roll post.
This was a spot par 3-year USD fixed-to-floating receiver swap exchanging 3-month Libor against a semiannual fixed rate, purchased on May 22, 2019 with a fixed rate of 2.2184 that equaled the market 3-year swap rate on that date. Its NPV on that date was therefore exactly equal to 0, as is the case for all traded par swaps that cost nothing at their inception date.
Below are the spreadsheet formulas that create this swap as an object of Deriscope type Vanilla IRS. The object's nickname is defined as "CustomSwap" for easy reference.
For an introduction on creating interest rate swaps in Excel using Deriscope, you may visit my post about non-standard interest rate swaps.
The swap legs start accruing with a delay of 2 business days after trade execution, on 24 May 2019, as seen from the Start Date entry in cell D8.
The index is the 3-month USD Libor as defined by the object %USDLibor|3M in cell I8. Note the latter object includes a fixing lag of 2 business days, which means the Libor rate for the first floating payment is fixed today to the value implied by the forecasting curve.
Here is a diagram of the involved cash flows:
Spot Price Calculation
It is important to verify that Deriscope calculates the theoretical NPV as of 22 May 2019 of this swap correctly.
As explained in this post, I must supply two input curves and also set up my Model object so that there is no ambiguity as to which curve is used for discounting and forecasting.
Below is the formula that calculates the price, which equals 0 as expected due to the swap's fixed rate matching the market 3-year swap rate:
PnL Scenario A: Curves at T equal the Forward Curves observed at T0 for maturity T and all Libor Resets prior to T equal the corresponding Forward Rates observed at T0
I will investigate the PnL incurred between the initial date T0 = 22 May 2019 and a terminal date chosen as T = 25 Nov 2019 so that it is the same date as the one I used in the Carry&Roll post as the horizon date for calculating the Carry and Roll-Down of my swap.
For a better understanding of the PnL, I start with the easiest possible assumption:
The market rates observed at T are such that their implied spot (relative to T) interest rates are exactly equal to their corresponding T0-observed forward rates. Similarly, any Libor resets occurring before T are assumed equal to their corresponding T0-observed forward rates.
This assumption is illustrated below:
The technical question arises how to build the assumed curves observed at T.
If I reconstruct the curves from scratch using the same Bloomberg rates that are observed at T0, the resulting curves will be almost identical with the curves at T0, but not equal to the intended forward curves. By the way, the word "almost" accounts for the fact that the lengths of the relevant time intervals, for example the accrual periods, will most likely not equal those of the corresponding T0-bound time intervals because all dates would be generated using T rather than T0 as reference, which could make all weekends and holidays to be distributed relative to T differently than relatively to T0.
In fact, it is not a simple feat to build a forward curve out of a given spot curve!
Thankfully, Deriscope has a function called Forward Curve that can be applied on any object of type Yield Curve to produce a new Yield Curve object that has the desired properties of a forward curve.
Below you see how this function may be used in practice:
There are two formulas here that create the forward curve objects corresponding to the spot discounting and forecasting curves.
The first formula is in the selected cell C8 and shown in the formula bar as =ds(C9:D13).
It takes as input the range C9:D13 that defines the object &USD-OIS.1 as the object on which the Deriscope Function Forward Curve applies.
This is the Yield Curve object that has been already created – see above – using the Bloomberg market rates as of May 22, 2019.
The crucial input here is the key-value pair Fwd Date= 25-Nov-2019 which defines the date to be regarded as the maturity date of the USD-OIS -implied forward rates that the produced curve ought to generate.
By default, the returned curve would be bound to the same spot date as the referenced USD-OIS curve, i.e. the 22 May 2019 and could be thus used in any pricing context where the valuation date would be that same date.
But since I intend to use this curve as the new "spot" curve presumably observed at the later "trade date" of 25 Nov 2019, I must set the curve's start date accordingly using the key-value pair Trade Date= 25-Nov-2019, as shown above.
I have thus created the two new yield curves, &FwdUSD-OIS.1 and &FwdUSD-3M.1, that are presumed to be observed at 25 Nov 2019 and imply spot rates that are equal to the corresponding forward rates implied by their respective referenced T0-bound yield curves.
Putting it differently, if I plot the curve of spot zero rates implied by – let's say - &FwdUSD-3M.1 with maturities starting with 25 Nov 2019, it must have the exact same shape as the curve of forward rates implied by &USD-3M.1, where all forward rates share the same maturity date of 25 Nov 2019.
I may now quickly check this is indeed the case for a randomly chosen maturity, for example the 25 Nov 2020. I can use the Deriscope functions Forward Rate and Zero Rate applied respectively on the curves &USD-3M.1 and &FwdUSD-3M.1 as shown below:
The two curves &FwdUSD-OIS.1 and &FwdUSD-3M.1 do not suffice to calculate the NPV of the swap as of 25 Nov 2019 because there exist cash flows occurring after that date that depend on Libor resets observed prior to 25 Nov 2019 that cannot be implied by these curves.
As a matter of fact, if I want to calculate the PnL, I must supply all the Libor resets taking place strictly before 25 Nov 2019. This is due to the PnL formula stated above that makes it dependent on the realized income produced between the two reference dates.
I must therefore construct an object of type Historical Values that contains the required dates and the corresponding Libor resets.
According to my stated assumption the presumed observed Libor resets must equal the corresponding forward rates implied by the &USD-3M.1 forecasting curve.
In order to do all this, I need to know the relevant dates.
The Deriscope wizard can be very helpful in this regard.
If I select the cell that contains the handle name of my swap object, I will see the contents of that object inside the wizard, as shown below:
What I am after here is the very last item _CashFlows= &Set#1. If I click on the lens sign, the screen changes into:
The N/A's in the #Amount column correspond to Libor-linked cash flows, of which the amount is unknown in the current context due to the absence of any yield curve input.
Remember, this table has been generated by selecting a stand-alone swap object and – for this reason – may contain only data that are implied by that swap object alone. These data include the various dates shown above and the fixed rate amounts, but not the Libor resets and the corresponding floating amounts.
I can see in this table that there exist three fixing dates occurring before 25 Nov 2019.
These are the 22-May-2019, 22-Aug-2019 and 21-Nov-2019.
In order to calculate the corresponding Libor forward rates, I also need to know the dates that define the start and end of the corresponding Libor accruing intervals.
The start dates can be read off the #AccrStart column, while the end dates can be read of the #FixingEnd column.
Putting this information in the spreadsheet and using the Deriscope Forward Rate function, I end up with the following table:
I may now create the Historical Values object, as follows:
Finally, I put together my two forward curves and this object into the same collection of type Market Set, as follows:
This has completed the task of creating the market information as of 25 Nov 2019 that satisfies my stated assumption in the currently treated scenario.
The market information is encapsulated by the object &FwdMarket.1 created in cell R4.
The next and final step is now trivial.
I modify the pricing formula shown earlier so that it takes an additional input: An object of type Advanced Pricing that I may generate with the wizard, as shown below:
The object &AdvPrMdl.1 created in cell C14 contains the specifications of my pricing request.
Its input data are shown in full expansion to make evident the current capabilities of the Advanced Pricing type.
In fact, many of these entries could be removed because they are optional, so that only the two rows PnL= TRUE and Horizon Date= 25-Nov-2019 could be kept, like this:
The object &AdvPrMdl.1 is passed as the input &AdvPrMdl.1 in cell E10 in association with the key Models= to the pricing function that produces the final object &ResultA.1 in cell C4.
The object &ResultA.1 is of type Variant and contains the results of the PnL calculation.
Its contents can be seen in the wizard upon selecting the cell, as shown below:
This screen contains several data that are accessed according to the keys on the left column.
The meaning of each key is obvious from its name, but the exact definition is available at the bottom of the wizard upon that key's selection.
For example, the key Forward (Resets)= refers to output data with the following description:
The most important data here is the element corresponding to the key PnL Explain= at the top. If I select that key, the following description appears:
As a matter of fact, this same description is exported on the web, as most other Deriscope wizard-displayed descriptions also do. If you want to learn more details about the various variables and quantities, visiting the above link can be very helpful because all important concepts are defined and interlinked.
The above text informs the user that the related output data form the table [Variant] where the various reported quantities are expressed in terms of the seven variables S, H, I, C, F, Fr, and P.
Clicking on [Variant], I see the following table:
This table effectively defines the seven variables and provides the formulas behind the quantities appearing in the output associated with the key PnL Explain=.
Here is the latter output, as displayed in the wizard after I click on the lens sign to the right of the above key:
Exact definition on each row is provided by selecting the corresponding key at the left column.
But a more helpful guidance to the meaning of these numbers is afforded by the table with the formulas above.
The numbers shown above as 4,268R-… are numbers very close to 0, as betrayed by hovering the mouse over them:
My final step involves pasting this PnL Explain table as a spreadsheet formula using the Go button. Here is the result:
I have formatted the numbers in column J with a two-decimals accuracy, which is good enough for this case where my swap has a notional of 1,000,000 USD.
The important result is that the PnL= in cell J4 comes out 0, exactly as it was expected in the current scenario where the market curves at the horizon date of 25-Nov-2019 specified in cell D24 as defined through the Horizon Market= entry &FwdMarket.1 in cell D12 have been constructed to equal the forward curves observed at the earlier date of 22-May-2019 specified in cell D8.
The elements underneath all the way to Consistency Check=, represent a breakdown of the reported PnL into components that carry a certain financial meaning that will become apparent below, when a non-zero PnL is produced.
I can always read the description associated with any key off the wizard, by selecting the respective cell while the wizard is open.
For example, this is what I see when I select the cell I7:
The remaining numbers are as follows:
Horizon Price= 1,713.41
This is the variable H (from Horizon) in the above formulas table and represents the NPV at the horizon date T = 25-Nov-2019 calculated using the market data available at T supplied through the object &FwdMarket.1 next to the key Horizon Market=.
Spot Price (Hor)= 0.00
This is the expression S/P in the above formulas table - where S and P are defined below - and represents the value S of the swap as an amount payable at the horizon time T. For that reason, it is better suited than S as a candidate of the swap's T0-value to be compared against H.
Spot Price= 0.00
This is the variable S (from Spot) in the above formulas table and represents the NPV at the spot date T0 = 22-May-2019 calculated using the market data available at T0 supplied through the objects &USD-OIS.1 and &USD-3M.1 next to the key Markets=.
This is the variable I (from Income) in the above formulas table and represents the sum of the realized cash flows after T0 but at or before the horizon date T. More precisely, the amounts that have been actually paid at or before T are first discounted down to T0 using the &USD-OIS.1. Then their sum is computed and compounded forward to T using the funding discount factor P (defined below).
Forward Price= 1,713.41
This is the variable F (from Forward) in the above formulas table and represents the sum of the projected cash flows after T. More precisely, the amounts expected to be paid strictly after T are computed with the curves supplied as of T0. Then they are discounted down to T0 using the &USD-OIS.1. Finally, their sum is computed and compounded forward to T using the funding discount factor P (defined below).
This is the variable C (from Carry) in the above formulas table and represents the sum of the projected cash flows after T0 but at or before the horizon date T. More precisely, the amounts expected to be paid at or before T are computed with the curves supplied as of T0. Then they are discounted down to T0 using the &USD-OIS.1. Then their sum is computed and compounded forward to T using the funding discount factor P (defined below).
Forward (Resets)= 1,713.41
This is the variable Fr (from Forward resets) in the above formulas table and represents the sum of the projected cash flows after T conditional on the observed index resets before T. More precisely, the amounts expected to be paid strictly after T are computed with the curves supplied as of T0 under the additional constraint that any index resets prior to T are equal to those contained in the supplied Horizon Market= object. Then they are discounted down to T0 using the &USD-OIS.1. Finally, their sum is computed and compounded forward to T using the funding discount factor P (defined below).
Funding DF= 0,987995
This is the variable P in the above formulas table and represents the discount factor from T0 to T associated with the yield curve that reflects the funding cost of the operation. The associated yield curve must be provided as input to the Models= key in the pricing function and its Issuer attribute must equal the one specified for the Fund Crv Issuer= key in the Advanced Pricing model. In most cases, the Fund Crv Issuer= is left unspecified, in which case the provided discounting curve – i.e. the &USD-OIS.1 curve in the current case – is used.
- 1.If we multiply the reported Carry C = -1,713.41 with the funding discount factor P = 0,987995, we get -1,692.84, which exactly equals the Carry amount calculated with two different approaches in my previous Carry&Roll post mentioned at the beginning of this exposition. This check further bestows trust to the current calculation.
- 2.The Horizon Price H equals the Forward Price F, as expected due to my artificial construction of the Horizon Market &FwdMarket.1, which effectively adds no new information at time T other than what is already conveyed through the forward curves implied at T0.
- 3.The Income I equals the Carry C, as expected due to the fact the actual resets occurring before T and involved in the Income calculation are by design equal to the forecast resets involved in the Carry calculation.
- 4.The Forward Price F is the opposite of the Carry C. This is a logical consequence of the fact that (F + C) P = S and S = 0 due to the swap being at par at T0.
PnL Scenario B: Curves observed at T equal the corresponding Forward Curves observed at T0 for maturity T and all Libor Resets occurring prior to T differ from the corresponding Forward Rates observed at T0
This assumption is illustrated below:
This scenario is very similar to the previous one. It only adds the realistic assumption that all Libor resets prior to T are different than their T0-forward counterparts. It does so in order to isolate the effect on PnL due to the Libor resets by T from the effect due to the curves at T. Keeping the curves at T equal to their T0-expected shapes, makes the latter effect vanish, so that the whole PnL is due exclusively to the resets effect.
This is a realistic assumption because in an actual market evolution it would be highly unlikely that the Libor resets will be ever observed at values that are equal to their earlier forecast levels.
Without further ado, this is how I construct my new market data collection as of the horizon date T:
First the Historical Values object &LiborHistAct.1 is created in cell C4 using Libor fixings produced by adding the shown flat increment of 0.1% on the fixings used earlier. These new fixings purport to represent the actual market fixings that are made to be different than their forward rates counterparts. They are still quite "artificial" since they are all exactly 0.1% higher than the respective forwards. I have done this in order to be able to draw easily a few quantitative conclusions later.
The final object designed to be used as input to the Price formula is the Market Set object &HorMarketFwdCrv.1 produced in cell H8.
Below is the final PnL output:
- 1.The PnL equals now -766.81 and is negative as expected since the only thing that I changed over the previous scenario – where the PnL was 0 – was the three Libor resets before T. But I had set these resets to higher values, which meant bigger amounts for the corresponding Libor flows. Since my receiver swap pays away the floating leg, its value must naturally go down.
- 2.The next two output elements PnL Due To Resets and PnL Due To Curves provide a useful decomposition of the total PnL.The former represents the impact of the unexpected levels of the realized resets on the PnL. According to the formulas table above, it is defined as I – C + Fr – F. This is formed of two parts: The I – C represents the impact of the unexpected levels of the realized resets on the cash flows prior to T. It equals what is further below referred as Carry Surprise and equals -515.40. It is what the name implies: Since Income is effectively the realized Carry, the difference I – C shows the amount by which the realized Carry exceeds the expected Carry. Therefore I – C is the "surprise" amount that shows up on top of the expected Carry by the horizon time T. The Fr – F represents the impact of the unexpected levels of the realized resets on the cash flows at and after T. It equals what is further below referred as Fwd Surprise Due to Resets and equals -251.42. The PnL Due To Curves element represents the impact of the unexpected levels of the horizon market curves on the PnL. The overall observation is that the whole PnL amount is absorbed by the PnL Due To Resets, which fulfils the expectations.
- 3.The next two output elements Carry Surprise and Forward Surprise provide another useful decomposition of the total PnL. The former element has been already described above. It equals I – C and represents the impact of the Carry on PnL. The latter equals H – F and represents the impact of the Forward Price on PnL. It is obvious why the Carry Surprise is negative. The Forward Surprise is also negative and equal to -251.41 because the first cash flow after T depends on the Libor fixing before T. It follows that the higher than expected Libor fixings affect negatively this first cash flow and therefore the NPV of the swap as of T. In other words, the price H at T turns out to be less than its expected value F because of the higher resets before T. This unexpected drop is exactly the Forward Surprise.
- 4.The next two elements Fwd Surprise Due to Resets and Fwd Surprise Due to Curves provide a useful decomposition of the Forward Surprise amount described above. The explanation above about the negative amount of Forward Surprise has shown that the reported amount of -251.41 is due to the unexpectedly high Libor resets realized before T. This amount represents the impact of the unexpected levels of the realized resets on the Forward Surprise and captured – in general – by the Fwd Surprise Due to Resets element. In general though, not only the surprises on the levels of resets have an impact on the Forward Surprise. Much more impact normally comes from the unexpected levels of the T-bound market curves, since the latter affect all the future cash flows of the swap. This latter impact is captured by the Fwd Surprise Due to Curves element.
- 5.The element Consistency Check reports the result of a basic check. It verifies that PnL Due To Curves and Fwd Surprise Due to Curves are equal, as expected due to their definitions. Indeed, from the formulas table their presumed equality can be written as: H - S/P + C - Fᵣ + F = H - Fᵣ ó S/P = C + F . The latter equality must hold – up to numerical precision - if all cash flows have been accounted properly, since C captures all cash flows at and before T, while F does so for all cash flows strictly after T.
- 6.Note the Horizon Price has gone down to 1,461.99 from the previous unperturbed value of 1,713.41. The change equals -251.42, which exactly matches the Forward Surprise seen above. This is actually always the case since the Forward Surprise is defined as H – F.
- 7.Note that setting higher than expected Libor resets has no impact whatsoever on the Forward Price and Carry, which are still at their previous values of 1,713.41 with -1,713.41 respectively. This is normal as both these quantities are not concerned with the market environment after T0.
- 8.A final check on the degree by which the reported numbers are sensible: I had intentionally set the Libor resets to be higher than their respective expected values by a flat amount of 0.1%. As can be seen in the cash flow diagram, the Carry is composed by two Libor flows and one fixed flow. The Forward Price depends on all remaining cash flows, of which only one bears exposure on the fixed Libor resets prior to T. Since the swap has a notional of 1,000,000 and pays Libor quarterly, each increment in a specific Libor reset by 1% must induce a change in the corresponding cash flow amount of roughly equal to 1%*0.25*1,000,000 = 2,500. Therefore, the current 0.1% increment must cause a cash flow amount change of roughly 250. This amount – after it has been discounted to its report time T and tagged with a negative sign – should be close to the reported Forward Surprise amount of -251.41, which is indeed the case here. Twice that amount, i.e. 500, should be close to the reported Carry amount of -515.40.
PnL Scenario C: Curves observed at T differ from the Forward Curves observed at T0 for maturity T, but all Libor Resets occurring prior to T equal the corresponding Forward Rates observed at T0
This assumption is illustrated below:
This scenario is the flipped version of the previous one. It keeps all Libor resets prior to T equal to their T0-forward counterparts, while allowing the market curves at T to differ. It does so in order to isolate the effect on PnL due to the curves at T from the effect due to the Libor resets by T. Keeping these Libor resets equal to their T0-expected values, makes the latter effect vanish, so that the whole PnL is due exclusively to the curves effect.
I will construct the horizon curves using a similar trick with that employed in the construction of the Libor resets.
Rather than constructing the horizon curves using market data as of T unrelated to the market data at T0, I will use the Deriscope Increment function to easily generate new Yield Curve objects out of the old ones that have their market rates incremented by a given flat amount.
Below is how I use this technique to build my market data collection as of the horizon date T:
Note that the market collection is now created using the &LiborHist.1 object, which was seen before to represent the three Libor resets set equal to the corresponding forward rates.
The final object designed to be used as input to the Price formula is the Market Set object &HorMarketFwdResets.1 produced in cell H4.Below is the final PnL output:
- 1.The PnL equals now -4,135.74, which is much more negative than the previous – resets-only – result of -766.81. It is obviously negative since the curves at T have been produced using market rates that are higher by 0.1% than the corresponding rates at T0. These incremented market rates are obviously also higher than the corresponding T0-observed forward rates, which in turn implies bigger amounts for the corresponding Libor flows, when compared to the flows expected from the T0-observed forward rates. Since my receiver swap pays away the floating leg, its value must naturally go down.
- 2.The decomposition of the total PnL offered by the next two output elements PnL Due To Resets and PnL Due To Curves is now a flipped version of the one before. Now the PnL Due To Resets equals 0, while the PnL Due To Curves carries the full blast of the shown PnL.
- 3.The next decomposition offered by the elements Carry Surprise and Forward Surprise is similarly interpreted. Same holds for the decomposition of the Forward Surprise offered by the elements Fwd Surprise Due to Resets and Fwd Surprise Due to Curves.
- 4.Similar to my comment in the previous scenario, the Horizon Price has gone down to -2,422.33 from the unperturbed forward value of 1,713.41. The change equals 4,135.74, which exactly matches the Forward Surprise seen above, as predicted by the latter's definition as the difference H – F.
PnL Scenario D: Curves observed at T differ from the Forward Curves observed at T0 for maturity T and all Libor Resets occurring prior to T differ from the corresponding Forward Rates observed at T0
This assumption is illustrated below:
This scenario is completely realistic as the market evolves in unpredicted manner and the Libor resets as well as the curves at T can practically never equal the values that were expected at the earlier time T0. Here is my Market Set construction:
Note that the market collection is now created using the &LiborHistAct.1 object, which was seen before to represent the perturbed Libor resets purported to represent "actual" market rates as of T.
The final object is the &HorMarket.1 produced in cell C4.
Below is the final PnL output:
Click on IntRateSwapPnL.xlsx to download the spreadsheet produced with the above steps.
Feel free to contact me if you want to share any thoughts with regard to this product or if you want to request any particular features. Contact info and social media links are available at my web site https://www.deriscope.com