Microsoft QuickTips

Is there an excel formula (in the same vain as pv, fv etc), that will give me the answer to this problem for any given year. i have £100,000 in my bank account, e year i take out £17,000 but gain 5% interest on the remainder at the end of the year. i require one formula of the pv, fv type, i.e. saying (100,000-17,000)*1.05 is not the answer as this does not give the answer for a particular year i.e. year 9 or saying make a table of values for each year, i.e. year 1 - £87,150.00 year 2 - £73,356.68 year 3 - £58,873.68 year 4 - £43,666.54 as this does not equal one formula. plz can anyone help, plz can i also note, i am english and have some trouble understand ing the american terminology, such as 'principle' in the excel help file for this type of formula, if you can explain using the example above, i would be greatful.

You can create your own function. if you want to pretty it up (add variable declarations, comments, etc.) feel free to do so but it'll work as is. you can also just copy and paste it into a module in your spreadsheet vba project. note: p is your original principle (amount you start with, in your example 100,000), year is the year you want to consider, pct is the interest in decimal (0.05) and wd is the amount you want to withdraw (as a negative number). function emma(p, year, pct, wd) emma = p for x = 1 to year emma = (emma + wd) * (1 + pct) next end function if you don't know how to put it in your spreadsheet, open the spreadsheet. hit [alt]+f11 to open up vb. you should see something on the left side with "vbaproject (spreadsheet.xls)". if you don't, hit [ctrl]+r to open up your project explorer window. under that "microsoft excel objects". right click on "microsoft excel objects" and click "insert" then "module". copy the above code into the module window that should have opened up. go back to excel and either call the function directly by typing in the function "=emma(p, year, pct, wd)" or go to insert-function-user defined-emma and the function window should pop up to walk you through it.