Can I use Dates to Automatilly Create amortization?

[Guest]

I have a table that has a total transaction amount (i.e. total liability). I
want to be able to amortize that amount monthly in another table and use it
to create a query that shows the remaining balance. In the original
transaction table, I have fields for begin and end dates for the
amortization. For instance, in the transaction table, there is a 100,000
item that will be amortized between January and October 2007. I want a table
to be autofilled with 10,000 in January 2007, February 2007, etc. Then, I
want to be able to pull a query that shows the balance at a given month. Any
help would be appreciated.

 

[strive4peace]

First, you need to calculate the monthly payment

Pmt = =S*p/((1+p)^T-1)

where

S = Sum of money
p = periodic rate
T = total number of periods

The total sum paid back will be

Pmt*T


At any point, the balance will be

Pmt (T - i)

where i is the number of payments that have been made

(this is assuming that no benefit is given for early payment)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*