I'm a developer building a financial reporting tool for a client. They have given me a calculation to find the amount of interest earned by an account throughout specific date ranges. But the value they are expecting at the end differs to what my site or excel is showing.
Below is an example:
Account Value as of 30/06/2015: $2857.75
Account Value as of 30/06/2016: $2897.76
Note: the 30/06/2015 value is the 30/06/2016 value minus 1.4%. They calculated that value by doing $2897.76 / 1.014
What they want me to do is provide the interest from 14/12/2015 to 30/06/2016. To do that I've been told to do the following:
Get 14/12/2015 value:
Daily_Interest_Rate = 0.014 / 366; // 366 days between 30/06/2015 and 30/06/2016)
Days = 167; // Number of days between 30/06/2015 and 14/12/2015
New_Value = $2857.75 + ($2857.75 * (Daily_Interest_Rate * Days);
The above gives me a value of $2876.01. So with my new value I calculate the interest from 14/12/2015 to 30/06/2016 doing the following:
Get Interest from 14/12/2016 to 30/06/2016
Daily_Interest_Rate = 0.014 / 366; // 366 days between 30/06/2015 and 30/06/2016)
Days = 199; // Number of days between 14/12/2015 and 30/06/2016
Interest_Value = $2876.01 * (Daily_Interest_Rate * Days);
The above gives me $21.89. If I add my interest value to my 14/12/2015 value I get $2897.90. However, my client is expecting that value to match the 30/06/2016 value of $2897.76.
As of now, it's being treated as if my implementation is wrong. Is there anything I'm missing? Or is the approach to the problem incorrect? If so, what information can I provide the client to resolve the issue?
Thanks!
PS: Apologies if my formatting is incorrect, I usually post at SOF...
I did not totally clear to me which numbers are fixed and which are calculated and therefore to question. The account value as of 30/6/2016 seems fixed, throughout the last year they had an interest rate of $1.4\%$ and from that you want to calculate back what your account value at 30/06/2015 was?
What is already obvious is that
$\$2897.76-1.4\%\neq \$2897.76/1.014$ but $\$2897.76 \times (1-0.014)=\$2897.76 \times 0.984=\$2857.19136$
Or is the account value for 30/06/2015 fixed and the deduced interest rate wrong? This is then analogue to my answer above.
If this does not solve your question, there is an other point: As of my knowledge interest rates and earnings are normally calculated on a basis of 360 days per year.
The first one clearly gives you a slightly smaller value, whereas the latter might even give you bigger results. However, this should probably not change the result.
oh and I guess there was a coupon on 14/12/2015 right? Because you are including this in your calculations
EDIT: sorry, I calculated it through and neither of the two brings the expected values. Perhaps it might however bring you one step further