Excel Average Calculation Glitch

[earthworm]

I have a below data

[TABLE="width: 863"]
<tbody>[TR]
[TD]Month[/TD]
[TD] 1,056,244.83[/TD]
[TD]Actual Rate[/TD]
[TD]Completed Tenor Rate[/TD]
[TD]Actual Rate Profit[/TD]
[TD]Completed Tenor Profit[/TD]
[TD]Rate Differential[/TD]
[TD]Differential Rate[/TD]
[TD]New Profit[/TD]
[/TR]
[TR]
[TD]May-2017[/TD]
[TD]13[/TD]
[TD]5.40%[/TD]
[TD]4.29%[/TD]
[TD] 2,031.46[/TD]
[TD] 1,613.88[/TD]
[TD]1.08502410%[/TD]
[TD]4.31%[/TD]
[TD] 1,623.28[/TD]
[/TR]
[TR]
[TD]Jun-2017[/TD]
[TD]30[/TD]
[TD]5.22%[/TD]
[TD]4.15%[/TD]
[TD] 4,531.72[/TD]
[TD] 3,602.81[/TD]
[TD]1.08502410%[/TD]
[TD]4.13%[/TD]
[TD] 3,589.76[/TD]
[/TR]
[TR]
[TD]Jul-2017[/TD]
[TD]31[/TD]
[TD]5.24%[/TD]
[TD]4.15%[/TD]
[TD] 4,700.72[/TD]
[TD] 3,722.90[/TD]
[TD]1.08502410%[/TD]
[TD]4.15%[/TD]
[TD] 3,727.37[/TD]
[/TR]
[TR]
[TD]Aug-2017[/TD]
[TD]31[/TD]
[TD]5.23%[/TD]
[TD]4.14%[/TD]
[TD] 4,691.75[/TD]
[TD] 3,713.93[/TD]
[TD]1.08502410%[/TD]
[TD]4.14%[/TD]
[TD] 3,718.39[/TD]
[/TR]
[TR]
[TD]Sep-2017[/TD]
[TD]30[/TD]
[TD]5.24%[/TD]
[TD]4.15%[/TD]
[TD] 4,549.09[/TD]
[TD] 3,602.81[/TD]
[TD]1.08502410%[/TD]
[TD]4.15%[/TD]
[TD] 3,607.13[/TD]
[/TR]
[TR]
[TD]Oct-2017[/TD]
[TD]31[/TD]
[TD]5.22%[/TD]
[TD]4.14%[/TD]
[TD] 4,682.78[/TD]
[TD] 3,713.93[/TD]
[TD]1.08502410%[/TD]
[TD]4.13%[/TD]
[TD] 3,709.42[/TD]
[/TR]
[TR]
[TD]Nov-2017[/TD]
[TD]30[/TD]
[TD]5.22%[/TD]
[TD]4.14%[/TD]
[TD] 4,531.72[/TD]
[TD] 3,594.13[/TD]
[TD]1.08502410%[/TD]
[TD]4.13%[/TD]
[TD] 3,589.76[/TD]
[/TR]
[TR]
[TD]Dec-2017[/TD]
[TD]5[/TD]
[TD]5.22%[/TD]
[TD]4.14%[/TD]
[TD] 755.29[/TD]
[TD] 599.02[/TD]
[TD]1.08502410%[/TD]
[TD]4.13%[/TD]
[TD] 598.29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 30,474.54[/TD]
[TD] 24,163.41[/TD]
[TD][/TD]
[TD][/TD]
[TD]24,163.41[/TD]
[/TR]
</tbody>[/TABLE]

I want the result of 24,163.41 by applying that particular number that can subtract or add the actual rates to recalculate the profit which when added can lead to 24,163.41 . Unfortunately i tried the average formula between two rates subtract them and then apply the formula = average of actual rates minus average of completed tenor rates = -1.08625% . However when this difference is applied to all actual rates the total profit does not reconcile with the amount resulting with an answer of 24,156.28 .

Hence to achieve my answer of 24,163.41 i manually tried hit and try logic to achieve the result . Please advice why there is difference between the two answer . I am lost. I need to automate the difference of 1.0850241% through available actual rate and last completed tenor rates .

After the month are the number of days example : 13 , 30 , 31 etc

Below is the sample file for easy understanding.

https://app.box.com/s/ozrdc7ycpvo25khgxs02p7nuv1ek01zf

 

[earthworm]

Bump!

 

[earthworm]

I finally found the answer below is the accurate logic

(Required Profit on completed tenor / amount / Total Number of days *365) - (
(Required Profit on Actual tenor / amount / Total Number of days *365)

The difference between two rates is the actual answer



/ = Drivide
* =Multiply
( = Brackets
- = Minus