Maximum value of 12 variable function to get highest bank interest rate
Background: A bank offers regular saving product whereby a sum of money is deposited monthly for a period of 12 months after which you receive interest on this money. The 12 deposits must be between £25 and £300. The interest rate is 5%.
However, if you choose to invest the full 12 x £300 = £3600, you obviously do not get 5% of £3600 because the whole amount is not invested for one year. It is 5%/12 of £300 for month 1, 5%/12 of £600 + [month 1 interest] for month 2, etc. At the end of the year you would make £99.01 equating to 2.75% of £3600 invested.
Using Excel I have found that investing £300 for the first 3 months and then the minimum £25 for the final 9 will give you £46.87 return on £1125 which is a much better 4.17%.
I attach the spreadsheet for you to see my results/hopefully give a clearer picture of what I am looking into.
This more useful interest rate can be calculated as a function of 12 variables (each monthly payment) with certain parameters. The function being:
where (monthly payments), and (the 5% interest applied each month)
and I = the actual percentage of the total amount invested at the end of the year which I am seeking to maximise.
(If it isn't clear how I arrived with this formula please ask or derive yourself!)
The question: is there a method for finding the best possible values for x1...x12 to make 'I' as large as possible.
Whilst I have found the best values in this case by trial and error in the spreadsheet, I am left wondering why it works out best to put in 300 for just the first 3 months, why not 2, why not 4. If there is a nice explanation that does not involve using the above function then I would appreciate that too. Just something so I can see why it works out this way.
It will also benefit to have a general method for finding the best options where the limits, interest rates and frequency of investment are different.
Many thanks for any assistance. It is nice using maths to help save money better :)