Calculate weighted average between 2 values, with dynamic lookup date

[kiddn]

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/2018	D	EUR	NZD	1	0.00012
26/02/2018	D	EUR	NZD	7	0.00092
26/02/2018	D	EUR	NZD	14	0.00183
26/02/2018	D	EUR	NZD	30	0.00379
26/02/2018	D	EUR	NZD	60	0.00835
26/02/2018	D	EUR	NZD	91	0.01234
26/02/2018	D	EUR	NZD	182	0.02447
26/02/2018	D	EUR	NZD	273	0.03680
26/02/2018	D	EUR	NZD	365	0.04982
26/02/2018	D	EUR	NZD	547	0.07550
26/02/2018	D	EUR	NZD	730	0.10238
26/02/2018	D	EUR	NZD	1095	0.15377
26/02/2018	D	EUR	NZD	1460	0.21016
26/02/2018	D	EUR	NZD	1825	0.26680