LinkBack URL
About LinkBacks
I want to save one million dollars in 10 years earning 8.00% interest. How do I calculate how much I need to save per month. Please help many thanks.
I want to save one million dollars in 10 years earning 8.00% interest. How do I calculate how much I need to save per month. Please help many thanks.
Hi:-
"X" x 1.08 ^(1/12) x 10ys x 12mths = $1,000,000
"X" = $8280 / Mth
Regards Mick
=pmt(8%/12,10*12,0,-1000000)
Assuming interest is paid monthly
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
Thanks, MickG - sorry but I don't understand your reply. i am trying to calculate backwards from $1,000,000 goal with klnown term and interest rate to calculate saving amt reqd. Thanks Squiggler47, your answer is very close but my manual calculation came up with $5,429.90m - are there any other ways to calculate this. Cheers
I added interest at the end of the month yours adds it at the start of the monthOriginally Posted by whitepaw
Hi, My last post is not correct for investing a fixed amount every Month. This is the formula you want.
Interest Rate 1.08 = monthly rate 1.08^(1/12) = 1.006434.
"X" = Amount to save per Month, Term 10 x 12 = 120 Mths
Formula = "X" x (((((1.006434^(120+1))-1)/(1.006434-1))-1)= 1,000,000.
"X" = 1,000,000/179.1234 = $5582.716
Work it out on a sheet as shown below, But up to column 120
Regards Mick
Column (A) Column (B) =+A4*B4 =+(A2+$A$4)*$B$4 "A4" = 5582.7163017536 . "B4" = 1.00643403011
Hi, A thought on this:-
In using the rate 8% the monthy equivelent is not 1.08^(1/12) =1.006434, but is:- [(0.08/12)+1 ]= 1.006666. This will give you a result of $5429.893479
This is what you calculated originally.
NB:- the PMT Function is for Repaynments on a loan.!!
Regards Mick
PMT can also be used to calculate situations like this!
PMT(rate,nper,pv,fv,type)
on a loan you would have PV (present value) as the loan ammount FV (FureValue) would default to 0
In this case we modify the parameters
PV = 0 (start with no savings)
FV = -Target
And it gives exactly the same answer as you got!
Its a well known use of PMT!
The only problem wast that the type perameter chooses when the interest was paid my first used the defaut of 0 since I assumed when savings started the interest would be paid at the end of each month since when I put 1000 in the bank I dont get interest until the last day of the month!
Like all excel functions help doesnt quite cover all situations in which it can be used!!!!
Last edited by squiggler47; 05-23-2009 at 05:36 AM.
=A2/POWER(1+B2;C2)
Where in cells you can put numbers you like:
A2 is final amount (1 000 000)
B2 is rate (0,08)
C2 is number of months (120)
result is 97,5325 dolars per month.
Never use Merged Cells in Excel
=PMT(8%/12,10*12,0,-1000000,1)
give 5429.89
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks