Finance Question

[cptkoble]

Anyone who knows an existing formula or VBA on solving the following:

I will put an amount of "cell A1" given dollars on my bank account for a "cell b1" given number of years (n-times). I know that I want to save money until it reaches "cell c1" given total amount.
What % need the intrest rate "to find" need to be to achieve my goal (cell d1).

I know it probably will have to find it's way around and resolve for each further decimal needed until it reaches an amount greater than that needed in (cell c1).
Any help would be greatly appreciated.

 

[shades]

I think that Solver (Add-in?) would be able to provide you with readymade tools.

 

[Dave Patton]

On 2002-08-26 11:36, shades wrote:
I think that Solver (Add-in?) would be able to provide you with readymade tools.

see Rate and/or Goal Seek in help.

Do you make weekly, monthly, annual or no deposits?

Are the amounts deposited equal?

Do you assume a constant rate of interest?

 

[Ricardo]

You do not need Solver
If
A1: anual Fee(write with negative)
B1: Number of years
C1: FV- Future Value

Try with:
TASA(B1;A1;0;C1;0 or 1

TASA is in spanish, Perhaps someone can say the english formula. (RATE??)
See the help for 0 or 1
(Begin of period, end of period)
Bye

 

[cptkoble]

Hi Dave,

> Do you make weekly, monthly, annual or no deposits?
It will be an annual deposit.
>Are the amounts deposited equal?
Yes
Do you assume a constant rate of interest?
Yes, and that is just what I would like to get out of this sheet. I want to play a little with the amount in let's say 25 or 30 years, combined with the yearly deposit. The %-rate required to achieve my goal, and to be calculated, is at this time still the problem.

Thanx
---------------------------------------------
Hi Ricardo

I tried your formula but it won't give the correct answer. Sorry.

Based upon the known 8% intrest (example)
The first year 1-1-03 I deposit 100, on 1-1-04 I will have 100*1,08^1. This to be added up with the next deposit will make the total 208. The year following the fun really start. Based on the deposit on the 1st of 2002 it will give me 100*1,08^2 added up with the 100*1,08^1 for the deposit from the last year and then to be added up with the new deposit of 100.

You can imagine it is not too difficult to create this in a sheet up to 30 years given the % to calculate the total future value.

Problem is though to calculate this % when given the total future value, the yearly deposit and the amount of years to follow your savings.

Thanx

 

[Dave Patton]

On 2002-08-26 12:20, cptkoble wrote:
Hi Dave,

> Do you make weekly, monthly, annual or no deposits?
It will be an annual deposit.
>Are the amounts deposited equal?
Yes
Do you assume a constant rate of interest?
Yes, and that is just what I would like to get out of this sheet. I want to play a little with the amount in let's say 25 or 30 years, combined with the yearly deposit. The %-rate required to achieve my goal, and to be calculated, is at this time still the problem.

Thanx
---------------------------------------------
Hi Ricardo

I tried your formula but it won't give the correct answer. Sorry.

Based upon the known 8% intrest (example)
The first year 1-1-03 I deposit 100, on 1-1-04 I will have 100*1,08^1. This to be added up with the next deposit will make the total 208. The year following the fun really start. Based on the deposit on the 1st of 2002 it will give me 100*1,08^2 added up with the 100*1,08^1 for the deposit from the last year and then to be added up with the new deposit of 100.

You can imagine it is not too difficult to create this in a sheet up to 30 years given the % to calculate the total future value.

Problem is though to calculate this % when given the total future value, the yearly deposit and the amount of years to follow your savings.

Thanx

What happened when you used RATE function?

Put your known factors into cells and then experiment with FV, Rate, and related functions.

Did you have questions on the EXCEL'S HELP information?

 

[Ricardo]

1) go to
http://www.inform.umd.edu/UMS+State/UMD-Projects/MCTP/Courses/Mathematics/simulations/income.html

Perhaps it helps

2) what happened with Rate?
I think works Ok

3) I am doing exactly the same exercise so i would like to know the conclusions.
bye

 

[cptkoble]

Hi All


It took some time but I have found the solution for the problem.

You were right. It can be as easy as the RATE function. It took me too long to find the answer but... I bought the great book by John Wenkenbach... (something) about formulas and there it was.

Thanx all for replying and have a good one.

Jan Alexander

 

[Dave Patton]

On 2002-08-30 05:50, cptkoble wrote:
Hi All


It took some time but I have found the solution for the problem.

You were right. It can be as easy as the RATE function. It took me too long to find the answer but... I bought the great book by John Wenkenbach... (something) about formulas and there it was.

Thanx all for replying and have a good one.

Jan Alexander

suggestion of Aug 26 was to try RATE and/or
read about it in Excel's Help.