Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
kiddn
Helper I
Helper I

Calculate weighted average between 2 values, with dynamic lookup date

‎02-26-2018 05:40 PM

 

Hi, I've got a really curly one here. Hoping someone can help.

 

I'm attempting to recreate historical daily forward (financial) rate based on a moving date.

 

As an example,

 

On the 26th February 2018, we enter into an option to sell EUR10 million. The option has a term of 330 days. I would like to calculate what the weighted average forward points are on that day, knowing that we only have a table that provide forward points at certain intervals (table below. In this case 1, 7, 14, 30 and so on. For a term of 330 days it lies between 273 and 365 days so should take the weighted average of these 2 terms. This would be caculated as ((0.0368+.04982)/(273+365)) * 330 = 0.044803.

 

This rate would then be used as a comparison to using an option product to evaluate options vs forwards as a mechanism for hedging.

 

So key aspects are -

 

1. Date of contract - calculation needs to be over historical contracts so dynamic

2. Term of contract

3. Calculating fwds points between 2 values

4. Multiplying Weighted average fwd points by term to get total fwd points.

5. New column showing Total fwd points applied to individual contract based on term

 

Any help greatly appreciated.

 

 

26/02/2018DEURNZD10.00012
26/02/2018DEURNZD70.00092
26/02/2018DEURNZD140.00183
26/02/2018DEURNZD300.00379
26/02/2018DEURNZD600.00835
26/02/2018DEURNZD910.01234
26/02/2018DEURNZD1820.02447
26/02/2018DEURNZD2730.03680
26/02/2018DEURNZD3650.04982
26/02/2018DEURNZD5470.07550
26/02/2018DEURNZD7300.10238
26/02/2018DEURNZD10950.15377
26/02/2018DEURNZD14600.21016
26/02/2018DEURNZD18250.26680

 

Message 1 of 17
1,834 Views
0
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User
In response to kiddn

‎02-26-2018 08:00 PM

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Message 12 of 17
2,232 Views
0
16 REPLIES 16
Ashish_Mathur
Super User
Super User

‎02-26-2018 05:45 PM

Hi,

 

So do you want the result in another column?  While i understand your computation, i do not know the result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 2 of 17
1,774 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎02-26-2018 05:49 PM

Hi - yes thanks as a row by row calculation against the individual contract

Message 3 of 17
1,770 Views
0
Ashish_Mathur
Super User
Super User
In response to kiddn

‎02-26-2018 05:52 PM

Hi,

 

So answer are you expecting for the first 5 rows of the sample that you have shared.  Please share the method of calculation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 4 of 17
1,767 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎02-26-2018 05:57 PM

The method of calculation is to lookup this table that has the forward points, term days etc, perform the calculation and show these values in another table which holds the financial contracts details.

 

Note the table that holds the forward points data has a full history going back a number of year. The key connections between the 2 tables are date, and the 2 x currencies columns e.g. EUR and NZD

Message 5 of 17
1,765 Views
0
Ashish_Mathur
Super User
Super User
In response to kiddn

‎02-26-2018 05:58 PM

Hi,

 

Share that other Table as well then.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 17
1,762 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎02-26-2018 06:03 PM

Payment Currency, Payment Currency 2 and Contract Date are keys columns here

 

Payment CurrencyPayment amount in payment currencyPayment CurrencyPayment amount in payment currencyPayment DatePayment amount in payment currencyDirectionOption CatExercise TypeProduct CategoryTextProduct TypeTransaction CategoryTerm StartContract Date
JPY250000000NZD4115226.349/08/20110.00-StandardEurop.760Currency option (OTC)76A1009/08/201116/04/2014
JPY250000000NZD4719652.639/08/20110.00+StandardEurop.760Currency option (OTC)76A2009/08/201116/04/2014
JPY250000000NZD4108463.439/05/20110.00-StandardEurop.760Currency option (OTC)76A1008/05/201115/05/2014
JPY250000000NZD4725897.929/05/20110.00+StandardEurop.760Currency option (OTC)76A2008/05/201115/05/2014
Message 7 of 17
1,759 Views
0
Ashish_Mathur
Super User
Super User
In response to kiddn

‎02-26-2018 06:07 PM

Hi,

 

How does one calculate contract term.  Is it Contract Date - Term Start?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 8 of 17
1,757 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎02-26-2018 06:16 PM

No sorry - Expiration Date is another column out to side - i thought i had captured that in last post but must've missed it.


Expiration Date - Contract Date will get your Term

Message 9 of 17
1,754 Views
0
Ashish_Mathur
Super User
Super User
In response to kiddn

‎02-26-2018 06:25 PM

Hi,

 

Please share the complete/correct dataset.  Ensure that the dates on both tables can be related.  Which date column on the second table can be related to Date column on Table1?  PLease draft a clear question with 2 tables and show the expected answer for the first 2-3 rows.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 10 of 17
1,752 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎02-26-2018 07:11 PM

Apologies hopefully this will provide more clarity.

 

Below are 2 x tables. The first table holds the option financial data. The Contract Date on this table can be related to the Effective From column in the second table. The term days can be calculated from Expiration Date less Contract Date in first table. 

 

Then, applying term days to second table to lookup a calculated weighted average points per day (average calculated by finding where term days sits between, then averaging out fwds with values above and below), which is then multiplied out by Term days again to give total forward points for the term for that contract.

 

This value is shown in a separate calculated column in the first table. For ease I have added a column in the first table called Fwd Points (Calculated Column) to demonstrate what the values should be for each contract on each day.

 

The first 2 x contracts are JPY/NZD and have a term of 187 days. The next 2 x contracts are EUR/NZD and have a term of 330 days.

 

Let me know if you need more information.

 

TransactionProcessing IndicatorTextBusiness PartnerName of Transaction TypeNameStrikePayment CurrencyPayment amount in payment currencyPayment CurrencyPayment amount in payment currencyPayment DatePayment amount in payment currencyDirectionOption CatExercise TypeProduct CategoryTextProduct TypeTransaction CategoryTerm StartContract DateExpiration dateEnd of termFwd Points (Calculated Column)External ReferenceAssignmentTransaction TypeInternal ReferenceFinance ProjectExercise TypePortfolioActive activityPayment CurrencyTextOption categoryReferenceActive StatusActivity CategoryName of activity categorySettlement
1000606 OTC FX opt PurchaseJPY-Put79.000000000JPY1175000000NZD14873417.7221/12/201794605.00-StandardEurop.760Currency option (OTC)76A10021/12/201721/12/201726/06/201826/06/20180.0000009011  100  1JPY2NZD 1 030Contract SettlementPhys.
1000607 OTC FX opt SaleJPY-Call76.000000000JPY1175000000NZD15460526.3221/12/20170.00+StandardEurop.760Currency option (OTC)76A20021/12/201721/12/201726/06/201826/06/20180.0000009011  200  1JPY2NZD 1 030Contract SettlementPhys.
1000635 OTC FX opt PurchaseEUR-Put0.580000000EUR6500000.00NZD11206896.5528/02/2018176210.00-StandardEurop.760Currency option (OTC)76A10026/02/201826/02/201822/01/201922/01/20190.044803 Collar Option 2018100  1EUR2NZD 1 030Contract SettlementPhys.
1000636 OTC FX opt SaleEUR-Call0.550000000EUR6500000.00NZD11818181.8228/02/20180.00+StandardEurop.760Currency option (OTC)76A20026/02/201826/02/201822/01/201922/01/20190.044803 Collar Option 2018200  1EUR2NZD 1 030Contract SettlementPhys.

 

Effective fromExchange Rate TypeFrom currencyCurrency into which system translatesTerm in daysForex swap rate
21/12/2017DEURNZD10.00022
21/12/2017DEURNZD70.00315
21/12/2017DEURNZD140.00410
21/12/2017DEURNZD300.00637
21/12/2017DEURNZD600.00986
21/12/2017DEURNZD910.01326
21/12/2017DEURNZD1820.02501
21/12/2017DEURNZD2730.03705
21/12/2017DEURNZD3650.04942
21/12/2017DEURNZD5470.07626
21/12/2017DEURNZD7300.10334
21/12/2017DEURNZD10950.15984
21/12/2017DEURNZD14600.21663
21/12/2017DEURNZD18250.28144
21/12/2017DJPYNZD10.00001
21/12/2017DJPYNZD70.00002
21/12/2017DJPYNZD140.00003
21/12/2017DJPYNZD300.00004
21/12/2017DJPYNZD600.00006
21/12/2017DJPYNZD910.00009
21/12/2017DJPYNZD1820.00016
21/12/2017DJPYNZD2730.00025
21/12/2017DJPYNZD3650.00033
21/12/2017DJPYNZD5470.00053
21/12/2017DJPYNZD7300.00074
21/12/2017DJPYNZD10950.00121
21/12/2017DJPYNZD14600.00174
21/12/2017DJPYNZD18250.00235
26/02/2018DEURNZD10.00012
26/02/2018DEURNZD70.00092
26/02/2018DEURNZD140.00183
26/02/2018DEURNZD300.00379
26/02/2018DEURNZD600.00835
26/02/2018DEURNZD910.01234
26/02/2018DEURNZD1820.02447
26/02/2018DEURNZD2730.03680
26/02/2018DEURNZD3650.04982
26/02/2018DEURNZD5470.07550
26/02/2018DEURNZD7300.10238
26/02/2018DEURNZD10950.15377
26/02/2018DEURNZD14600.21016
26/02/2018DEURNZD18250.26680
26/02/2018DJPYNZD10.00001
26/02/2018DJPYNZD70.00001
26/02/2018DJPYNZD140.00001
26/02/2018DJPYNZD300.00003
26/02/2018DJPYNZD600.00006
26/02/2018DJPYNZD910.00009
26/02/2018DJPYNZD1820.00017
26/02/2018DJPYNZD2730.00026
26/02/2018DJPYNZD3650.00036
26/02/2018DJPYNZD5470.00055
26/02/2018DJPYNZD7300.00078
26/02/2018DJPYNZD10950.00126
26/02/2018DJPYNZD14600.00184
26/02/2018DJPYNZD18250.00246
Message 11 of 17
1,613 Views
0
Ashish_Mathur
Super User
Super User
In response to kiddn

‎02-26-2018 08:00 PM

Hi,

 

You may refer to my solution in this file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 12 of 17
2,233 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎08-06-2018 03:40 PM

Hi,

Slight change to formula now required.

We have some transactions that are executed/entered on the weekend however the corresponding rates are not available as they are loaded on a weekday basis - Monday to Friday. 

Can the formula be modified to lookup the last available rates? e.g. If transaction entered 22/10/17 (Sunday) can it look up the rates from the previous Friday 20/10/17?

Thanks

Message 13 of 17
1,481 Views
0
Ashish_Mathur
Super User
Super User
In response to kiddn

‎08-06-2018 07:57 PM

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 14 of 17
1,478 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎08-06-2018 08:16 PM

Hi,

 

Assume 21/12/07 last rates entered as per below for EUR to NZD. Fast forward to Saturday 23/12/07. Rates to be generated are based on the 21/12/07 for the relevant term.

 

As an example, if a EURNZD transaction generated on 23/12/07, and term is for 45 days the average will be 0.008115 (which is based on last available rates generated from 21/12/07).

 

Note your original formula looks up term of transaction and lower swap rate and upper swap rates, and lower and upper term, to generate a weighted average fwd points.

 

 

 

Effective fromExchange Rate TypeFrom currencyCurrency into which system translatesTerm in daysForex swap rate
21/12/2017DEURNZD10.00022
21/12/2017DEURNZD70.00315
21/12/2017DEURNZD140.00410
21/12/2017DEURNZD300.00637
21/12/2017DEURNZD600.00986
21/12/2017DEURNZD910.01326
21/12/2017DEURNZD1820.02501
21/12/2017DEURNZD2730.03705
21/12/2017DEURNZD3650.04942
21/12/2017DEURNZD5470.07626
21/12/2017DEURNZD7300.10334
21/12/2017DEURNZD10950.15984
21/12/2017DEURNZD14600.21663
21/12/2017DEURNZD18250.28144
Message 15 of 17
1,475 Views
0
Ashish_Mathur
Super User
Super User
In response to kiddn

‎08-06-2018 08:20 PM

Hi,

 

It's been a while since i answered your first question.  Please show me the exact result you are expecting and how have you arrived at that result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 16 of 17
1,472 Views
0
kiddn
Helper I
Helper I
In response to Ashish_Mathur

‎08-06-2018 08:39 PM

All the information is available from previous posts. This included a file that you prepared yourself for the final answer. If you follow the logic from the original posts to now it will make sense. I also included an example of an answer in my most recent post.

 

Thanks

Message 17 of 17
1,469 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All