Calculating interest rate

July 13, 2010 2:05 PM

Excel-finance future value help please.... Google Spreadsheet inside.

Solving for interest rate given the following:

1) Annual payment into an investment
2) Known future value at year 20
3) The tax rate

If someone could please provide me with the formula to use in my particular scenario to calculate this interest rate, I would be most grateful, thanks!

Here is my particular scenario:
http://spreadsheets.google.com/ccc?key=0Ai41XkWa4cLYdGZqUURRUW5PQldTWHJrT29OR0E1dXc&hl=en

If you would like to be able to edit this sheet, you can download the excel sheet here. Thanks

posted by yoyoceramic to Work & Money (4 answers total)

Best answer: I don't have a formula for you, but excel's 'goal seek' function will handle this easily.
posted by jon1270 at 2:22 PM on July 13, 2010

Best answer: The easiest way to do this is to use Excel's goal seek tool, which gives a CD rate of just over 5.7%. Set the goal seek to change the interest rate cell such that the future value at year 20 is the target amount.
posted by matthewr at 2:23 PM on July 13, 2010

just use the "rate" function and then divide that by 1-tax rate
posted by JPD at 2:36 PM on July 13, 2010

As JPD wrote, the RATE() function is probably the one you want.

For more general information, check out the Financial Formulas section at http://www.techonthenet.com/excel/formulas/index.php.

Many of these functions require the correct sign on the dollar amounts or an error will be returned. Typically the payment amount is given as negative and the present/future value is given as positive.
posted by RobinFiveWords at 7:16 PM on July 13, 2010