I am attempting to create a 'savings goal' calculator but am stuck with how (if possible) I can achieve the following:-
Calculate the monthly payment amount required to reach a goal given an interest rate. (can do this with formula at the bottom - in javascript but should make sense anyway)
Adjust the monthly payment amount based on 1 or more lump sum payments at some point in the future. (cant work this out)
For example I can calculate that to achieve a £300,000 balance in 30 years at a 7% interest rate is £245.91 pcm.
But I am not sure how I could adjust this amount if a lump sum payment at year 5 was introduced:-
e.g.
yr 1 - x
yr 2 - 4 - x
yr 5 - 10000 lump sum
yr 6 - 30 - x
Where x would be the amount needed per month taking into account the lump sum payment to still achieve £300,000 total at year 30.
Also for the same scenario but with another payment at say year 15.
e.g.
yr 1 - x
yr 2 - 4 - x
yr 5 - 10000 lump sum
yr 6 - 14 - x
yr 15 - 10000 lump sum
yr 16 - 30 - x
I was thinking something along the lines of working out the future value of each lump sum payment (so 10000 at year 5 compounded at 7% for 25 years) and then subtracting that amount from the amount required (so £300,000 - $54,274.33 = £245725.67) and then calculating the monthly payments required based on £245725.67).
It comes close (£301,827.53 at £201.42 pcm) but I am missing something as part of the calculation I am sure, as it goes further out at larger values.
I am guessing it is because at year 5 I am double-counting a payment amount in effect (as the calculation below will still account for the monthly payment even though it has been over-ridden) - do I need to subtract the compound interest for the monthly payment that has been over-ridden?
I hope the above makes sense and any help would be greatly appreciated.
Calculation used:-
ir - interest rate fv - future value (residual value) np - number of periods (years) pv - present value ^ = power as I dont know how to do that here :-P
(ir/12)[fv - pv(1 + ir/12) ^ 12np] / [(1 + ir/12) ^ 12np - 1]
Or in Javascript
function calc1(ir, fv, np, pv){
/*
ir - interest rate
fv - future value (residual value)
np - number of periods (years)
pv - present value
*/
var a, b, c, d;
a = ir / 12;
b = (1 + ir/12);
c = 12 * np;
d = (a * (fv - (pv * Math.pow(b, c)))) / (Math.pow(b, c) - 1);
return d;
}
Yes, your hunch about double counting is correct, you could write the amount for year 5 also as
x + (10000 - x)
where the first term is covered by the geometric sum formula. That the second term needs a reduction by x, but x is unknown leads to a circle that could be broken since the problem is linear.
However, to avoid too much code clutter and preserve some flexibility, one could recommend to do as you did for a first guess and use the secant formula on the exact principal computation formula (the one you used for verification to get £302k from x=201) to refine the result. Use the first guess x and x+1 to initialize the secant method.