Compound Interest Form

[colmtourque]

Does anyone know how to make a form that will figure out compound interest.
That is If you have a begining value of 100, and you want to the figure that every year the value will grow by 10%.
I have a query that holds the begining value of an account and the interest rate and the number of year to compound, but how do I fill a flex grid (if that is what I should use) with the values of a number of years.
I figure I need to do a do loop until a counter=s the number of years.

So far I have created a blank form that is a datagrid with three cells and named the cells, as a subform of an account value form.

Someone please help and tell me what to do next.

ps
I know this can be done quite easily in excel but I have been asked to make it happen in access.

 

[dcx693]

A quick-and-dirty way to do this would be with some VBA code. Create a table that holds the year value and the calculated value with interest. Then set your subform recordsource to display that table.

 

[colmtourque]

Confused

I realize with an Update Query, but I'm unsure what the code would be. Don't mind the down and dirty way, just unsure of what the actual code would be.

 

[dcx693]

I wouldn't do this with an update query, true you might already have year numbers in there, but those are easy enough to recreate.

What version of Access are you using? And do you know if you're using DAO or ADO? Do you know how to write a function to calculate your value with interest given a certain number of years?

 

[namliam]

to calculate running intrest you can use this formula

(100%+Interest)^NumberOfYears*OriginalAmount

If the interest = 10%
Number of year = 5
Orignal amount = 100
(100%+10%)^5*100 = 161.051

you can easily calculate the number of years all others are allready known...

Regards

The Mailman

 

[colmtourque]

Think I have it started

dcx693:

I actually meant an append query, sorry.

I'm using access 2000 and I'm doing the work straight within access. Here's is the db layout:
ClientQuery (holds the number of years (NoY) to compound interest)
AccountTable: Holds original account values
TempTable: Supposed to hold compound interest values over NoY

So far I have written one append query that will Place the first year of Compounding into the temp table, and a second append query that pulls the first entries information and inputs the second line.

Question here:
How do I setup the loop to run the second query NoY times.

Am I on the right track now?

 

[colmtourque]

THANKS!!!

thanks to your help I made it work

Dim CountN
DoCmd.OpenQuery "Query1"

CountN = 0
Do Until CountN = NumberofPeriods.Value
CountN = CountN + 1
DoCmd.OpenQuery "Query2"
Loop

One question more and that should be it. is there and echo off command and to refresh a form is that [formname].refresh?

 

[dcx693]

I wouldn't use an append query either, though I guess you could. It's just that unless your principal amount and interest rates stay the same, every entry (except the number of years) in the table is subject to change.

I wouldn't use a query for this, but call a VBA routine. I'd create a new table each time the calculation is changed, unless we're talking about thousands of years (in which case it might go slowly).

 

[dcx693]

Well, I'm glad it worked out!

You can use:
Application.Echo False
and
Application.Echo True
to turn on and off the screen updates.

I think you might be looking for the Me.Repaint method, but look up the help topic on the difference between the .Refresh and .Repaint methods.

 

[llkhoutx]

If you look, you'll be able to find a formular for compound interest.

I don't have one handy.

 

[Mile-O]

If you look, you'll be able to find a formular for compound interest.

There are some built in financial functions - you can get these using the Intellisense to

VBA.Financial.

 

[Jacob Mathai]

fval = pval * ((1 + apr) ^ nper)

fval=future value
pval = present value (investment)
apr = Interest rate in fraction for the period. Example : 0.06
nper = number of periods

For quarterly compounding, divide annual percentage rate (apr) by 4 and number of periods will be number of quartes.