Hello, I have a question about a procedure I wrote for a financial planning tool I'm working on in Visual Basic 6.0 (DAO). I set up text boxes to accept the following inputs: 1) Retirement Goal - retgoal (single) 2) Years to Retirement - yrsret (integer) 3) Annual Interest Rate - annrate (integer) 4) Initial Deposit - cav (single) 5) Contribution Years - yrscont (the number of years an annual contibution will be made consecutively starting in year 1, same amount each year - integer) I then created a formula to calculate the Annual Deposit (dp - single) that one would have to make yearly in order to attain the retirement goal based on the years to retirement, annual interest rate, initial deposit and the number of years a contribution will be made. this part of my code looks like this: retgoal = Text1.Text yrsret = Text2.Text annrate = Text3.Text cav = Text4.Text yrscont = Text5.Text For dp = 1 To 50000 Step 0.1 For b = 2 To yrscont For c = yrscont + 1 To yrsret bal(1) = (cav + dp) * annrate / 100 + (cav + dp) ' the balance in the first year bal(b) = (bal(b - 1) + dp) * annrate / 100 + bal(b - 1) + dp ' the balance of years 2 to the number of years contributions are made bal(c) = bal(c - 1) * annrate / 100 + bal(c - 1) ' the balance for the years starting when no more annual contibutions are made Next c Next b If retgoal - bal(yrsret) < Abs(1) Then ' after going through the nested loops, if the difference between the retirement goal and the balance at years to retirement is less than 1 then exit for. Label19.Caption = Format(bal(yrsret), "#,###,###,###") ' this will show the balance at "years to retirement" Exit For End If Next dp Text6.Text = dp ' this will show the annual deposit amount End If After this, I created code to display the balances for each year untill retirement. My procedure works fine up to a value of 10,000,000 for the retirement goal. When I try to input higher values, I get rather rediculous results for dp (annual deposit amount). My question is; can anyone provide a better formula (or procedure) to achieve what I am trying to accomplish. I hope I explained what it is I am trying to do in a clear manner, but if not, please let me know. I used these values as a control: retgoal = $10,000,000 yrsret = 20 annrate = 15% cav = $600,000 yrscont = 16 Any help would be greatly appreciated. Thanks Janny

Don't understand why all the nested loops. There are financial functions built into VB that will do all the dirty work for you. Look up FV = Future Value and PV = Present Value and NPV = Net Present Value Plus loads of other to do with calculating annuities. All you have to do is provide the parameters and format the output. Basically, if the function exits in Excel, it also exists in VB. All these functions use Double data types which may be where your problem lies. Stuart

Hi Stuart, You are so right! I can't believe I didn't think of just using built in functions. I used to teach Excel and I know it fairly well. Just think of the time I could have saved. Thanks browser

Your numeric precision could be higher. Actually your payment schedule should not be nested. You do 'b' loop then 'c' loop, your 'c' is within 'b'. It is awkward to estimate 'dp' by step increment. With your .1 step it would take almost 19000 iterations. A binary or other approximation method would result in faster convergence. Less than 25 tries to $.10 The problem is easier solved in Excel or some other spreadsheet using financial functions. Your assumptions are extremely agressive. $600,000 compounded at 15% at the end of 20 years is more than $9,800,000. Which leaves an approximate annual payment of $1885 for 16 years. These numbers are guestimates. Since I calculated by parts.

Hi Stuart, With regards to the financial functions, can any of them be used for the following scenerio: Mr. X will retire in 20 years. At retirement, he wants to have $10,000,000. He makes an initial deposit of $600,000. He wants to make an annaul contribution for 16 consecutive years starting at year 1. The interest rate is 15%. I tried using the PMT and PPMT functions to calculate the annual deposit amount in order to attain his retirement goal, but I think I am a little rusty on it. Can you offer any suggestions? Thanks a lot. Browser

Thanks for your input wizard-fred. browser

I don't think there is a specific function to calculate the payments required as you describe. The PMT function is used to calculate how much you would have to pay back on a specific loan. The way I did it was to use the PV function inside a loop starting of with an assumed monthly payment. RateGrowth = 15% payment = 600,000 For a = 1 to 16 sum = PV(RateGrowth, a, -Payment) next For a = 17 to 20 sum = PV(RateGrowth, a, 0) next You can then do the same using NPV which calculates the future value of the investments based on a particular rate of inflation. $10,000,000 today will not be worth the same as $10,000,000 in twenty years time. Put the sums into a grid then use a slider of some other method at adjust the payments and interest rates until you achieve the goal required. Notice that payments are a negative value. Positive values are used when calculating return on investments. Bear in mind that I am a programmer, not a Finical Adviser. I did this some time ago with the assistance of a Financial Adviser who knew nothing about computers. Stuart