Excel Formula

[Stretford End]

Can anyone help me?

I've been working on excel all day and my brain is fried, i need a formula that will solve this question.

you want to save 20,000 grand over two years, create a formula to calculate how much you would need to save each month to acheive this goal. the intrest rate is 5% per annum.

Thanks in advance

 

[JuddyBuddy]

Wouldn't it just be something like...

20,000 / 2 = 10,000 a year

10,000 - 5% per annum = 9500 / 12 months = 791.66 per month

Probably oversimplified it and way off. But, my brain is fried from a long day too.

 

[21C]

Homework?

 

[Jabso]

There's compound interest to take into account too though. I.e. you'll be earning interest on your interest earnt.
IF you search for compound interest on google you'll get your answer.

There are a few financial formulas on excel like FV (can't check now don't have excel on my comp) you might want to look at, but you need to the monthly payment amount, which would require a bit of algebra I think to work out the formula.

The formula you're looking for is probably this, but you already know the FV ($20,000), you want the PMT.

Annuity

FV = PMT [((1 + i)^n - 1) / i]

FV = future value (maturity value)
PMT = payment per period
i = interest rate in percent per period
N = number of periods
It also depends on how often it's compounded.

EDIT: Read this page carefully you should have your answer.

http://www.csgnetwork.com/directcalccompinttrainer.html

 

[Falchoon]

$795.23 ?

 

[Falchoon]

 Contrib 	mnth Int	int
 $795 	23	 $76 
 $1,590 	22	 $73 
 $2,386 	21	 $70 
 $3,181 	20	 $66 
 $3,976 	19	 $63 
 $4,771 	18	 $60 
 $5,567 	17	 $56 
 $6,362 	16	 $53 
 $7,157 	15	 $50 
 $7,952 	14	 $46 
 $8,748 	13	 $43 
 $9,543 	12	 $40 
 $10,338 	11	 $36 
 $11,133 	10	 $33 
 $11,928 	9	 $30 
 $12,724 	8	 $27 
 $13,519 	7	 $23 
 $14,314 	6	 $20 
 $15,109 	5	 $17 
 $15,905 	4	 $13 
 $16,700 	3	 $10 
 $17,495 	2	 $7 
 $18,290 	1	 $3 
 $19,086 	0	 $-   
		
 $19,086 		 $915

 

[HarryTiger]

I get $794.10 per month.

1 = $794.10
2 = ($794.1 * (0.05 /12)) + $794.10 (current) + $794.10 (deposit) = $1591.51

etc

24 = $20,000.14


But if you import drugs from Thailand in your anus you make $20,000 in 1 month only. Obviously that is the better option.

 

[MarcusP2]

Easiest non-automated way is to use Goal Seek. Enter Jabso's formula and tell Excel to solve it for $20,000.

But since this sounds like Excel homework you probably need something automated, which is easiest to do with Jabso's link as a simple formula.

Edit: didn't realise this thread was 4 days old.