earthworm
Well-known Member
- Joined
- May 19, 2009
- Messages
- 760
- Office Version
- 2019
- 2016
- Platform
- Windows
I have a below data
<tbody>
</tbody>
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
| Month | 1,056,244.83 | Actual Rate | Completed Tenor Rate | Actual Rate Profit | Completed Tenor Profit | Rate Differential | Differential Rate | New Profit |
| May-2017 | 13 | 5.40% | 4.29% | 2,031.46 | 1,613.88 | 1.08502410% | 4.31% | 1,623.28 |
| Jun-2017 | 30 | 5.22% | 4.15% | 4,531.72 | 3,602.81 | 1.08502410% | 4.13% | 3,589.76 |
| Jul-2017 | 31 | 5.24% | 4.15% | 4,700.72 | 3,722.90 | 1.08502410% | 4.15% | 3,727.37 |
| Aug-2017 | 31 | 5.23% | 4.14% | 4,691.75 | 3,713.93 | 1.08502410% | 4.14% | 3,718.39 |
| Sep-2017 | 30 | 5.24% | 4.15% | 4,549.09 | 3,602.81 | 1.08502410% | 4.15% | 3,607.13 |
| Oct-2017 | 31 | 5.22% | 4.14% | 4,682.78 | 3,713.93 | 1.08502410% | 4.13% | 3,709.42 |
| Nov-2017 | 30 | 5.22% | 4.14% | 4,531.72 | 3,594.13 | 1.08502410% | 4.13% | 3,589.76 |
| Dec-2017 | 5 | 5.22% | 4.14% | 755.29 | 599.02 | 1.08502410% | 4.13% | 598.29 |
| 30,474.54 | 24,163.41 | 24,163.41 |
<tbody>
</tbody>
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
Last edited:
