Results 1 to 2 of 2
2011-09-29, 01:30 #1
- Join Date
- Oct 2002
- Mornington Peninsula
- Thanked 0 Times in 0 Posts
Excel - Solver to obtain compounding interest and future value?
Have attached a file with starting investment of $1000. Aim is to achieve $1,100 by end of 12 months, but the interest rate is unknown.
You will see i have used a simple formula in cells D415. (Column E is the same formula, but through to the 13th month, assuming the interest is accrued at the anniversary of the end of the 1st month).
I realise i would not be able to use Goal Seek to set the final cell in D15 to $1100, because I could only change ONE cell.
If i used Solver so that I can change (many) all the cells to allow for accruing interest, I can't seem to set the constraints. I tried setting that each next cell must be greater than the previous, but that doesn't seem to work.
I do want to show the month by month accruing investment + interest. Should I have used RATE? If so how would that work, would i fill it down the column?
Many thanks in advance.
PS. Am using 2003 but can use 07 or 10.Many thanks for any help, much appreciated.
Have a great day!
2011-09-29, 03:34 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 192 Times in 178 Posts
Seems to me you could use FV to do this. In the attached workbook, you can use Goal Seek to set the value of B5 to 1100 by changing the value in B1.Regards,
Microsoft MVP - Excel