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
=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
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
Please Login or Register to view this content.
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.
Hi, My apologies, you absolutely correct. My problem is that I tend to use my own formulas when working this sort of thing out, because unless you use the functions regularly your never too sure if the answer is correct.
But you have enlightened me.
Regards Mick
I might suggest that Whitepaw should do a little reasearch into the financial functions of excel, I see that all the posts from whitepaw I see are to do with interest/savings/loans etc.
These functions will do most of that in a simple way!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks